Statistics Collection Job Remaining Active in the Database: Steps to Identify and Terminate the Session
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.