Steps for recreating Oracle temp Tablespace

 == Step 1 Create Alternate Temporary Tablespace Temp2

CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE
  ‘/u02/oradata/altdbdev/temp2a.dbf′ SIZE 10240M AUTOEXTEND OFF,
  ‘/u02/oradata/altdbdev/temp2b.dbf′ SIZE 10240M AUTOEXTEND OFF,
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

== Step 2 Move Default Database temp tablespace to temp2

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

== Step 3 Drop old temp tablespace

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

== Step 4 Recreate Tablespace Temp

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE
  ‘/u02/oradata/altdbdev/temp01.dbf′ SIZE 10240M AUTOEXTEND OFF,
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

== Step 5 Move Tablespace Temp, back to new temp tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

== Step 6 Drop temporary for tablespace temp
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Advertisements

About this entry