ORACLE 临时表空间相关操作

    临时表空间主要用来数据库排序运算，索引处理，视图乖操作时，提供临时的运算空间，当运算完成后，系统会自动清理。排序时，如果PGA中的 sort_area_size不够，
将会把数据放入临时表空间进行排序。正常情况下，在排序等操作后，ORACLE会自动释放这些临时段，这里指将空间标记为空闲，以便复用。真正的磁盘空间并没有释放，
所以临时表空间会越来越大。
一、临时表空间上的操作
1.增加临时表空间文件。
SQL> select t.tablespace_name,t.contents from dba_tablespaces t;
 
TABLESPACE_NAME                CONTENTS
------------------------------ ---------
SYSTEM                         PERMANENT
SYSAUX                         PERMANENT
UNDOTBS1                       UNDO
USERS                          PERMANENT
UNDOTBS2                       UNDO
EXAMPLE                        PERMANENT
JERRY                          PERMANENT
BFTBS                          PERMANENT
EYGLE                          PERMANENT
RMAN                           PERMANENT
FBRA                           PERMANENT
TEMP01                         TEMPORARY
DEMO                           PERMANENT
 
13 rows selected
 
SQL> select * from v$tempfile;
 
     FILE# CREATION_CHANGE# CREATION_TIME        TS#     RFILE# STATUS  ENABLED         BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE NAME
---------- ---------------- ------------- ---------- ---------- ------- ---------- ---------- ---------- ------------ ---------- --------------------------------------------------------------------------------
         1          9135608 2014-01-01 17         16          1 ONLINE  READ WRITE  536870912      65536    536870912       8192 /u01/app/oracle/oradata/orcl/temp01.dbf
 
SQL> alter  tablespace temp01 add tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' size 10m reuse;
 
Tablespace altered


SQL> select * from v$tempfile;
 
     FILE# CREATION_CHANGE# CREATION_TIME        TS#     RFILE# STATUS  ENABLED         BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE NAME
---------- ---------------- ------------- ---------- ---------- ------- ---------- ---------- ---------- ------------ ---------- --------------------------------------------
         1          9135608 2014-01-01 17         16          1 ONLINE  READ WRITE  536870912      65536    536870912       8192 /u01/app/oracle/oradata/orcl/temp01.dbf
         2          9377322 2014-01-07 07         16          2 ONLINE  READ WRITE   10485760       1280     10485760       8192 /u01/app/oracle/oradata/orcl/temp02.dbf
 


临时表空间不可以被offline，但是其数据文件可以。


2.offline临时表空间报错
SQL> alter tablespace temp01 offline;
 
alter tablespace temp01 offline
 
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE


3.offline临时表空间中的数据文件
SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' offline;
 
Database altered
 
SQL> select * from v$tempfile;
 
     FILE# CREATION_CHANGE# CREATION_TIME        TS#     RFILE# STATUS  ENABLED         BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE NAME
---------- ---------------- ------------- ---------- ---------- ------- ---------- ---------- ---------- ------------ ---------- --------------------------------------------
         1          9135608 2014-01-01 17         16          1 OFFLINE READ WRITE          0          0    536870912       8192 /u01/app/oracle/oradata/orcl/temp01.dbf
         2          9377322 2014-01-07 07         16          2 ONLINE  READ WRITE   10485760       1280     10485760       8192 /u01/app/oracle/oradata/orcl/temp02.dbf
 
SQL> alter database tempfile  '/u01/app/oracle/oradata/orcl/temp01.dbf' online;
 
Database altered
 
SQL> select * from v$tempfile;
 
     FILE# CREATION_CHANGE# CREATION_TIME        TS#     RFILE# STATUS  ENABLED         BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE NAME
---------- ---------------- ------------- ---------- ---------- ------- ---------- ---------- ---------- ------------ ---------- --------------------------------------------
         1          9135608 2014-01-01 17         16          1 ONLINE  READ WRITE  536870912      65536    536870912       8192 /u01/app/oracle/oradata/orcl/temp01.dbf
         2          9377322 2014-01-07 07         16          2 ONLINE  READ WRITE   10485760       1280     10485760       8192 /u01/app/oracle/oradata/orcl/temp02.dbf  


3.resize表空间（即修改数据文件大小 ）
SQL> alter database tempfile  '/u01/app/oracle/oradata/orcl/temp02.dbf' resize 24m ;
 
Database altered


