How to recreate PSAPTEMP Tablespace on Oracle

You may find it useful. I did need it for once in my 15 years of Basis Life.

1. Create a new TEMP Tablespace

SQL> CREATE TEMPORARY TABLESPACE temp2
   2  TEMPFILE ‘/u02/oradata/TESTDB/temp2_01.dbf’ SIZE 5M REUSE
   3  AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
   4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

2. Set default TEMP tablespace to one created in STEP1
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Database altered.

3. Delete old PSAPTEMP tablespace.
SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

4. Recreate the PSAPTEMP tablespace with the size and location you desire.
SQL> CREATE TEMPORARY TABLESPACE temp
   2  TEMPFILE ‘/u02/oradata/TESTDB/temp01.dbf’ SIZE 500M REUSE
   3  AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
   4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

5. Change default temporary tablespace back to PSAPTEMP
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.

6. Drop temporarily created tablespace.
SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.