Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Sessions belonging to database statistics collection jobs can sometimes remain active. In this article, we will discuss how to identify and terminate these sessions.
1. “v$session” is queried from both nodes to find the remaining active sessions.
1 | SELECT SID, SERIAL#, USERNAME, STATUS, PROCESS FROM V$SESSION WHERE MODULE LIKE '%DBMS%'; |
NOTE: Sometimes this query may not yield any results. You need to check from the other node.
2. In Scheduler_Jobs, it is checked whether the Job is in RUNNING or not.
1 2 3 4 5 | SQL> select owner, job_name,state,last_start_date,comments from dba_scheduler_jobs where PROGRAM_NAME='GATHER_STATS_PROG' ; OWNER JOB_NAME STATE LAST_START_DATE COMMENTS ----- ------------------------------ --------------- -------------------------------------------------- -------------------------------------------------- SYS ORA$AT_OS_OPT_SY_5173 RUNNING 12-AUG-16 10.00.08.740921 PM AFRICA/CAIRO automatic optimizer statistics collection job |
3. These sessions are also checked by the operating system according to their process IDs and checked with the ora_jo* command.
1 | [root@mwdb02 ~]# top -c -M |
4. Sessions are killed on the database side.
1 2 | ALTER SYSTEM KILL SESSION '405,61711'; alter system kill session '10548,14473'; |
During the kill, a warning is received as follows.
This indicates that these sessions are marked for killing and will be killed as a result of the completion of the operations related to those sessions.
5. If the processes belonging to the sessions are not waited to finish, the sessions are also killed by the operating system.
1 2 | [root@mwdb02 ~]# kill -9 5226; [root@mwdb02 ~]# |
6. It is checked whether the session is cleared or not.
1 | [root@mwdb02 ~]# top -c -M |
7. On the database side, the existence of sessions is queried from v$session.
1 2 3 | SQL> SELECT SID, SERIAL#, USERNAME, STATUS, PROCESS FROM V$SESSION WHERE MODULE LIKE '%DBMS%'; no rows selected |
8. The state of the job is checked.
1 2 3 | SQL> select owner, job_name,state,last_start_date,comments from dba_scheduler_jobs where PROGRAM_NAME='GATHER_STATS_PROG' ; no rows selected |
