Oracle重建临时表空间

[oracle@hd58 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 27 11:58:25 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@JOY:~>col PROPERTY_VALUE format a15
SYS@JOY:~>col DESCRIPTION format a37
SYS@JOY:~>select PROPERTY_VALUE,DESCRIPTION from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE  DESCRIPTION
--------------- -------------------------------------
TEMP            Name of default temporary tablespace

SYS@JOY:~>select username,temporary_tablespace from dba_users where rownum < 10;

USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW                      TEMP
SYS                            TEMP
SYSTEM                         TEMP
DBSNMP                         TEMP
SYSMAN                         TEMP
SCOTT                          TEMP
HR                             TEMP
OUTLN                          TEMP
FLOWS_FILES                    TEMP

9 rows selected.

SYS@JOY:~>select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/opt/app/oracle/oradata/JOY/temp01.dbf

SYS@JOY:~>

当前默认temp tablespace是temp,对应数据文件是/opt/app/oracle/oradata/JOY/temp01.dbf 
创建新临时表空间并切换

SYS@JOY:~>create temporary tablespace temp02 tempfile '/opt/app/oracle/oradata/JOY/temp02.dbf' size 10m autoextend on next 2m maxsize unlimited;

Tablespace created.

SYS@JOY:~>alter tablespace temp02 add tempfile '/opt/app/oracle/oradata/JOY/temp02_01.dbf' size 10m autoextend on next 2m maxsize unlimited;

Tablespace altered.

SYS@JOY:~>alter database default temporary tablespace temp02;

Database altered.

SYS@JOY:~>select PROPERTY_VALUE,DESCRIPTION from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE  DESCRIPTION
--------------- -------------------------------------
TEMP02          Name of default temporary tablespace

SYS@JOY:~>drop tablespace temp including contents and datafiles;

Tablespace dropped.

SYS@JOY:~>select username,temporary_tablespace from dba_users where rownum < 10;

USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW                      TEMP02
SYS                            TEMP02
SYSTEM                         TEMP02
DBSNMP                         TEMP02
SYSMAN                         TEMP02
SCOTT                          TEMP02
HR                             TEMP02
OUTLN                          TEMP02
FLOWS_FILES                    TEMP02

9 rows selected.

SYS@JOY:~>select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/opt/app/oracle/oradata/JOY/temp02.dbf
/opt/app/oracle/oradata/JOY/temp02_01.dbf

SYS@JOY:~>