[SQL] View plain copy print? /* *locked *query locked object and analyse reason,kill it * */ select ' alter system kill session "' | | sid | | ', ' | | serial# | | '; from (select distinct a.sid, a.Serial#, status, machine, lockwait, logon_time from v$session a, v$locked_object b where (a.status = ' ACTIVE ' or a.status = ' INACTIVE ') and a.sid = b.session_id and b.ORACLE_USERNAME = ' xyhistest ' --plus user name filter user resources ) /*2, batch execution of the statements generated in the first step Alter system kill session ' Sid,serial# '; alter system kill session ' 6976,33967 ';*/ /*3, query Oracle user name, machine name, Lock Table object */ select l.session_id sid, s.serial#, l.locked_ mode, l.oracle_username, l.os_user_name, s.machine, s.terminal, o.object_name, s.logon_time from v$locked_object l, all_objects o, v$session s WHERE l.object_id = o.object_id AND l.session_id = s.sid order by sid, s.serial#; /*3. or */ Select s.sid, s.OSUSER, p.spid as OSPID, s.MACHINE, s.TERMINAL, s.PROGRAM from v$session s, v$process p where s.sid = 6 --session_id and s.paddr = p.addr; /* 4, the query is the execution of what SQL statements led to lock the table */ select b.sql_text from v$session a, v$sql b where a.sid = 6 --session_id and a.sql_address = b.address (+); /*5, query is the execution of SQL statements resulting in lock table */