KILL Oracle USER sessions

Published by Alessandro Caredda on 28 July 201928 July 2019

If you’re looking to perform “KILL Oracle user sessions,” you’re in the right place!

During the deletion phase of a SCHEMA, it often happens that sessions connected to the database hinder the process, generating the classic error ORA-01940: cannot drop a user that is currently connected.

This situation typically occurs during data alignment from production environments to test environments.

Before starting the import process, I try to drop the user just to avoid encountering the ORA-01940 error.

So, what can be done in these cases?

A practical approach involves identifying Oracle sessions associated with the connected user.

Firstly, it’s essential to determine whether these sessions originate from an application server or if they are direct connections from other clients.

If you want to examine all sessions belonging to a specific user, you can use the query provided in the associated article to obtain details on each session.

However, what if time is essential and extensive testing is not possible?

No problem.

The goal is to terminate all currently connected Oracle user sessions to the database.

This can be achieved by executing the following query:

KILL Oracle USER sessions

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

If after disconnecting you find new sessions, the problem is likely due to the application server maintaining persistent connections.

Whenever possible, we try to close these connections. However, if closure is not immediately possible, we resort to more strategic measures.

To enable such actions, the user must have their ACCOUNT_STATUS in the DBA_USERS view set to OPEN, indicating an UNLOCKED state.

We explicitly set the user’s status to LOCK using the following command:

alter user PIPPO account LOCK;

To view my guides on CRS commands click here!
At this LINK, alternatively, find the SQL statements.

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