[Oracle] Check locks and sessions basic

[Oracle] Check locks and sessions basic


-- Check locked tables

1 2 3

SELECT do.object_name, do.owner, do.object_type, do.owner, vo.xidusn, vo.session_id, vo.locked_mode FROM v$locked_object vo , dba_objects do WHERE vo.object_id = do.object_id ;

-- Check the specific table is locked

1 2 3 4

SELECT A.SID, A.SERIAL#, B.TYPE, C.OBJECT_NAME FROM V$SESSION A, V$LOCK B, DBA_OBJECTS C WHERE A.SID=B.SID AND B.ID1=C.OBJECT_ID AND B.TYPE='TM' AND C.OBJECT_NAME IN ('tbl_ABC');

-- Check lock caused users and objects

1 2 3 4 5 6 7 8

SELECT distinct x.session_id, a.serial#, d.object_name, a.machine, a.terminal, a.program, b.address, b.piece, b.sql_text FROM v$locked_object x, v$session a, v$sqltext b, dba_objects d WHERE x.session_id = a.sid and x.object_id = d.object_id and a.sql_address = b.address order by b.address,b.piece;

-- Check lock sessions and users

1 2 3 4 5 6

SELECT distinct x.session_id, a.serial#, d.object_name, a.machine, a.terminal, a.program, a.logon_time , 'alter system kill session ''' || a.sid || ', ' || a.serial# || ''';' FROM gvlocked_object x, gv$session

a, dba_objects d WHERE x.session_id = a.sid and x.object_id = d.object_id order by logon_time;
-- Kill user session

1 2	--alter system kill session 'session_id,serial#'; alter system kill session '26,6044';

-- Show current connected user session information

1 2 3 4 5 6

SELECT distinct a.sid, a.serial#, a.machine, a.terminal, a.program, b.address, b.piece, b.sql_text FROM v$session a, v$sqltext b WHERE a.sql_address = b.address order by a.sid, a.serial#,b.address,b.piece;