報錯信息：[HY000](1652) [Oracle][ODBC][Ora]ORA-01652: 無法通過 128 (在表空間 TEMP 中) 擴展 temp 段

原因分析：oracle臨時表空間不足，事務執行一般將會報ora-01652無法擴展臨時段的錯誤。因為oracle總是盡量分配連續空間，一旦沒有足夠的可分配空間或者分配不連續就會出現上述的現象。

回憶下臨時表空間的作用：

（1）臨時表空間主要用途是在數據庫進行排序運算[如創建索引、order by及group by、distinct、union/intersect/minus/、sort-merge及join、analyze命令]、管理索引[如創建索引、IMP進行數據導入]、訪問視圖等操作時提供臨時的運算空間，當運算完成之后系統會自動清理。

（2）當臨時表空間不足時，表現為運算速度異常的慢，并且臨時表空間迅速增長到***空間(擴展的極限)，并且一般不會自動清理了。

解決辦法：我們知道由于ORACLE將表空間作為邏輯結構-單元，而表空間的物理結構是數據文件，數據文件在磁盤上物理地創建，表空間的所有對象也存在于磁盤上，為了給表空間增加空間，就必須增加數據文件。先查看一下指定表空間的可用空間，使用視圖SYS.DBA_FREE_SPACE，視圖中每條記錄代表可用空間的碎片大小。當然也可以擴展表空間。

1、增大臨時表空間(或增加臨時表空間文件)。

2、設置臨時數據文件自動擴展

步驟：

1、 查詢臨時表空間狀態:

SQL> col file_name for a20; 
SQL> select tablespace_name,file_name,bytes/1024/1024file_size,autoextensible from dba_temp_files;  

 

2、 擴展臨時表空間

SQL> alter database tempfile '/u01/app/oracle/oradata/CP7PV1DB/temp01.dbf'resize 8192m; 

或也可增加臨時表空間文件

alter tablespace temp add tempfile‘/u01/app/oracle/oradata/CP7PV1DB/temp02.dbf’ size 8192m; 

注：臨時表空間文件如果已經32G，達到***文件大小，只能添加文件。

3、 設置自動擴展

SQL> alter database tempfile'/u01/app/oracle/oradata/CP7PV1DB/temp01.dbf' autoextend on next 10m maxsizeunlimited; 

4、 擴展表空間時的報錯

ERROR atline 1: 
ORA-00376:file 201 cannot be read at this time 
ORA-01110:data file 201: '/u01/app/oracle/oradata/CP7PV1DB/temp01.dbf' 

原因是臨時表空間不知道什么原因offline了，修改為online后修改成功。

SQL>  alter database tempfile ‘/u01/app/oracle/oradata/CP7PV1DB/temp01.dbf’online; 
Database altered. 

5、 刪除臨時表空間(補充)

SQL>drop tablespace temp01  including contents and datafiles; 
SQL> ALTER DATABASE TEMPFILE  '/u01/app/oracle/oradata/CP7PV1DB/temp01.dbf' DROPINCLUDING DATAFILES; 
Database altered. 

注意：刪除臨時表空間的臨時數據文件時，不需要指定INCLUDING DATAFILES 選項也會真正刪除物理文件，否則需要手工刪除物理文件。也不能直接刪除當前用戶的默認表空間，否則會報ORA-12906錯誤。如果需要刪除某一個默認的臨時表空間，則必須先創建一個臨時表空間，然后指定新創建的表空間為默認表空間，然后刪除原來的臨時表空間。

6、 更改系統默認的臨時表空間

--查詢默認臨時表空間 
SQL> select *from database_properties where property_name='DEFAULT_TEMP_TABLESPACE'; 
PROPERTY_NAME                  PROPERTY_VALUE       DESCRIPTION 
-------------------------------------------------- -------------------------------------------------- 
DEFAULT_TEMP_TABLESPACE        TEMP       Name of default temporary tablespace 
--修改默認臨時表空間 
SQL> alterdatabase default temporary tablespace temp02; 
Databasealtered. 

我們可以查詢是否切換為TEMP02：

SQL> select *from database_properties where property_name='DEFAULT_TEMP_TABLESPACE'; 
PROPERTY_NAME                  PROPERTY_VALUE       DESCRIPTION 
-------------------------------------------------- ---------------------------------------- 
DEFAULT_TEMP_TABLESPACE        TEMP02     Name of default temporary tablespace 

7、 查看臨時表空間的使用率

SQL>SELECT temp_used.tablespace_name, 
           total - used as "Free", 
           total as "Total", 
           round(nvl(total - used, 0) * 100 /total, 3) "Free percent" 
      FROM (SELECT tablespace_name,SUM(bytes_used) / 1024 / 1024 used 
              FROM GV_$TEMP_SPACE_HEADER 
             GROUP BY tablespace_name) temp_used, 
           (SELECT tablespace_name, SUM(bytes) /1024 / 1024 total 
              FROM dba_temp_files 
           GROUP BY tablespace_name) temp_total 
    WHEREtemp_used.tablespace_name = temp_total.tablespace_name; 
  
TABLESPACE_NAME                      Free      Total Free percent 
---------------------------------------- ---------- ------------ 
TEMP                                 6876       8192       83.936 

8、 查找消耗資源比較多的sql語句

Select se.username, 
       se.sid, 
       su.extents, 
       su.blocks * to_number(rtrim(p.value)) asSpace, 
       tablespace, 
       segtype, 
       sql_text 
  from v$sort_usage su, v$parameter p, v$session se, v$sql s 
 where p.name = 'db_block_size' 
   and su.session_addr = se.saddr 
   and s.hash_value = su.sqlhash 
   and s.address = su.sqladdr 
 order by se.username, se.sid; 

【本文為51CTO專欄作者“孫杰”原創稿件，轉載請聯系原作者】