How to terminating Sessions in Oracle

Sometimes it is necessary to terminate current user sessions. you might want to perform an administrative operation and need to terminate all non-administrative sessions.

• About Terminating Sessions
• Identifying Which Session to Terminate
• Terminating an Active Session
• Terminating an Inactive Session
• Cancelling a SQL Statement in a Session

1.About Terminating Sessions

You terminate a current session using the SQL statement

ALTER SYSTEM KILL SESSION

. The following statement terminates the session whose system identifier is 7 and serial number is 15:

ALTER SYSTEM KILL SESSION '7,15';

2.Identifying Which Session to Terminate

To identify which session to terminate, specify the session index number and serial number.

To identify the system identifier (SID) and serial number of a session:

• Query the V$SESSION dynamic performance view.

SELECT SID, SERIAL#, STATUS
  FROM V$SESSION
  WHERE USERNAME = 'JWARD';

SID    SERIAL#    STATUS
-----  ---------  --------
    7         15  ACTIVE 
   12         63  INACTIVE

A session is

ACTIVE

when it is making a SQL call to Oracle Database. A session is

INACTIVE

if it is not making a SQL call to the database.

3.Terminating an Active Session

Terminating an active session ends the session.

If a user session is processing a transaction (

ACTIVE

status) when you terminate the session, then the transaction is rolled back and the user immediately receives the following message:

ORA-00028: your session has been killed

If, after receiving the

ORA-00028

message, a user submits additional statements before reconnecting to the database, then Oracle Database returns the following message:

ORA-01012: not logged on

If you are using Application Continuity, then an active session's activity is recovered when the session terminates. If you do not want to recover a session after you terminate it, then you can include the

NOREPLAY

keyword in the

ALTER

SYSTEM

statement. For example, the following statement specifies that the session will not be recovered:

ALTER SYSTEM KILL SESSION '7,15' NOREPLAY;

If you use the

DBMS_SERVICE.DISCONNECT_SESSION

procedure to terminate one or more sessions, then you can specify

DBMS_SERVICE.NOREPLAY

for the

disconnect_option

parameter to indicate that the sessions should not be recovered by Application Continuity. For example, to disconnect all sessions with the service

sales.example.com

and specify that the sessions should not be recovered, run the following procedure:

BEGIN
  DBMS_SERVICE.DISCONNECT_SESSION(
    service_name      => 'sales.example.com',
    disconnect_option => DBMS_SERVICE.NOREPLAY);
END;
/

4.Terminating an Inactive Session

If the session is not making a SQL call to Oracle Database (is

INACTIVE

) when it is terminated, the

ORA-00028

message is not returned immediately. The message is not returned until the user subsequently attempts to use the terminated session.

When an inactive session has been terminated, the

STATUS

of the session in the

V$SESSION

view is

KILLED

. The row for the terminated session is removed from

V$SESSION

after the user attempts to use the session again and receives the

ORA-00028 

message.

In the following example, an inactive session is terminated. First,

V$SESSION

is queried to identify the

SID

and

SERIAL#

of the session, and then the session is terminated.

SELECT SID,SERIAL#,STATUS,SERVER
   FROM V$SESSION
   WHERE USERNAME = 'JWARD';

SID    SERIAL#   STATUS     SERVER
-----  --------  ---------  ---------
    7        15  INACTIVE   DEDICATED
   12        63  INACTIVE   DEDICATED
2 rows selected.

ALTER SYSTEM KILL SESSION '7,15';
Statement processed.

SELECT SID, SERIAL#, STATUS, SERVER
   FROM V$SESSION
   WHERE USERNAME = 'JWARD';

SID    SERIAL#   STATUS     SERVER
-----  --------  ---------  ---------
    7        15  KILLED     PSEUDO
   12        63  INACTIVE   DEDICATED
2 rows selected.

5.Cancelling a SQL Statement in a Session

You can cancel a SQL statement in a session using the

ALTER SYSTEM CANCEL SQL

statement.

Instead of terminating a session, you can cancel a high-load SQL statement in a session. When you cancel a DML statement, the statement is rolled back.

The following clauses are required in an

ALTER SYSTEM CANCEL SQL

statement:

• SID – Session ID
• SERIAL – Session serial number

The following clauses are optional in an

ALTER SYSTEM CANCEL SQL

statement:

• INST_ID – Instance ID
• SQL_ID – SQL ID of the SQL statement

You can view this information for a session by querying the

GV$SESSION

view.

ALTER SYSTEM CANCEL SQL 'SID, SERIAL, @INST_ID, SQL_ID';

The following example cancels a SQL statement having the session identifier of

20

, session serial number of

51142

, and SQL ID of

8vu7s907prbgr

:

ALTER SYSTEM CANCEL SQL '20, 51142, 8vu7s907prbgr';

11.Process and Session Data Dictionary Views

ViewDescription

V$PROCESS

Contains information about the currently active processes

V$SESSION

Lists session information for each current session

V$SESS_IO

Contains I/O statistics for each user session

V$SESSION_LONGOPS

Displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution. More operations are added for every Oracle Database release.

V$SESSION_WAIT

Displays the current or last wait for each session

V$SESSION_WAIT_HISTORY

Lists the last ten wait events for each active session

V$WAIT_CHAINS

Displays information about blocked sessions

V$SESSTAT

Contains session statistics

V$RESOURCE_LIMIT

Provides information about current and maximum global resource utilization for some system resources

V$SQLAREA

Contains statistics about shared SQL areas. Contains one row for each SQL string. Provides statistics about SQL statements that are in memory, parsed, and ready for execution