Oracle9i重建与切换临时表空间

Oracle9i 引入了全局缺省临时表空间,缺省的如果不指定用户临时表空间,Oracle会为用户指定这个缺省临时表空间.
首先查询用户的缺省临时表空间: 

[ oracle@jumper oracle ] $ sqlplus " /   as  sysdba"
SQL * Plus: Release  9.2 . 0.4 . 0   -  Production  on  Wed Apr  12   11 : 11 : 43   2006
Copyright (c)  1982 ,  2002 , Oracle Corporation.   All  rights reserved.
Connected  to :
Oracle9i Enterprise Edition Release  9.2 . 0.4 . 0   -  Production
With  the Partitioning  option
JServer Release  9.2 . 0.4 . 0  – Production
SQL >   select  username,temporary_tablespace  from  dba_users;
USERNAME                       TEMPORARY_TABLESPACE
-- ---------------------------- ------------------------------
SYS                                 TEMP2
SYSTEM                         TEMP2
OUTLN                           TEMP2
EYGLE                            TEMP2
CSMIG                            TEMP2
TEST                               TEMP2
REPADMIN                    TEMP2
......
13  rows selected.
SQL >   select  name  from  v$tempfile;
NAME
-- -------------------------------------------------------------------
/ opt / oracle / oradata / conner / temp02.dbf
/ opt / oracle / oradata / conner / temp03.dbf

重建新的临时表空间并进行切换:

SQL >   create   temporary  tablespace  temp  tempfile  ' /opt/oracle/oradata/conner/temp1.dbf '  size 10M;
Tablespace created.
SQL >   alter  tablespace  temp   add  tempfile  ' /opt/oracle/oradata/conner/temp2.dbf '  size 20M;
Tablespace altered.
SQL >   alter   database   default   temporary  tablespace  temp ;
Database  altered.
SQL >   select  username,temporary_tablespace  from  dba_users;
USERNAME                       TEMPORARY_TABLESPACE
-- ---------------------------- ------------------------------
SYS                                  TEMP
SYSTEM                          TEMP
OUTLN                            TEMP
EYGLE                             TEMP
CSMIG                            TEMP
TEST                                TEMP
REPADMIN                     TEMP
.......
13  rows selected.

如果原临时表空间无用户使用,我们可以删除该表空间:

SQL >   drop  tablespace temp2;
Tablespace dropped.
SQL >  
SQL >   select  name  from  v$tempfile;
NAME
-- -------------------------------------------------------------
/ opt / oracle / oradata / conner / temp1.dbf
/ opt / oracle / oradata / conner / temp2.dbf
SQL >   select   file_name ,tablespace_name,bytes / 1024 / 1024  MB,autoextensible
   2    from  dba_temp_files
   3    /
FILE_NAME                               TABLESPACE_NAME              MB AUTOEXTENSIBLE
-- ------------------------------------ -------------------- ---------- --------------
/ opt / oracle / oradata / conner / temp2.dbf    TEMP                           20  NO
/ opt / oracle / oradata / conner / temp1.dbf    TEMP                           10  NO