I canceled some UPDATE query in Oracle SQL Developer, then killed Oracle SQL Developer. Three times.
This resulted in 3 sessions that each got a row exclusive lock in the same table (the one on which the UPDATE queries was working):
SELECT * FROM DBA_DML_LOCKS;
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 );
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.
Executing COMMIT or ROLLBACK afterward didn't help, because since I started Oracle SQL Developer I got a new session ID. After a while Oracle removed the inactive sessions and locks, but I would like to have a way to remove the locks as a non-admin user and without having to wait for Oracle to do so.