SQL> select t.file#,t.BYTES,t.BLOCKS,t.CREATE_BYTES,t.BLOCK_SIZE,t.NAME from v$tempfile t;
 
     FILE#      BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE NAME
---------- ---------- ---------- ------------ ---------- --------------------------------------------------------------------------------
         1  536870912      65536    536870912       8192 /u01/app/oracle/oradata/orcl/temp01.dbf
         2   25165824       3072     10485760       8192 /u01/app/oracle/oradata/orcl/temp02.dbf


4.删除临时表空间及对应的数据文件  
SQL> alter database tempfile  '/u01/app/oracle/oradata/orcl/temp02.dbf' drop including datafiles ;
 
Database altered
 
SQL> select t.file#,t.BYTES,t.BLOCKS,t.CREATE_BYTES,t.BLOCK_SIZE,t.NAME from v$tempfile t;
 
     FILE#      BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE NAME
---------- ---------- ---------- ------------ ---------- --------------------------------------------------------------------------------
         1  536870912      65536    536870912       8192 /u01/app/oracle/oradata/orcl/temp01.dbf
5.临时表空间文件命名步骤
5.1.offline临时表空间数据文件
SQL> select * from v$tempfile;
 
     FILE# CREATION_CHANGE# CREATION_TIME        TS#     RFILE# STATUS  ENABLED         BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE NAME
---------- ---------------- ------------- ---------- ---------- ------- ---------- ---------- ---------- ------------ ---------- ----------------------------------------
         1          9135608 2014-01-01 17         16          1 ONLINE  READ WRITE  536870912      65536    536870912       8192 /u01/app/oracle/oradata/orcl/temp01.dbf


SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' offline;
 
Database altered


SQL> select t.file#,t.BYTES,t.BLOCKS,t.CREATE_BYTES,t.BLOCK_SIZE,t.NAME,t.status from v$tempfile t;
 
     FILE#      BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE NAME                                                                             STATUS
---------- ---------- ---------- ------------ ---------- -------------------------------------------------------------------------------- -------
         1          0          0    536870912       8192 /u01/app/oracle/oradata/orcl/temp01.dbf                                          OFFLINE
5.2在操作系统上重新命名
 [root@pc-centos orcl]# cd /u01/app/oracle/oradata/orcl/
 [root@pc-centos orcl]# ls -rlt
