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;