查詢鎖表的進程、分析并kill掉

SQL> drop table TMP_MANHOUR_LOAD_NEW;
 
drop table TMP_MANHOUR_LOAD_NEW
 
ORA-14452: 嘗試建立、更新或刪除已在使用中的索引或暫時表格

SQL> select object_id from  user_objects where object_name=upper('TMP_MANHOUR_LOAD_NEW');
 
 OBJECT_ID
----------
     48791
  
SQL> select * from v$lock where id1=48791;
 
ADDR     KADDR           SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
92C5308C 92C5309C        153 TO        48791          1          3          0    1983406          0
 
SQL> select * from v$session where sid=153;
 
SADDR           SID    SERIAL#     AUDSID PADDR         USER# USERNAME                          COMMAND    OWNERID TADDR    LOCKWAIT STATUS   SERVER       SCHEMA# SCHEMANAME                     OSUSER                         PROCESS      MACHINE                                                          TERMINAL                       PROGRAM                                          TYPE       SQL_ADDRESS SQL_HASH_VALUE PREV_SQL_ADDR PREV_HASH_VALUE MODULE                                           MODULE_HASH ACTION                           ACTION_HASH CLIENT_INFO                                                      FIXED_TABLE_SEQUENCE ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LOGON_TIME  LAST_CALL_ET PDML_ENABLED FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER RESOURCE_CONSUMER_GROUP          PDML_STATUS PDDL_STATUS PQ_STATUS CURRENT_QUEUE_DURATION CLIENT_IDENTIFIER
-------- ---------- ---------- ---------- -------- ---------- ------------------------------ ---------- ---------- -------- -------- -------- --------- ---------- ------------------------------ ------------------------------ ------------ ---------------------------------------------------------------- ------------------------------ ------------------------------------------------ ---------- ----------- -------------- ------------- --------------- ------------------------------------------------ ----------- -------------------------------- ----------- ---------------------------------------------------------------- -------------------- ------------- -------------- --------------- ------------- ----------- ------------ ------------ ------------- --------------- ----------- -------------------------------- ----------- ----------- --------- ---------------------- ----------------------------------------------------------------
92ADA454        153      20039          0 92A0F290         96 FMPMS                                   0 2147483644 93C7FC88          ACTIVE   DEDICATED         96 FMPMS                                                                                                                                                                                                                       USER       95EE9CC4        1352309931 96EF7E80           4166735688                                                            0                                            0                                                                               1385637            -1              0               0             0 2012/5/27 上      1983454 NO           NONE          NONE            NO                                           DISABLED    ENABLED     ENABLED                        0
可以看出這個進程是2012/5/27就開始的，死進程干掉它。 

SQL> alter system kill session '153,20039';
 
alter system kill session '153,20039'
 
ORA-00031: 標示為要終止的階段作業

SQL> select * from v$session where sid=153;
 
SADDR           SID    SERIAL#     AUDSID PADDR         USER# USERNAME                          COMMAND    OWNERID TADDR    LOCKWAIT STATUS   SERVER       SCHEMA# SCHEMANAME                     OSUSER                         PROCESS      MACHINE                                                          TERMINAL                       PROGRAM                                          TYPE       SQL_ADDRESS SQL_HASH_VALUE PREV_SQL_ADDR PREV_HASH_VALUE MODULE                                           MODULE_HASH ACTION                           ACTION_HASH CLIENT_INFO                                                      FIXED_TABLE_SEQUENCE ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LOGON_TIME  LAST_CALL_ET PDML_ENABLED FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER RESOURCE_CONSUMER_GROUP          PDML_STATUS PDDL_STATUS PQ_STATUS CURRENT_QUEUE_DURATION CLIENT_IDENTIFIER
-------- ---------- ---------- ---------- -------- ---------- ------------------------------ ---------- ---------- -------- -------- -------- --------- ---------- ------------------------------ ------------------------------ ------------ ---------------------------------------------------------------- ------------------------------ ------------------------------------------------ ---------- ----------- -------------- ------------- --------------- ------------------------------------------------ ----------- -------------------------------- ----------- ---------------------------------------------------------------- -------------------- ------------- -------------- --------------- ------------- ----------- ------------ ------------ ------------- --------------- ----------- -------------------------------- ----------- ----------- --------- ---------------------- ----------------------------------------------------------------
92ADA454        153      20039          0 92A0F290         96 FMPMS                                   0 2147483644 93C7FC88          KILLED   DEDICATED         96 FMPMS                                                                                                                                                                                                                       USER       95EE9CC4        1352309931 96EF7E80           4166735688                                                            0                                            0                                                                               1385637            -1              0               0             0 2012/5/27 上      1984067 NO           NONE          NONE            NO                                           DISABLED    ENABLED     ENABLED                        0
 
SQL> select spid,osuser,s.program from v$session s,v$process p where s.paddr=p.addr and s.sid=153;
 
SPID         OSUSER                         PROGRAM
------------ ------------------------------ ------------------------------------------------
19380           

在操作系統root下Kill命令殺死系統進程：

[root@hahrdb root]# kill -9 19380
[root@hahrdb root]# ps -ef|grep 19380
root     28655 18986  0 10:31 pts/0    00:00:00 grep 19380

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16381228/viewspace-733234/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16381228/viewspace-733234/