解決oracle臨時表空間的報錯

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

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

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

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

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

解決辦法：我們知道由于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，達到最大文件大小，只能添加文件。

SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/CP7PV1DB/temp03.dbf' size 4G autoextend on next 128M maxsize 6G;

SQL> ALTER TABLESPACE TEMP DROP TEMPFILE '/u01/app/oracle/oradata/CP7PV1DB/temp03.dbf';

SQL> ALTER DATABASE TEMPFILE  '/u01/app/oracle/oradata/CP7PV1DB/temp03.dbf' RESIZE 6G;

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;

先創建一個臨時表空間，把這個表空間設置為默認的臨時表空間，然后把以前的臨時表空間刪掉，再把數據文件刪掉，很簡單。

經驗：

• Oracle表空間中的數據文件只能增加不能刪除.;可以移動數據文件,把表空間的指針rename一下.默認的臨時表空間不能脫機.