數(shù)據(jù)世界并不令人驚訝，我們經(jīng)常遇到常識(shí)之外的問(wèn)題。面對(duì)一些奇怪的問(wèn)題，需要大多數(shù)DBA對(duì)數(shù)據(jù)庫(kù)的內(nèi)部機(jī)制有深刻的了解，消除迷霧并找到問(wèn)題的所在。在眾多數(shù)據(jù)庫(kù)當(dāng)中，Oracle數(shù)據(jù)庫(kù)是我們比較熟悉的。但是在Oracle數(shù)據(jù)庫(kù)使用過(guò)程中，總是會(huì)出現(xiàn)這樣或者那樣的問(wèn)題，今天我們一起來(lái)盤點(diǎn)一下Oracle數(shù)據(jù)庫(kù)中，Oracle臨時(shí)表空間過(guò)大怎么辦？如何解決Oracle臨時(shí)表空間的報(bào)錯(cuò)問(wèn)題？

　　Oracle臨時(shí)表空間過(guò)大怎么辦？

臨時(shí)表空間主要使用在：

- 索引創(chuàng)建或重創(chuàng)建。

- ORDER BY or GROUP BY 。

- DISTINCT 操作。

- UNION & INTERSECT & MINUS 。

- Sort-Merge joins。

- Analyze 操作。

- 有些異常將會(huì)引起temp暴漲 。

下面是重新創(chuàng)建一個(gè)臨時(shí)表空間，把原來(lái)的默認(rèn)臨時(shí)表空間drop掉(包括里面的臨時(shí)數(shù)據(jù)文件)再重新建立。

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.

注意：由于臨時(shí)表空間的數(shù)據(jù)文件比較大，所以這步可能會(huì)花費(fèi)比較長(zhǎng)的時(shí)間

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

以上的方法只是暫時(shí)釋放了臨時(shí)表空間的磁盤占用空間，是治標(biāo)但不是治本的方法，真正的治本的方法是找出數(shù)據(jù)庫(kù)中消耗資源比較大的sql語(yǔ)句，然后對(duì)其進(jìn)行優(yōu)化處理。

如何解決Oracle 臨時(shí)表空間的報(bào)錯(cuò)問(wèn)題？

　　1、 查詢臨時(shí)表空間狀態(tài)：

SQL> col file_name for a20;

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

　　2、 擴(kuò)展臨時(shí)表空間

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

　　或也可增加臨時(shí)表空間文件

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

注：臨時(shí)表空間文件如果已經(jīng)32G，達(dá)到最大文件大小，只能添加文件。

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、 設(shè)置自動(dòng)擴(kuò)展

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

　　4、 擴(kuò)展表空間時(shí)的報(bào)錯(cuò)

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'

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

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

Database altered.

　　5、 刪除臨時(shí)表空間

SQL>drop tablespace temp01 including contents and datafiles;

SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/CP7PV1DB/temp01.dbf' DROPINCLUDING DATAFILES;

Database altered.

注意：刪除臨時(shí)表空間的臨時(shí)數(shù)據(jù)文件時(shí)，不需要指定INCLUDING DATAFILES 選項(xiàng)也會(huì)真正刪除物理文件，否則需要手工刪除物理文件。也不能直接刪除當(dāng)前用戶的默認(rèn)表空間，否則會(huì)報(bào)ORA-12906錯(cuò)誤。如果需要?jiǎng)h除某一個(gè)默認(rèn)的臨時(shí)表空間，則必須先創(chuàng)建一個(gè)臨時(shí)表空間，然后指定新創(chuàng)建的表空間為默認(rèn)表空間，然后刪除原來(lái)的臨時(shí)表空間。

　　6、 更改系統(tǒng)默認(rèn)的臨時(shí)表空間

--查詢默認(rèn)臨時(shí)表空間

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

--修改默認(rèn)臨時(shí)表空間

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、 查看臨時(shí)表空間的使用率

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語(yǔ)句

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;

先創(chuàng)建一個(gè)臨時(shí)表空間，把這個(gè)表空間設(shè)置為默認(rèn)的臨時(shí)表空間，然后把以前的臨時(shí)表空間刪掉，再把數(shù)據(jù)文件刪掉，很簡(jiǎn)單。

上述就是關(guān)于Oracle臨時(shí)表空間過(guò)大怎么辦，以及如何解決表空間的臨時(shí)報(bào)錯(cuò)問(wèn)題的全部?jī)?nèi)容介紹，想了解更多關(guān)于Oracle數(shù)據(jù)庫(kù)的信息，請(qǐng)繼續(xù)關(guān)注中培偉業(yè)吧。