數(shù)據(jù)庫(kù)temp臨時(shí)表空間增大，一般在數(shù)據(jù)安裝時(shí)設(shè)置臨時(shí)表空間大小，默認(rèn)的情況下都是設(shè)置為自動(dòng)增長(zhǎng)。這樣會(huì)引起一個(gè)問(wèn)題：在數(shù)據(jù)庫(kù)使用中temp表空間會(huì)自動(dòng)擴(kuò)展的越來(lái)越大，造成磁盤(pán)空間使用不足。
引起臨時(shí)表空間增大主要使用在以下幾種情況：
1、order by or group by (disc sort占主要部分)；
2、索引的創(chuàng)建和重創(chuàng)建；
3、distinct操作；
4、union & intersect & minus sort-merge joins；
5、Analyze 操作；
6、有些異常也會(huì)引起TEMP的暴漲。
解決temp臨時(shí)表空間大小問(wèn)題：
一.直接縮小temp表空間大小
alter database tempfile 'd:powerdmsdb\dmsdb\temp01.dbf' resize 1024M;
此語(yǔ)句會(huì)直接修改temp表空間的大小，但可能會(huì)執(zhí)行不成功，因?yàn)楫?dāng)temp使用率為100%或當(dāng)前有會(huì)話占用時(shí)，temp表空間是無(wú)法縮小的。
查詢(xún)當(dāng)前會(huì)話：
SELECT se.username,
se.sid,
se.serial#,
se.sql_address,
se.machine,
se.program,
su.tablespace,
su.SEGTYPE,
su.CONTENTS
FROM v$session se,
v$sort_usage su
WHERE se.saddr=su.session_addr
利用此sql語(yǔ)句查詢(xún)當(dāng)前會(huì)話，然后kill當(dāng)前會(huì)話：
Alter system kill session 'sid,serial#'
執(zhí)行此語(yǔ)句后再對(duì)temp表空間resize空間大小就可以了。
注：此方法只是對(duì)temp表空間做臨時(shí)性的縮小，以后還會(huì)繼續(xù)增大。
方法二：
對(duì)temp臨時(shí)表空間重建并設(shè)置增長(zhǎng)上限值，設(shè)置這個(gè)值時(shí)要預(yù)先估算設(shè)置合理，不然當(dāng)增大到此值時(shí)會(huì)出錯(cuò)，在合理的范圍內(nèi)增加到上限值時(shí)數(shù)據(jù)庫(kù)會(huì)自動(dòng)釋放temp臨時(shí)表空間。
重建temp臨時(shí)表空間：
1.--啟動(dòng)數(shù)據(jù)庫(kù)
startup
2.--創(chuàng)建中轉(zhuǎn)臨時(shí)表空間
create TEMPORARY TABLESPACE TEMP2 TEMPFILE 'D:\PowerDmsDB\DMSDB\TEMP02.DBF' SIZE 2048M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE 5120M;
3.--改變?nèi)笔∨R時(shí)表空間 為剛剛創(chuàng)建的新臨時(shí)表空間temp2
alter database default temporary tablespace temp2;
4.--刪除原來(lái)臨時(shí)表空間
drop tablespace temp including contents and datafiles;
5.--重新創(chuàng)建臨時(shí)表空間
create TEMPORARY TABLESPACE TEMP TEMPFILE 'D:\PowerDmsDB\DMSDB\TEMP01.DBF' SIZE 2048M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE 5120M;
6.--重置缺省臨時(shí)表空間為新建的temp表空間
alter database default temporary tablespace temp;
7.--刪除中轉(zhuǎn)用臨時(shí)表空間
drop tablespace temp2 including contents and datafiles;
8.--重新指定用戶(hù)表空間為重建的臨時(shí)表空間
alter user zxd temporary tablespace temp;
至此臨時(shí)表空間增長(zhǎng)過(guò)大可以更改完成。
下面是查詢(xún)?cè)趕ort排序區(qū)使用的執(zhí)行耗時(shí)的SQL:
Select se.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value))as Space,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
此語(yǔ)句可以做跟蹤查看分析時(shí)用。