Kill locked session

 

11:45:50 SQL> clear

11:46:11 SQL> create table t as select * from all_objects where 1=0;

 

Table created.

 

11:47:39 SQL> select * from t;

 

no rows selected

 

 

--插入大量数据，插入还没有完成的时候，手动断开sqlplus的连接

11:47:46 SQL> insert into t select * from all_objects WHERE ROWNUM<10000;  

--重新连接，并删除表t

SQL> drop table t;

drop table t

           *

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

 

--原因是上个session插入数据的时候，对table t产生了锁定

--查看锁定表的记录

SELECT A.OWNER      ,A.OBJECT_NAME      ,B.XIDUSN      ,B.XIDSLOT      ,B.XIDSQN      ,B.SESSION_ID

      ,B.ORACLE_USERNAME      ,B.OS_USER_NAME      ,B.PROCESS      ,B.LOCKED_MODE      ,C.MACHINE

      ,C.STATUS      ,C.SERVER      ,C.SID      ,C.SERIAL#      ,C.PROGRAM

FROM ALL_OBJECTS A, V$LOCKED_OBJECT B, SYS.GV_$SESSION C

WHERE (A.OBJECT_ID = B.OBJECT_ID)

AND (B.PROCESS = C.PROCESS)

AND a.owner = 'ETL'

ORDER BY 1, 2;

 

--查询结果：

OWNER OBJECT_NAME XIDUSN      XIDSLOT     XIDSQN      SESSION_ID  ORACLE_USERNAME      OS_USER_NAME      PROCESS     LOCKED_MODE MACHINE         STATUS    SERVER              SID      SERIAL#     PROGRAM

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

ETL   T             10    21    104273      814           ETL           dwapp         20165 3             dw_testdb       ACTIVE    DEDICATED   814   45197 sqlplus@dw_testdb (TNS V1-V3)

 

--用alter命令kill锁：

--ALTER SYSTEM KILL SESSION 'sid,serial#';

SQL> ALTER SYSTEM KILL SESSION '814,45197';

 

System altered.

--现在可以删除表t了

SQL> drop table t;

 

Table dropped.

 

SQL>