臨時表空間ORA-1652問題解決

1 某業務系統資料庫告警如下：

Tue Dec 14 16:50:14 2021

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP1 

2 在17：00 檢視臨時表空間，幾個節點進行檢視，使用率為 0% ；但為什麼會報臨時表空間不足？距離告警時間只有10分鐘。

節點 1

TABLESPACE_NAME                 Total(MB)   Used(MB)   Free(MB) Pct. Free(%)

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

TEMP1                             215040          0     215040          100

TEMP                              155647          3     155644          100

TEMP2                              28672          0      28672          100

 

節點 2 ：

TABLESPACE_NAME                 Total(MB)   Used(MB)   Free(MB) Pct. Free(%)

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

TEMP1                             215040        0     215040         100

TEMP                              155647        2     155645         100

TEMP2                              28672        0      28672         100

3   根據 ASH 檢視 16:40-16:50 執行的相關 SQL ，在節點 2 上發現如下 SQL 進行排序。

4 檢視相關 SQL 的執行計劃，發現進行排序耗費的臨時表空間為 18E ，即為 18*1024*1024G ，這明顯應該是寫的 SQL 有問題，產生笛卡爾積的結果。

5  使用 PLSQL 進行對相關 SQL 進行格式化，發現此 SQL 沒有 where 條件，故導致笛卡爾積。

select table_A.CONS_ID as "CONS_ID", table_A.CUST_ID as "CUST_ID",

        ...................

        '' as "TYPE_CODE"

   from table_A, table_B, table_C, table_D   order by  ORG_NO, table_A.CONS_NO

6  原因分析：

在查詢臨時表空間是否夠用？如果是RAC，則每個節點都需要進行查詢，原因為RAC環境下，臨時表空間為共用的，

即在節點1查詢臨時表空間是夠用的，在節點2有可能已經耗盡。如果事件已經發生，不能通過當前會話查詢出耗盡臨時表空間的SQL，那麼可以通過生成每個節點的ASH，檢視頂級事件中是否有sort關鍵字的SQL，並檢視執行計劃，來驗證是否耗盡臨時表空間。