Identify and Terminate Active Statistics Collection Job in Database

Learn how to handle statistics collection jobs that remain active in your Oracle database. This guide provides step-by-step instructions for identifying active sessions, verifying job statuses, and safely terminating lingering sessions to optimize database performance.

1. Identify Active Sessions in v$session

Run the following query on both nodes to find sessions related to database statistics jobs:

SELECT SID, SERIAL#, USERNAME, STATUS, PROCESS  
FROM V$SESSION  
WHERE MODULE LIKE '%DBMS%';

Note: If no results are returned, repeat the query on the other node.

2. Check Job Status in Scheduler

Verify the status of the statistics collection job in the DBA_SCHEDULER_JOBS view:

SELECT OWNER, JOB_NAME, STATE, LAST_START_DATE, COMMENTS  
FROM DBA_SCHEDULER_JOBS  
WHERE PROGRAM_NAME = 'GATHER_STATS_PROG';

3. Cross-Check with OS Process

Using the process ID from the session, monitor processes at the operating system level:

[W3Buddy@<hostname> ~]$ top -c -M

4. Terminate Sessions in the Database

Kill the identified sessions using their SID and SERIAL#:

ALTER SYSTEM KILL SESSION '405,61711';  
ALTER SYSTEM KILL SESSION '10548,14473';

Note: A warning may indicate that the session is marked for termination and will end after completing ongoing operations.

5. Force Kill Process at OS Level

If the session does not terminate, forcefully kill the associated OS process:

[W3Buddy@<hostname> ~]$ kill -9 5226

6. Verify Session Clearance

Re-check active sessions in the database:

SELECT SID, SERIAL#, USERNAME, STATUS, PROCESS  
FROM V$SESSION  
WHERE MODULE LIKE '%DBMS%';

Expected output: no rows selected

7. Confirm Job State

Ensure the job is no longer running:

SELECT OWNER, JOB_NAME, STATE, LAST_START_DATE, COMMENTS  
FROM DBA_SCHEDULER_JOBS  
WHERE PROGRAM_NAME = 'GATHER_STATS_PROG';

Expected output: no rows selected

By following these steps, you can efficiently resolve issues with active statistics collection jobs in the database.