lunedì 4 marzo 2013

Oracle Tip: Find locked objects in Oracle Database

Tipically I end up locking objects by executing an Oracle Warehouse Builder (OWB) mapping and then stopping it because I realize I have forgotten a join condition. If you abruptly kill the session, the object may stay locked until the rollback is completed or endlessy if the session is killed before any action against the target table is taken.
In order to find out which object has been locked (and beg your DBA to kill the associated session or free the locks), run the following query:

 SELECT c.owner, c.object_name, c.object_type, b.SID, b.serial#,
                 b.status, b.osuser, b.machine
 FROM v$locked_object a, v$session b, dba_objects c
 WHERE b.SID = a.session_id AND a.object_id = c.object_id;

The output will look like this:

In case you are the DBA (and you have DBA privileges), then get the SID and the SERIAL# and run the following:

 alter system kill session 'SID,SERIAL#';

Nessun commento:

Posta un commento