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  – ProductionSQL >   select  username,temporary_tablespace  from  dba_users;USERNAME                       TEMPORARY_TABLESPACE -- ---------------------------- ------------------------------ SYS                                 TEMP2SYSTEM                         TEMP2OUTLN                           TEMP2EYGLE                            TEMP2CSMIG                            TEMP2TEST                               TEMP2REPADMIN                    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.dbfSQL >   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