SAP Basis Community :: SAP Basis, Netweaver, ABAP, Portal, BW, Oracle, System Copy

Following procedure helps you to re-create Temporary Tablespace PSAPTEMP while system is running.

SQL> CREATE TEMPORARY TABLESPACE temp2
  2  TEMPFILE '/oracle/<SID>/sapdata<n>/temp2_1/temp2.data1' SIZE 5M REUSE
  3  AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
  4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.


SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Database altered.


SQL> DROP TABLESPACE PSAPTEMP INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> CREATE TEMPORARY TABLESPACE PSAPTEMP
  2  TEMPFILE '/oracle/<SID>/sapdata<n>/temp_1/temp.data1' SIZE 500M REUSE
  3  AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
  4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.


SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE PSAPTEMP;

Database altered.


SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.