First to find out the session that caused the locking. Use any of below two SQLs.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
select a.session_id, a.oracle_username, a.os_user_name, b.owner "OBJECT OWNER", b.object_name, b.object_type, a.locked_mode from (select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a, (select OBJECT_ID, OWNER, OBJECT_NAME,OBJECT_TYPE from DBA_OBJECTS) b where a.object_id=b.object_id; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SELECT l.session_id||','||v.serial# sid_serial, l.ORACLE_USERNAME ora_user, o.object_name, o.object_type, DECODE(l.locked_mode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(l.locked_mode) ) lock_mode, o.status, to_char(o.last_ddl_time,'dd.mm.yy') last_ddl FROM dba_objects o, gv$locked_object l, v$session v WHERE o.object_id = l.object_id and l.SESSION_ID=v.sid order by 2,3; |
Once you have session id, query v$session to find SQL_ID or PREV_SQL_ID.
|
1 |
select SQL_ID, PREV_SQL_ID from v$session where sid=:sessionId; |
And get the query from v$sql
|
1 |
select * from v$sql where sql_id=:sqlId; |