Oracle Lock Table Solution

--View the Locked object

Select A.session_id,a.object_id,a.oracle_username,b.object_type,b.object_name from V$locked_object a,all_objects b

where a.object_id = b.object_id;

--Unlocking method

1 Find SID, serial#

Select c.sid,c.serial# from v$session C where c.sid into (select a.session_id from V$locked_object a,all_objects b where a. object_id = b.object_id);

2) Unlock

Alter system kill session ' sid,serial# ';

For the above method kill session is invalid, when the report ora error, use the following method to resolve:

1) 、--Find the SPID

Select spid, Osuser, S.program

From V$session s,v$process p

where s.paddr=p.addr and s.sid = ' xxx '-can be obtained by the above lock SQL

2, with the root user login to the Oracle database server, kill the process immediately unlock

kill-9 spid;

Attach 2 section automatic unlock sql:

--Only a single SID is available

Declare

V_SID number;

V_serial number;

Vv_sql varchar2 (4000);

Begin

Select c.sid,c.serial# into v_sid,v_serial from v$session C

where C.sid in

(select a.session_id from V$locked_object a,all _objects b where a.object_id = b.object_id);

Dbms_output.put_line (v_sid| | ', ' | | V_serial);

Vv_sql: = ' alter system kill session ' | | v_sid| | ', ' | | v_serial| | ';

Execute immediate vv_sql;

End

--cursor traversal applies to multiple SIDs

Declare

Begin

For cur in

(select c.sid,c.serial# from v$session C

where C.sid in

(select a.session_id from V$locked_object a,all_objects b where a.object_id = b.object_id)) Loop

Execute immediate ' alter system kill session ' | | cur.sid| | ', ' | | cur.serial#| | ';

End Loop;

End

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/