Oracle – Removing Locks Without Admin Privileges

lockingoracle

3 wild sessions each got a row exclusive lock in the same table:

SELECT * FROM DBA_DML_LOCKS;

enter image description here

How can I remove the lock without being admin?

If I was admin I would list (SID, SERIAL#) of the sessions that have the locks:

SELECT SID,SERIAL# 
FROM V$SESSION 
WHERE SID IN (SELECT SESSION_ID 
FROM DBA_DML_LOCKS );

enter image description here

Then execute:

ALTER SYSTEM KILL SESSION 'SID,SERIALl#';

e.g.:

ALTER SYSTEM KILL SESSION '115,1931';

But since I am not admin I get an insufficient privilege error:

ALTER SYSTEM KILL SESSION '115,1931'
Error report -
SQL Error: ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges

I am running Oracle 11gR2 11.2.0.3.

Best Answer

If this happens often there is an option to write a package or a stored procedure for that. The owner of the package would need the alter system privilege and the package should be created with definers rights.

You just need execute privileges on that package and feed the session kill procedure with the right parameters.

There is still a good chance that zombie processes keep running until the end user that was running the killed processes tries to do anything. These zombies kan be killed from the OS. To find those processed, before killing read their os pids by joining v$session with v$process. An other option is to regularly clean those orphaned processes by finding processes in v$process that have no session in v$session.

Related Question