Freeing Locks on an Oracle Database

Introduction

These queries and commands will be useful to anyone working with an Oracle database that's running into the issues of locks grinding things to a halt. This will help you quickly identify what's responsible for the lock.

Using the Code

Let's say you're trying to recompile a PL/SQL package, but the database keeps hanging without recompiling. You'll want to run the query below to see what has a lock on that package:

SELECT * 
FROM dba_ddl_locks 
WHERE name = 'PACKAGE_NAME';

Alternatively, if you're running into locking issues when trying to manipulate data (INSERT, DELETE, UPDATE, etc.), try the below query:

SELECT * 
FROM dba_dml_locks
WHERE name = 'TABLE_NAME';

Once you find the corresponding SESSION_ID that is responsible for the locks, you can learn more about it from the following query:

SELECT *
FROM v$session
WHERE sid = 123; --whatever your session ID was

You may find that a job is responsible, one of your coworkers sessions, or even an old session of yours. If you want to kill the session, you can run the following command using the session ID and "serial#' values from the v$session table:

ALTER SYSTEM KILL SESSIONS 'sid, serial #';