临时表空间过大的解决方法

Temporary tablespace是oracle里临时表空间，临时表空间主要用途是在数据库进行排序运算、管理索引、访问视图等操作时提供临时的运算空间，当运算完成之后系统会自动清理。当oracle里需要用到sort的时候，PGA中sort_area_size大小不够时，将会把数据放入临时表空间里进行排序，同时如果有异常情况的话，也会被放入临时表空间,正常来说，在完成Select语句、create index等一些使用TEMP表空间的排序操作后，Oracle是会自动释放掉临时段的。
但有些有侯我们则会遇到临时段没有被释放，TEMP表空间几乎满的状况，甚至是我们重启了数据库仍没有解决问题。下面介绍一下此种情况的处理方法。
yang@rac1>conn system/yang as sysdba
已连接。
sys@rac1>@showtbs.sql

TABLESPACE_NAME  STATUS    CONTENTS  EXTENT_MAN   total(M)    free(M)    used(M) utilization%
---------------- --------- --------- ---------- ---------- ---------- ---------- ------------
SYSAUX           ONLINE    PERMANENT LOCAL             683         35        648  94.83
UNDOTBS1         ONLINE    UNDO      LOCAL              80         50         30  37.89
USERS            ONLINE    PERMANENT LOCAL            1231       1228          4  .29
SYSTEM           ONLINE    PERMANENT LOCAL            1170        416        754  64.41
EXAMPLE          ONLINE    PERMANENT LOCAL             300        222         78  25.9
TEMP             ONLINE    TEMPORARY LOCAL              97          1         96  98.97

已选择6行。

TABLESPACE_NAME      BLOCK_SIZE CONTENTS  EXTENT_MAN ALLOCATIO SEGMEN STATUS    LOGGING   FOR PLU
-------------------- ---------- --------- ---------- --------- ------ --------- --------- --- ---
SYSTEM                     8192 PERMANENT LOCAL      SYSTEM    MANUAL ONLINE    LOGGING   NO  NO
SYSAUX                     8192 PERMANENT LOCAL      SYSTEM    AUTO   ONLINE    LOGGING   NO  NO
UNDOTBS1                   8192 UNDO      LOCAL      SYSTEM    MANUAL ONLINE    LOGGING   NO  NO
TEMP                       8192 TEMPORARY LOCAL      UNIFORM   MANUAL ONLINE    NOLOGGING NO  NO
USERS                      8192 PERMANENT LOCAL      SYSTEM    AUTO   ONLINE    LOGGING   NO  NO
EXAMPLE                    8192 PERMANENT LOCAL      SYSTEM    AUTO   ONLINE    NOLOGGING NO  YES

已选择6行。

TABLESPACE_NAME      INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE
-------------------- -------------- ----------- ----------- ----------- ------------
SYSTEM                        65536                       1  2147483645
SYSAUX                        65536                       1  2147483645
UNDOTBS1                      65536                       1  2147483645
TEMP                        1048576     1048576           1                        0
USERS                         65536                       1  2147483645
EXAMPLE                       65536                       1  2147483645

已选择6行。
解决办法：
1 创建一个可共替换的临时表空间temp02
sys@rac1>create temporary tablespace temp02 tempfile 'd:\oracle\oradata\oracl\temp02.dbf' size 512m
autoextend on next 1m maxsize unlimited;
表空间已创建。
2 更改数据库的默认临时表空间
sys@rac1>alter database default temporary tablespace temp02;
数据库已更改。
3 删除使用过大的临时表空间
sys@rac1>drop tablespace temp including contents and datafiles;
表空间已删除。
4 创建新的临时表空间
sys@rac1>create temporary tablespace temp tempfile 'd:\oracle\oradata\oracl\temp01.dbf' size 512m au
toextend on next 1m maxsize unlimited;
表空间已创建。
5 重置数据库的临时表空间
sys@rac1>alter database default temporary tablespace temp;
数据库已更改。
6 删除临时替代的临时表空间。
sys@rac1>drop tablespace temp02 including contents and datafiles;
表空间已删除。
sys@rac1>@showtbs.sql
TABLESPACE_NAME      STATUS    CONTENTS  EXTENT_MAN   total(M)    free(M)    used(M) utilization%
-------------------- --------- --------- ---------- ---------- ---------- ---------- ------------
SYSAUX               ONLINE    PERMANENT LOCAL             683         35        648        94.83
UNDOTBS1             ONLINE    UNDO      LOCAL              80         50         30        37.89
USERS                ONLINE    PERMANENT LOCAL            1231       1228          4          .29
SYSTEM               ONLINE    PERMANENT LOCAL            1170        416        754        64.41
EXAMPLE              ONLINE    PERMANENT LOCAL             300        222         78         25.9
TEMP                 ONLINE    TEMPORARY LOCAL             512

已选择6行。
TABLESPACE_NAME      BLOCK_SIZE CONTENTS  EXTENT_MAN ALLOCATIO SEGMEN STATUS    LOGGING   FOR PLU
-------------------- ---------- --------- ---------- --------- ------ --------- --------- --- ---
SYSTEM                     8192 PERMANENT LOCAL      SYSTEM    MANUAL ONLINE    LOGGING   NO  NO
SYSAUX                     8192 PERMANENT LOCAL      SYSTEM    AUTO   ONLINE    LOGGING   NO  NO
UNDOTBS1                   8192 UNDO      LOCAL      SYSTEM    MANUAL ONLINE    LOGGING   NO  NO
TEMP                       8192 TEMPORARY LOCAL      UNIFORM   MANUAL ONLINE    NOLOGGING NO  NO
USERS                      8192 PERMANENT LOCAL      SYSTEM    AUTO   ONLINE    LOGGING   NO  NO
EXAMPLE                    8192 PERMANENT LOCAL      SYSTEM    AUTO   ONLINE    NOLOGGING NO  YES
已选择6行。
TABLESPACE_NAME      INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE
-------------------- -------------- ----------- ----------- ----------- ------------
SYSTEM                        65536                       1  2147483645
SYSAUX                        65536                       1  2147483645
UNDOTBS1                      65536                       1  2147483645
TEMP                        1048576     1048576           1                        0
USERS                         65536                       1  2147483645
EXAMPLE                       65536                       1  2147483645

已选择6行。
sys@rac1>