ORA-01652：無法通過128(在表空間TEMP中)擴充套件temp段

風靈使發表於2018-09-26
套件

在Oracle資料庫中進行order by or group by、索引的建立和重建立、distinct操作、union & intersect & minus sort-merge joins、Analyze 操作、異常等操作時，會產生很多的臨時資料。如有一張員工資訊表，資料庫中是安裝記錄建立的時間來儲存的。如果使用者查詢時，使用Order BY排序語句指定按員工編號來排序，那麼排序後產生的所有記錄就是臨時資料。通常情況下，Oracle資料庫會先將這些臨時資料存放到記憶體的PGA(程式全域性區)內。如果資料量太大，PGA存不了，則會放入臨時表空間。

預設情況下，臨時表空間對所有使用者是共享的。當然可以為特殊使用者指定單獨的臨時表空間。臨時表空間可以重用。

當臨時表空間不足的時候，就會報：ORA-01652：無法通過128(在表空間TEMP中)擴充套件temp段

這個時候，想當然的就想看看臨時表空間的使用情況了：

select tablespace_name, bytes, user_bytes, user_bytes/bytes,file_name from dba_temp_files;
```　　 
![在這裡插入圖片描述](https://img-blog.csdn.net/2018092609295569)
`99%?`但是其實這個使用率並沒有太大的意義，因為當新增一個臨時檔案(如`TEMP02.DBF`)，然後再跑一次`order by`，這個使用率就直接飆到`99%`了。然後換其他的`order by`語句測試，發現還是可以正常查出資料，說明臨時表空間的使用率跟正常的表空間使用率，有所不一樣(具體怎麼個不一樣，就不得而知了)。

也就是說，報錯的時候，它的使用率可能是`99%`；正常執行的時候，使用率也可能是`99%`。

當報錯的時候，就說明臨時表空間真的不足了。但由於臨時表空間表現為的是`dbf`檔案，那擴充套件空間也就方便多了。可以直接增大原臨時檔案，還可以增加臨時檔案以達到擴大臨時表空間的目的。如果你的臨時表空間滿得太快，這種方式就治標不治本了。可以通過`v$sort_usage`和`v$sort_segment`兩個檢視，分析出是哪些使用者和哪些`sql`導致臨時表空間暴漲，再針對性解決。除此之外，優化`sql`以及對查詢的欄位建立索引，也可以減少臨時表空間的佔用。在Oracle中，如果表沒有索引，那麼會將所有的資料都複製到臨時表空間，而如果有索引的話，一般只是將索引的資料複製到臨時表空間中。

除了以上這種臨時表空間真正滿了的情況，查詢會報錯以外，還有一種情況，也會報同樣的錯，那就是臨時檔案`offline`了。
![在這裡插入圖片描述](https://img-blog.csdn.net/20180926094330645)
在`v_$tempfile`檢視中，記錄了臨時檔案的狀態，一種是`online`，一種是`offline`。當狀態為`offline`的時候，查詢也有可能會報這個錯。

當然，如果一個臨時檔案`offline`了，查詢大小時，也是查不出來的。
![在這裡插入圖片描述](https://img-blog.csdn.net/20180926094416927)

**與臨時表空間相關的語句：**

```sql
　　--查詢使用者所使用的臨時表空間：
　　select username,default_tablespace,temporary_tablespace from dba_users;
 
　　--查詢臨時表空間大小以及使用率：
　　select tablespace_name, bytes, user_bytes, user_bytes/bytes,file_name from dba_temp_files;
 
　　--查詢臨時檔案是否線上：
　　select name,status from v$tempfile;
 
　　--修改臨時檔案線上(離線)狀態：
　　alter database tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP02.DBF' online(offline);
 
　　--增加臨時檔案大小(增加原檔案)：
　　alter database tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF' resize 100m;
 
　　--通過增加新的臨時檔案，來擴大臨時表空間：
　　alter tablespace temp add tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP02.DBF' size 4000m;
 
　　--刪除臨時檔案：
　　alter database tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP02.DBF' drop;
 
　　--將臨時檔案設定為自動擴充套件：
　　alter database tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF' autoextend on next 5m maxsize unlimited;
 
　　--關閉(啟動)臨時檔案的自動增長：
 　 alter database tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF' autoextend off(on);
 　

相關文章