临时表空间空间不足解决方法

--临时表空间空间不足解决方法

--关闭自动增长

SYS@PROD2> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE	11.2.0.3.0	Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SYS@PROD2> alter database tempfile '/u01/app/oracle/oradata/PROD2/temp02.dbf' autoextend off;

Database altered.

SYS@PROD2> alter database tempfile '/u01/app/oracle/oradata/PROD2/temp02.dbf' resize 2m;

Database altered.

SCOTT@PROD2> select * from temp1 order by 1,2,3,4,5,6,7,8,9;
select * from temp1 order by 1,2,3,4,5,6,7,8,9
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

SCOTT@PROD2> set autot trace exp 
SCOTT@PROD2> select * from temp1 order by 1,2,3,4,5,6,7,8,9;  

Execution Plan
----------------------------------------------------------
Plan hash value: 3339515882

------------------------------------------------------------------------------------
| Id  | Operation	   | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time	   |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	   |	10M|  1542M|	   |   361K  (1)| 01:12:18 |
|   1 |  SORT ORDER BY	   |	   |	10M|  1542M|  1904M|   361K  (1)| 01:12:18 |
|   2 |   TABLE ACCESS FULL| TEMP1 |	10M|  1542M|	   |  4909   (6)| 00:00:59 |
------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)
   
查看执行计划，发现估算需要1904M空间

SYS@PROD2> alter database tempfile '/u01/app/oracle/oradata/PROD2/temp02.dbf' resize 2000m;

Database altered.

SCOTT@PROD2> select * from temp1 order by 1,2,3,4,5,6,7,8,9;
54247 rows selected.  --时间太长中止了

但是通过resize很难准确调整，很容易导致过度使用的问题。

11g以前只能重建表空间替换原来临时表空间。

在11g中可以使用在线回收临时表空间的功能。

SYS@PROD2> ho ls -lh /u01/app/oracle/oradata/PROD2/temp02.dbf   --当前大小2G
-rw-r----- 1 oracle oinstall 2.0G Dec 13 16:55 /u01/app/oracle/oradata/PROD2/temp02.dbf

SYS@PROD2> alter tablespace temp shrink space;  --使用在线回收

Tablespace altered.

SYS@PROD2> ho ls -lh /u01/app/oracle/oradata/PROD2/temp02.dbf  --大小缩小为139M
-rw-r----- 1 oracle oinstall 139M Dec 13 17:01 /u01/app/oracle/oradata/PROD2/temp02.dbf

SYS@PROD2> alter tablespace temp shrink space keep 20m;  --通过KEEP语句指定回收大小

Tablespace altered.

SYS@PROD2> ho ls -lh /u01/app/oracle/oradata/PROD2/temp02.dbf
-rw-r----- 1 oracle oinstall 139M Dec 13 17:01 /u01/app/oracle/oradata/PROD2/temp02.dbf
发现大小并未回收，说明这部分空间正在被使用。