Oracle9i的临时表空间删除注意事项

在9i之前,如果一个数据库用户没有被指定默认临时表空间,那么oracle就会使用system表空间作为该用户的临时表空间,这是很危险的。在9i里面,database可以被指定一个默认临时表空间。这样如果数据库用户没有被明确指定临时表空间,oracle 9i就会自动指定database的默认临时表空间作为该用户的临时表空间。
 我们可以通过下面的语句来查询数据库的默认临时表空间:
  SQL> select * from database_properties where property_name = ‘DEFAULT_TEMP_TABLESPACE’;
  PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
  ——————— ——————- ———————— 
  DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace 
   默认临时表空间的限制: 
  1. 默认临时表空间必须是TEMPORARY的:  
   SQL> alter database default temporary tablespace tools;  
   alter database default temporary tablespace tools  
   *  
   ERROR at line 1:  
   ORA-12902: default temporary tablespace must be SYSTEM or of TEMPORARY type
   2. 默认临时表空间一旦被指定,将无法在改成PERMANET:
   SQL> alter tablespace temp2 permanent;
   alter tablespace temp2 permanent
   *
   ERROR at line 1:
   ORA-12904: default temporary tablespace cannot be altered to PERMANENT type
  3. 在删除默认临时表空间必须先重新指定默认临时表空间:
   SQL> drop tablespace temp including contents and datafiles;
   drop tablespace temp including contents and datafiles
   *
   ERROR at line 1:
   ORA-12906: cannot drop default temporary tablespace
   SQL> create tablespace TEMP2
   2 datafile ‘/data1/ora9data/temp2_01.dbf’
   3 size 100k TEMPORARY;
   Tablespace created.
   SQL> alter database default temporary tablespace TEMP2;
   Database altered.
   SQL> drop tablespace temp including contents and datafiles;
   Tablespace dropped.
   4. 默认临时表空间无法OFFLINE:
   SQL> alter tablespace temp offline;
   alter tablespace temp offline
   *
   ERROR at line 1:
   ORA-12905: default temporary tablespace cannot be brought OFFLINE
   5. 用户的临时表空间必须是TEMPORARY的(在9i之前没有这个限制,可以是PERMANENT):
   SQL> alter user scott temporary tablespace TOOLS;
   alter user scott temporary tablespace TOOLS
   *
   ERROR at line 1:
   ORA-12911: permanent tablespace cannot be temporary tablespace
   SQL> create tablespace temp2
   2 datafile ‘/data1/ora9data/temp2_01.dbf’
   3 size 100k temporary;
   Tablespace created.
   SQL> alter user scott temporary tablespace temp2;
   User altered.
   6. 如果删除了用户的临时表空间,而这个临时表空间又不是数据库的默认临时表空间(如果是数据库的默认临时表空间是删不掉的),用户的临时表空间不会自动转换到数据库的默认临时表空间上:
   SQL> select tablespace_name, contents from dba_tablespaces where tablespace_name like ‘TEMP%’;
   TABLESPACE_NAME CONTENTS
   —————————— ———
   TEMP TEMPORARY
   TEMP2 TEMPORARY
   SQL> select TEMPORARY_TABLESPACE from dba_users where username=’SCOTT’;
   TEMPORARY_TABLESPACE
   ——————————
   TEMP2
   SQL> drop tablespace TEMP2 including contents and datafiles;
   Tablespace dropped.
   SQL> select TEMPORARY_TABLESPACE from dba_users where username=’SCOTT’;
   TEMPORARY_TABLESPACE
   ——————————
   TEMP2



无觅相关文章插件,快速提升流量

“Oracle9i的临时表空间删除注意事项”有1条评论

mtime | 2012-11-10 14:49 |

元芳,你怎么看?