total 6763640
-rw-r-----. 1 oracle oinstall    9781248 Jun 29  2013 20130629control01.ctl
-rw-r-----. 1 oracle oinstall    9781248 Jun 29  2013 20130629control02.ctl
-rw-r-----. 1 oracle oinstall    9781248 Jun 29  2013 20130629control03.ctl
-rw-r-----. 1 oracle oinstall   52429312 Jan  6 22:08 redo01.log
-rw-r-----. 1 oracle oinstall   52429312 Jan  7 05:00 redo02.log
-rw-r-----. 1 oracle oinstall  108011520 Jan  7 05:05 undotbs01.dbf
-rw-r-----. 1 oracle oinstall    6561792 Jan  7 05:05 users01.dbf
-rw-r-----. 1 oracle oinstall  104865792 Jan  7 05:05 example01.dbf
-rw-r-----. 1 oracle oinstall 2956992512 Jan  7 05:05 jerry.db
-rw-r-----. 1 oracle oinstall   10493952 Jan  7 05:05 bftbs01.dbf
-rw-r-----. 1 oracle oinstall  104865792 Jan  7 05:05 rman01.dbf
-rw-r-----. 1 oracle oinstall   52436992 Jan  7 05:05 eygle01.dbf
-rw-r-----. 1 oracle oinstall  104865792 Jan  7 05:05 fbra01.dbf
-rw-r-----. 1 oracle oinstall    5251072 Jan  7 08:02 demo01.dbf
-rw-r-----. 1 oracle oinstall  536879104 Jan  7 08:07 temp01.dbf
-rw-r-----. 1 oracle oinstall 2422218752 Jan  7 08:28 system01.dbf
-rw-r-----. 1 oracle oinstall  660611072 Jan  7 08:31 sysaux01.dbf
-rw-r-----. 1 oracle oinstall  104865792 Jan  7 08:31 undotbs02.dbf
-rw-r-----. 1 oracle oinstall   52429312 Jan  7 08:31 redo03.log
-rw-r-----. 1 oracle oinstall   10436608 Jan  7 08:31 control01.ctl
-rw-r-----. 1 oracle oinstall   10436608 Jan  7 08:31 control02.ctl
-rw-r-----. 1 oracle oinstall   10436608 Jan  7 08:31 control03.ctl
[root@pc-centos orcl]# mv temp01.dbf temp01new.dbf
[root@pc-centos orcl]# ls -rlt
total 6763640
-rw-r-----. 1 oracle oinstall    9781248 Jun 29  2013 20130629control01.ctl
-rw-r-----. 1 oracle oinstall    9781248 Jun 29  2013 20130629control02.ctl
-rw-r-----. 1 oracle oinstall    9781248 Jun 29  2013 20130629control03.ctl
-rw-r-----. 1 oracle oinstall   52429312 Jan  6 22:08 redo01.log
-rw-r-----. 1 oracle oinstall   52429312 Jan  7 05:00 redo02.log
-rw-r-----. 1 oracle oinstall  108011520 Jan  7 05:05 undotbs01.dbf
-rw-r-----. 1 oracle oinstall    6561792 Jan  7 05:05 users01.dbf
-rw-r-----. 1 oracle oinstall  104865792 Jan  7 05:05 example01.dbf
-rw-r-----. 1 oracle oinstall 2956992512 Jan  7 05:05 jerry.db
-rw-r-----. 1 oracle oinstall   10493952 Jan  7 05:05 bftbs01.dbf
-rw-r-----. 1 oracle oinstall  104865792 Jan  7 05:05 rman01.dbf
-rw-r-----. 1 oracle oinstall   52436992 Jan  7 05:05 eygle01.dbf
-rw-r-----. 1 oracle oinstall  104865792 Jan  7 05:05 fbra01.dbf
-rw-r-----. 1 oracle oinstall    5251072 Jan  7 08:02 demo01.dbf
-rw-r-----. 1 oracle oinstall  536879104 Jan  7 08:07 temp01new.dbf
-rw-r-----. 1 oracle oinstall 2422218752 Jan  7 08:28 system01.dbf
-rw-r-----. 1 oracle oinstall  104865792 Jan  7 08:32 undotbs02.dbf
-rw-r-----. 1 oracle oinstall  660611072 Jan  7 08:32 sysaux01.dbf
-rw-r-----. 1 oracle oinstall   52429312 Jan  7 08:32 redo03.log
-rw-r-----. 1 oracle oinstall   10436608 Jan  7 08:32 control01.ctl
-rw-r-----. 1 oracle oinstall   10436608 Jan  7 08:32 control02.ctl
-rw-r-----. 1 oracle oinstall   10436608 Jan  7 08:32 control03.ctl


5.3使用alter database rename file 更新控制文件
SQL> alter database rename file '/u01/app/oracle/oradata/orcl/temp01.dbf' to '/u01/app/oracle/oradata/orcl/temp01new.dbf';
 
Database altered
 
SQL>select t.file#,t.BYTES,t.BLOCKS,t.CREATE_BYTES,t.BLOCK_SIZE,t.NAME,t.status from v$tempfile t;
 
select t.file#,t.BYTES,t.BLOCKS,t.CREATE_BYTES,t.BLOCK_SIZE,t.NAME,t.status from v$tempfile t
 
ORA-00376: file 201 cannot be read at this time
ORA-01110: data file 201: '/u01/app/oracle/oradata/orcl/temp01new.dbf'
 
SQL> alter database tempfile  '/u01/app/oracle/oradata/orcl/temp01new.dbf' online;
 
Database altered
 
SQL> select t.file#,t.BYTES,t.BLOCKS,t.CREATE_BYTES,t.BLOCK_SIZE,t.NAME,t.status from v$tempfile t;
 
     FILE#      BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE NAME                                                                             STATUS
---------- ---------- ---------- ------------ ---------- -------------------------------------------------------------------------------- -------
         1  536870912      65536    536870912       8192 /u01/app/oracle/oradata/orcl/temp01new.dbf                                       ONLINE


二、临时表空间已满的处理方法
2.1添加数据文件
SQL> alter tablespace temp01 add tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' size 30m reuse;
 
Tablespace altered
 
SQL> select t.file#,t.BYTES,t.BLOCKS,t.CREATE_BYTES,t.BLOCK_SIZE,t.NAME,t.status from v$tempfile t;
 
     FILE#      BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE NAME                                                                             STATUS
---------- ---------- ---------- ------------ ---------- -------------------------------------------------------------------------------- -------
         1  536870912      65536    536870912       8192 /u01/app/oracle/oradata/orcl/temp01new.dbf                                       ONLINE
         2   31457280       3840     31457280       8192 /u01/app/oracle/oradata/orcl/temp02.dbf                                          ONLINE
 
