Create, Add & Drop PSAPTEMP Tablespace

Symptom

You want to create, add & drop PSAPTEMP tablespace in SAP on Oracle database environment. It is advisable to shut down your SAP application (Central & dialog instance) during the operation, just leave the Oracle database running.

Creating PSAPTEMP Tablespace

1. Execute below command to create a PSAPTEMP tablespace. Ensure that the directory is existed.

SQL> CREATE TEMPORARY TABLESPACE "PSAPTEMP" TEMPFILE
'/oracle/SID/sapdataX/temp_X/temp.dataX' SIZE xxM REUSE AUTOEXTEND OFF;

Adding PSAPTEMP Tablespace

1. Execute below command to add a PSAPTEMP tablespace.

SQL> ALTER TABLESPACE PSAPTEMP ADD TEMPFILE 
'/oracle/SID/sapdataX/temp_X/temp.dataX' SIZE xxM REUSE AUTOEXTEND OFF;

Dropping PSAPTEMP Tablespace

1. Create a dummy temporary tablespace, which is in this example, the dummy is PSAPTEMP123.

CREATE TEMPORARY TABLESPACE "PSAPTEMP123" TEMPFILE 
'/oracle/SID/sapdataX/temp_X/temp.datax' SIZE xxM REUSE AUTOEXTEND OFF;

2. Switch the created dummy temporary tablespace as the default.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE PSAPTEMP123;

3. Now, you can drop the PSAPTEMP tablespace.

SQL> DROP TABLESPACE PSAPTEMP INCLUDING CONTENTS;

Note

Execute below command to display the created or added PSAPTEMP tablspace.

SQL> select file_name from dba_temp_files;

You May Also Like

Leave a Reply?