ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Online Drop & Recreate TEMPORARY Tablespace

Online Drop & Recreate TEMPORARY Tablespace

原创 Linux操作系统 作者:annnnna 时间:2009-09-11 11:40:44 0 删除 编辑

Check current temporary tablespace status:

 

col FILE_NAME format a30;
set lines 200 pages 200

select a.TABLESPACE_NAME, a.FILE_NAME, a.FILE_ID, (a.BYTES)/1024/1024 AS "total size(MB)",
(b.BYTES_USED)/1024/1024 AS "used size(MB)", (b.BYTES_FREE)/1024/1024 AS "free size(MB)", a.AUTOEXTENSIBLE
from dba_temp_files a, v$temp_space_header b
where a.FILE_ID=b.FILE_ID
order by a.TABLESPACE_NAME asc
/

 

Check current default temporary tablespace:

 

select property_name, property_value
from database_properties
where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

 

Recreate temporary Tablespace Process:

 

Step 1: Create a new temporary tablespace "TEMP2"

 

SQL> CREATE TEMPORARY TABLESPACE temp2
  2  TEMPFILE '/u02/oradata/TESTDB/temp2_01.dbf' SIZE 1000M REUSE
  3  AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
  4  EXTENT MANAGEMENT LOCAL ;

 

Step2: Change default temporary tablespace to "TEMP2"

 

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

 

Step3: Drop old temporary tablespace "TEMP"

 

SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

 

Step4: Recreate old temporary tablespace "TEMP"

 

SQL> CREATE TEMPORARY TABLESPACE temp
  2  TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 1000M REUSE
  3  AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
  4  EXTENT MANAGEMENT LOCAL ;

 

Step5: Change back default temporary tablespace to "TEMP"

 

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

 

Step6: Drop unused new temporary tablespace "TEMP2"

 

SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

 


  

:


SQL> ALTER DATABASE TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' DROP INCLUDING DATAFILES;

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22473175/viewspace-614433/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-09-04

  • 博文量
    23
  • 访问量
    12454