Temporary Tablespaces

If you have reason to do some work on your temporary tablespaces, the following code may help.


-- Create the new temp tablespace.
CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/u01/oradata/temp02.dbf' SIZE 10M
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

-- Make this the default temp tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

-- Drop the original temp tablespace.
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

-- Create the original tablespace again.
CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u01/oradata/temp01.dbf' SIZE 10M
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

-- Make this the default temp tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

-- Drop the unwanted second temp tablespace.
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

-- If you have a temporary tablespace without a datafile as
-- sometimes happens with an RMAN restore, this will add
-- a datafile to the tablespace.
ALTER TABLESPACE TEMP
ADD TEMPFILE '/u01/oradata/temp01.dbf' SIZE 10000M
AUTOEXTEND ON NEXT 8K MAXSIZE UNLIMITED;



Keywords: oracle temp tablespace database