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

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

--关闭自动增长

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75

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 发现大小并未回收，说明这部分空间正在被使用。