解決臨時(shí)表空間的報(bào)錯(cuò)
報(bào)錯(cuò)信息：[HY000](1652) [Oracle][ODBC][Ora]ORA-01652: 無法通過 128 (在表空間 TEMP 中) 擴(kuò)展 temp 段
原因分析：oracle臨時(shí)表空間不足，事務(wù)執(zhí)行一般將會(huì)報(bào)ora-01652無法擴(kuò)展臨時(shí)段的錯(cuò)誤。因?yàn)閛racle總是盡量分配連續(xù)空間，一旦沒有足夠的可分配空間或者分配不連續(xù)就會(huì)出現(xiàn)上述的現(xiàn)象。
回憶下臨時(shí)表空間的作用：
（1）臨時(shí)表空間主要用途是在數(shù)據(jù)庫進(jìn)行排序運(yùn)算[如創(chuàng)建索引、order by及group by、distinct、union/intersect/minus/、sort-merge及join、analyze命令]、管理索引[如創(chuàng)建索引、IMP進(jìn)行數(shù)據(jù)導(dǎo)入]、訪問視圖等操作時(shí)提供臨時(shí)的運(yùn)算空間，當(dāng)運(yùn)算完成之后系統(tǒng)會(huì)自動(dòng)清理。
（2）當(dāng)臨時(shí)表空間不足時(shí)，表現(xiàn)為運(yùn)算速度異常的慢，并且臨時(shí)表空間迅速增長到***空間(擴(kuò)展的極限)，并且一般不會(huì)自動(dòng)清理了。
解決辦法：我們知道由于ORACLE將表空間作為邏輯結(jié)構(gòu)-單元，而表空間的物理結(jié)構(gòu)是數(shù)據(jù)文件，數(shù)據(jù)文件在磁盤上物理地創(chuàng)建，表空間的所有對(duì)象也存在于磁盤上，為了給表空間增加空間，就必須增加數(shù)據(jù)文件。先查看一下指定表空間的可用空間，使用視圖SYS.DBA_FREE_SPACE，視圖中每條記錄代表可用空間的碎片大小。當(dāng)然也可以擴(kuò)展表空間。
1、增大臨時(shí)表空間(或增加臨時(shí)表空間文件)。
2、設(shè)置臨時(shí)數(shù)據(jù)文件自動(dòng)擴(kuò)展
步驟：
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á)到***文件大小，只能添加文件。
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í)表空間(補(bǔ)充)
- 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)表空間，然后刪除原來的臨時(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語句
- 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專欄作者“孫杰”原創(chuàng)稿件，轉(zhuǎn)載請(qǐng)聯(lián)系原作者】
