Look in:

Web oracle-core-dba.blogspot.com

Monday, November 05, 2007

Recreate Temp tablespace

You created temp tablespace using AUTOEXTEND ON MAXSIZE UNLIMITED to avoid error.
On your production database you noticed that your temp tablespace datafile is huge in size. So you should drop and recreate temp tablespace using the following commands,

CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/u01/app/oracle/oradata/edr/temp02.dbf' SIZE 5M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
------------------------------------------------------------------------------------------
CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u01/app/oracle/oradata/edr/temp01.dbf' SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
------------------------------------------------------------------------------------------
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

2 comments:

anil kumar bommaredy said...

ehi sri,
but why sholud i recycle the TEMP tablespace? doesn't oracle(smon) handle the freeing up used temp tablespace after the transaction completed or aborted?

Cheers,
anil

Sridhar Kasukurthi said...

Anil,

When you give your temp tablespace Parameters as AUTOEXTEND ON MAXSIZE UNLIMITED. The Smon will not free up the space. It will grow as much as it requires but does not reduce after the transaction completed.

So you should manually do this process.

Thanks for reading my blog and your comments.