一般情况下，临时表空间和UNDO表空间不设置为自增。


2.2修改当前数据文件的大小
QL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' resize 50m ;
 
Database altered
 
SQL> select t.file#,t.BYTES,t.BLOCKS,t.CREATE_BYTES,t.BLOCK_SIZE,t.NAME,t.status from v$tempfile t;
 
     FILE#      BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE NAME                                                                             STATUS
---------- ---------- ---------- ------------ ---------- -------------------------------------------------------------------------------- -------
         1  536870912      65536    536870912       8192 /u01/app/oracle/oradata/orcl/temp01new.dbf                                       ONLINE
         2   52428800       6400     31457280       8192 /u01/app/oracle/oradata/orcl/temp02.dbf                                          ONLINE


三、临时表空间过大的处理
如果我们将默认TEMP表空间指向别的名称，则用户相关的信息会失效，因此一般情况采用如下：
3.1创建中转表空间
SQL> select name from v$tempfile;
 
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/temp01new.dbf
/u01/app/oracle/oradata/orcl/temp02.dbf


SQL> select t.username,t.temporary_tablespace from dba_users t;
 
USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
RMAN                           TEMP01
EYGLE                          TEMP01
TRANS                          TEMP01
JERRY                          TEMP01
SCOTT                          TEMP01
USERTEST                       TEMP01
OPS$EYGLE                      TEMP01
HR                             TEMP01
AUDITOR                        TEMP01
ORACLE_OCM                     TEMP01
XS$NULL                        TEMP01
BI                             TEMP01
PM                             TEMP01
..................................
临时表空间必须是uniform，UNDO表空间必须是autoallocate .
SQL> create temporary tablespace temp03 tempfile '/u01/app/oracle/oradata/orcl/temp03.dbf' size 10m autoextend off extent management local uniform size 1m;
 
Tablespace created


3.2 修改temp03为临时表空间
SQL> alter database default temporary tablespace temp03;
 
Database altered


3.3删除原来的临时表空间
SQL> drop tablespace temp01 including contents and datafiles;


Tablespace dropped.


3.4重新创建临时表空间
SQL> create temporary tablespace temp01 tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' size 500m autoextend off extent management local uniform size 2m;


Tablespace created.


3.5重新设置新建表TEMP01为默认表空间
SQL> alter database default temporary tablespace temp01;


Database altered.


3.6删除中转临时表空间
SQL> drop tablespace temp03 including contents and datafiles;


Tablespace dropped.


四、缩小临时表空间
临时表空间释放，只是标记为空闲，其真正占用的物理空间不会减小，可以用shrink来缩小临时表空间，shrink是一个online操作，不影响其他查询。
4.1收缩表空间
SQL> alter tablespace temp01 shrink space keep 200m;


Tablespace altered.
SQL> select t.file#,t.BYTES,t.BLOCKS,t.CREATE_BYTES,t.BLOCK_SIZE,t.NAME,t.status from v$tempfile t;


 FILE#      BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE NAME                                                                             STATUS
---------- ---------- ---------- ------------ ---------- -------------------------------------------------------------------------------- -------
1  211812352      25856    524288000       8192 /u01/app/oracle/oradata/orcl/temp01.dbf                                          ONLINE


4.2缩小数据文件（先恢复到原空间大小 ）
SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' resize 500m;
 
Database altered
 
SQL> select t.file#,t.BYTES,t.BLOCKS,t.CREATE_BYTES,t.BLOCK_SIZE,t.NAME,t.status from v$tempfile t;
 
     FILE#      BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE NAME                                                                             STATUS
---------- ---------- ---------- ------------ ---------- -------------------------------------------------------------------------------- -------
         1  524288000      64000    524288000       8192 /u01/app/oracle/oradata/orcl/temp01.dbf                                          ONLINE
SQL> alter tablespace temp01 shrink tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' keep 200m;
 
Tablespace altered
 
SQL> select t.file#,t.BYTES,t.BLOCKS,t.CREATE_BYTES,t.BLOCK_SIZE,t.NAME,t.status from v$tempfile t;
 
     FILE#      BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE NAME                                                                             STATUS
---------- ---------- ---------- ------------ ---------- -------------------------------------------------------------------------------- -------
         1  210755584      25727    524288000       8192 /u01/app/oracle/oradata/orcl/temp01.dbf                                          ONLINE