Thursday, January 17, 2013

ORACLE - Temporary Tablespace

Check temporary tablespace informations:
select  * from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE';
------------------------  -----
DEFAULT_TEMP_TABLESPACE   TEMP


select tablespace_name, contents, status from dba_tablespaces where tablespace_name like '%TEMP%';
TABLESPACE_NAME                CONTENTS  STATUS
------------------------------ --------- ---------
TEMP                           TEMPORARY ONLINE


Create a new temporary tablespace TEMP_X and make it default temporary tablespace, drop the old TEMP tablespace:
CREATE TEMPORARY TABLESPACE TEMP_X
TEMPFILE '+MWDATA1' SIZE 4000M  AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;


ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_X;

SQL> select FILE_NAME, TABLESPACE_NAME from  dba_temp_files;
FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- ----------------
+JDATA1/JDB/tempfile/temp_x.274.804949525     TEMP_X
/u01/app/oracle/oradata/JDB/temp01.dbf        TEMP


DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

Create new temporary tablespace TEMP,make it deault temporary tablespace and drop the old TEMP_X tablespace:
CREATE TEMPORARY TABLESPACE TEMP
TEMPFILE '+MWDATA1' SIZE 4000M  AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;


ALTER DATABASE DEFAULT TEMPORARY TABLESPACE  TEMP;

DROP TABLESPACE TEMP_X INCLUDING CONTENTS AND DATAFILES;

Resize a Temporary Tablespace:
alter tablespace temp resize 1000M;
alter database tempfile 'tempfile_name' resize 3000M;


Shrink a Temporary Tablespace:
alter tablespace temp shrink space;
alter tablespace temp shrink tempfile 'tempfile_name' keep 2000M;


RENAME a Temporary Tablespace:
alter tablespace temp rename to temp_x;

To view information about Temporary Tablespace:select * from dba_temp_files;
select * from v$tempfile;
select * from v$temp_space_header;


Monitor the tempoary tablspace usage:
select * from v$sort_usage;
select *  from v$sort_segment;


If you try to drop the default temporary tablespace, you will get following error message:
SQL> drop tablespace temp;
drop tablespace temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

No comments:

Post a Comment