KILL Oracle USER sessions on RAC

Published by Patrizia Gardis on 2 March 20192 March 2019

In the article KILL Oracle USER sessions on RAC is much loved by me because of its precious use, since through the INST_ID field, we are able to execute the KILL on each instance that makes up the Oracle RAC.

In the numerous requests that arrive from the clients that I manage, I am often assigned data alignment tasks from the production environment to the test environment.

This involves: exporting data from the production environment,
transferring the export dump from the production server to the test server, dropping the SCHEMA to be imported into the test environment and subsequent import of the data and metadata, i.e. the entire SCHEMA .

However, if users are connected or there are application sessions linked to the SCHEMA, the DROP command will fail signaling that there are sessions connected to the DB.

Going more specifically, the following error message will appear:

ORA-01940: cannot drop a user that is currently connected

How can we solve the problem?

First of all, we must identify the sessions and then we will have to KILL them.

So, in case we need to kill all Oracle sessions of a particular user, with the following dynamic query,
we will build the command to launch to kill all sessions on all instances of the RAC from a single instance.

KILL Oracle USER sessions on RAC

select  'alter system kill session ''' || sid || ',' || serial# || ','||'@'|| INST_ID || ''' immediate;' 
from gv$session where USERNAME = '&USERNAME';

As described above, the query kills all sessions of a specific SCHEMA, but can be adapted to any need.

For example, if you wanted to kill a specific SID (Serial ID), just modify the where condition by eliminating the where clause after USERNAME = with SID = number of the sid to kill.

For other types of conditions, such as: all sessions before a certain date or before a certain time.

Take the query given in the article as an example of where condition

To view my guides on CRS Commands click here!
At this LINK, however, you will find the SQL queries

Stay updated on our activities by joining our community:
Facebook
Youtube
Linkedin