數據世界并不令人驚訝，我們經常遇到常識之外的問題。面對一些奇怪的問題，需要大多數DBA對數據庫的內部機制有深刻的了解，消除迷霧并找到問題的所在。在眾多數據庫當中，Oracle數據庫是我們比較熟悉的。但是在Oracle數據庫使用過程中，總是會出現這樣或者那樣的問題，今天我們一起來盤點一下Oracle數據庫中，Oracle臨時表空間過大怎么辦？如何解決Oracle臨時表空間的報錯問題？

　　Oracle臨時表空間過大怎么辦？

臨時表空間主要使用在：

- 索引創建或重創建。

- ORDER BY or GROUP BY 。

- DISTINCT 操作。

- UNION & INTERSECT & MINUS 。

- Sort-Merge joins。

- Analyze 操作。

- 有些異常將會引起temp暴漲 。

下面是重新創建一個臨時表空間，把原來的默認臨時表空間drop掉(包括里面的臨時數據文件)再重新建立。

SQL> create temporary tablespace temp2

2 tempfile '/home/oracle/oracle/product/10.2.0/oradata/hatest/temp02.pdf' size 512M reuse

3 autoextend on next 640k maxsize unlimited;

Tablespace created.

SQL> alter database default temporary tablespace temp2;

Database altered.

SQL> drop tablespace temp including contents and datafiles;

Tablespace dropped.

注意：由于臨時表空間的數據文件比較大，所以這步可能會花費比較長的時間

SQL> create temporary tablespace temp

2 tempfile '/home/oracle/oracle/product/10.2.0/oradata/hatest/temp01.pdf' size 512M reuse

3 autoextend on next 640K maxsize unlimited;

Tablespace created.

SQL> alter database default temporary tablespace temp;

Database altered.

SQL> drop tablespace temp2 including contents and datafiles;

Tablespace dropped.

SQL> exit

以上的方法只是暫時釋放了臨時表空間的磁盤占用空間，是治標但不是治本的方法，真正的治本的方法是找出數據庫中消耗資源比較大的sql語句，然后對其進行優化處理。

如何解決Oracle 臨時表空間的報錯問題？

　　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臨時表空間過大怎么辦，以及如何解決表空間的臨時報錯問題的全部內容介紹，想了解更多關于Oracle數據庫的信息，請繼續關注中培偉業吧。