一、 临时表空间理论

在9i之前，如果一个数据库用户没有被指定默认临时表空间，那么oracle就会使用system表空间作为该用户的临时表空间，这是很危险的。在9i里面，database可以被指定一个默认临时表空间。这样如果数据库用户没有被明确指定临时表空间，oracle 9i就会自动指定database的默认临时表空间作为该用户的临时表空间。

默认临时表空间的限制：

1.1. 默认临时表空间必须是TEMPORARY的:

SQL> alter database default temporary tablespace EXAMPLE;

ORA-12901: default temporary tablespace must be of TEMPORARY type

1.2. 默认临时表空间一旦被指定，将无法在改成PERMANET：

SQL> alter tablespace tmp01 permanent;

ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

1.3. 在删除默认临时表空间必须先重新指定默认临时表空间：

SQL> drop tablespace temp including contents and datafiles;

ORA-12906: cannot drop default temporary tablespace

SQL> create temporary tablespace tmp01 tempfile '+DATA' size 10m autoextend  off;

Tablespace created.

SQL> alter database default temporary tablespace TMP01;

Database altered.

SQL> drop tablespace temp including contents and datafiles;

Tablespace dropped.

1.4. 默认临时表空间无法OFFLINE：

SQL> alter tablespace temp offline;

ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

1.5. 用户的临时表空间必须是TEMPORARY的(在9i之前没有这个限制，可以是PERMANENT)：

SQL> alter user TEST temporary tablespace tmp01;

User altered.

1.6. 修改数据库默认临时表空间

SQL> alter database default temporary tablespace tmp_grp;

Database altered.

1.7. 如果删除了用户的临时表空间，而这个临时表空间又不是数据库的默认临时表空间（如果是数据库的默认临时表空间是删不掉的），用户的临时表空间不会自动转换到数据库的默认临时表空间上：

SQL> select tablespace_name, contents from dba_tablespaces where contents like 'TEMPORARY%';

TABLESPACE CONTENTS

---------- ---------

TEMP TEMPORARY

TMP01 TEMPORARY

SQL> select TEMPORARY_TABLESPACE from dba_users where username='TEST';

TEMPORARY_TABLESPACE

------------------------------

TMP01

SQL> drop tablespace TMP01 including contents and datafiles;

Tablespace dropped.

SQL> select TEMPORARY_TABLESPACE from dba_users where username='TEST';

TEMPORARY_TABLESPACE

------------------------------

TMP01

二、 临时表空间实战

2.1 查询临时表空间使用率

SELECT temp_used.tablespace_name,

total - used as "Free_M",

total as "Total_M",

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

WHERE temp_used.tablespace_name = temp_total.tablespace_name;

2.2 临时表空间扩容

--2.2.0查看临时表空间及大小

SQL>

col FILE_NAME for a40;

col TABLESPACE_NAME for a10;

select tablespace_name,file_name,bytes/1024/1204 m from dba_temp_files;

TABLESPACE FILE_NAME M

---------- ---------------------------------------- ----------

TEMP +DATA/devdb/tempfile/temp.264.936769423 24.6644518

--2.2.1查询当前默认临时表空间

col PROPERTY_VALUE for a15;

col DESCRIPTION for a25;

select * from database_properties where property_name like 'DEFAULT_TEMP_%';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION

------------------------------ --------------- -------------------------

DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace

--2.2.2resize临时表空间文件

SQL> alter database tempfile '+DATA/devdb/tempfile/temp.264.936769423' resize 30m;

Database altered.

--2.2.3查看系统文件大小,已经修改成功

+ASM1@rac1 /home/oracle$ export ORACLE_SID=+ASM1

+ASM1@rac1 /home/oracle$ asmcmd

ASMCMD> ls -ls +DATA/devdb/tempfile/temp.264.936769423

Type Redund Striped Time Sys Block_Size Blocks Bytes Space Name

TEMPFILE UNPROT COARSE AUG 04 18:00:00 Y 8192 3841 31465472 32505856 temp.264.936769423

三、 重建临时表空间

3.1 先建

SQL> create temporary tablespace tmp01 tempfile '+DATA' size 2m tablespace group tmp_grp;

Tablespace created.

SQL> create temporary tablespace tmp02 tempfile '+DATA' size 2m;

Tablespace created.

SQL> alter tablespace tmp02 tablespace group tmp_grp;

Tablespace altered.

SQL> alter database default temporary tablespace tmp_grp;

Database altered.

3.2 观察系统运行情况与告警日志信息，无异常就删除旧的临时表空间的数据文件。