Cancel 刪除 正在使用的臨時表空間的操作 將導致異常

westzq1984發表於2013-08-29
系統的預設臨時表空間是無法刪除的，但是使用者的預設臨時表空間是可刪除的
但是，如果刪除時，這個表空間還被其他會話使用，那麼刪除的語句就會在TS上等待
就算中斷了刪除的會話，後續也會導致一些其他SQL語句出現異常。

如下：explain plan竟然會去請求TS6的鎖定，而柱塞者是SMON
SMON以模式3持有這個臨時表空間上的鎖定是正常效能。

SQL> @we

  SID NAME     OSUSER   PROGRAM         EVENT                     P1P2                 BLKING   STATE      SQL_ID          OPCOD
----- -------- -------- --------------- ------------------------- -------------------- -------- ---------- --------------- -----
   10 CTAIS2   oracle   sqlplus@zhangqi enq: TS - contention      1414725638.7         1.66.V   WAITING    9kmmjw9ab10za   Expla
  196 SYS      oracle   sqlplus@zhangqi SQL*Net message from clie 1650815232.1                  WAITED SHO 4tzjv0x70g3hs   Query

SQL> select sql_text from v$sql where sql_id='9kmmjw9ab10za';

SQL_TEXT
-------------------------------------------------
explain plan for select * from dual

SQL> select program from v$session where sid=66;

PROGRAM
---------------
oracle@zhangqiaoc (SMON)

SQL> select * from v$lock where type='TS';

ADDR             KADDR              SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ----- -- ---------- ---------- ---------- ---------- ---------- ----------
000000009F112450 000000009F1124A8    66 TS          8          1          3          0      11834          0
000000009F1137C0 000000009F113818    66 TS          7          1          3          0       6095          1
000000009D256378 000000009D256450    10 TS          7          1          0          6         93          0

SQL> select * from v$lock where sid=10;

ADDR             KADDR              SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ----- -- ---------- ---------- ---------- ---------- ---------- ----------
000000009F1130A8 000000009F113100    10 TO       5124          2          3          0        113          0
000000009F113188 000000009F1131E0    10 AE        100          0          4          0        461          0
000000009F113348 000000009F1133A0    10 TT          7         16          4          0        113          0
000000009F113440 000000009F113498    10 TO       5124          1          3          0        113          0
00002AE948861E98 00002AE948861EF8    10 TM       5124          0          3          0        113          0
000000009D256378 000000009D256450    10 TS          7          1          0          6        113          0
000000009D256378 000000009D2564B8    10 SS          7          1          6          0        113          0

並且，卡住的會話還在臨時表空間上一直持有SS6鎖定，可能影響後續的很多動作

解決的辦法是，切換該使用者的臨時表空間到另外一個臨時表空間
KILL已經被柱塞的會話

刪除前，一定要查詢v$sort_usage，確認已經沒有會話在使用準備刪除的臨時表空間

PS：20130904 測試發現

DROP TABLESPACE語句一旦發出，就算中斷也需要清理
後續新執行的會話，將發起清理（可能是他做，也可能是請求SMON做）
但是DROP TBS以前的佔用臨時表空間會話未退出，將阻塞這次清理，從而發生問題

操作LOB.XML的動作，都可能導致TEMP段被長期持有

解決的辦法，也可以是查詢v$sort_usage，把使用這個TEMP表空間的會話幹掉。然後等PMON回收掉這些會話的資源

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8242091/viewspace-769625/，如需轉載，請註明出處，否則將追究法律責任。

相關文章