Saturday 1 September 2012

How to Empty Temp tablespace?

Hi,
Over the period of time if temp tablespace size (OS level) grow but actual size of it is small.
then How to Empty temp tablespace?

--Create another temporary tablespace temp2

SQL> CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/shreyas/temp02.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

--Make temp2 as default temp tbs

alter database default temporary tablespace temp2;

--Drop temp tablespace

 drop tablespace temp including contents and datafiles;

--Create temp tablespace

SQL> CREATE TEMPORARY TABLESPACE temp TEMPFILE '/shreyas/temp01.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

--Make temp as default temp tbs

alter database default temporary tablespace temp;

--Drop temp2 tablespace

drop tablespace temp2 including contents and datafiles;

No comments:

Post a Comment