Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
Showing results for 
Search instead for 
Did you mean: 
0 Kudos


Sometimes, it is necessary to rename the temporary tablespace. Unfortunately the  temporary tablespace is used by the processes of a sap system. This short blog shows you how to change the temporary tablespace online, without disturbing the sap-system.


The system consists of one database server and 2 application servers. The transaction DB02OLD stated a temporary tablespace with a wrong name. PSAPTEMP1 is defined as default temporary tablespace and is stored under


We want to rename the temporary tablespace to PSAPTEMP.


How is the procedure to rename PSAPTEMP to PSAPTEMP?

1. Create the new temporary tablespace.

2. Define the new temporary tablespace as default temporary tablespace.

3. Drop the old temporary tablespace.

1. Create the new temporary tablespace.

orasid> mkdir /oracle/SID/sapdata1/temp_1/

orasid> sqlplus "/as sysdba"


2. Define the new temporary tablespace as default temporary tablespace.

SQL> alter database default temporary tablespace PSAPTEMP;

3. Drop the old temporary tablespace.

SQL> drop tablespace PSAPTEMP1 including contents and datafiles;

Every new operation in ORACLE will now use the new temporary tablespace PSAPTEMP!


In all probability there are handles on the temporary tablespace by the workprocesses of the sap system. This causes the drop statement to hang! In order to be able to identify the processes that have a handle on PSAPTEMP1 enter in sqlplus:

SQL> SET linesize 200

SQL> COLUMN tablespace FORMAT a15;

SQL> COLUMN machine FORMAT a15;

SQL> SELECT se.sid sid, se.process process, se.status status, su.tablespace tablespace,

            su.segtype segtype, su.contents contents, se.machine machine, se.program program

     FROM v$session se, v$sort_usage su

     WHERE se.saddr=su.session_addr;

You get a list with handles. In this list you can identify the server (column MACHINE) and the process (column PROCESS)  of the handle holder. The program column shows the process name. The process name indicates that workprocesses are holding the handles. There are two types of handles: ACTIVE and INACTIVE ones. You have to wait until the ACTIVE handle disappear, because this handle is initiated by workprocess processing a report using PSAPTEMP1. Killing this workprocess could inflict inconsistencies. The INACTIVE handles can be cleared by restarting the corresponding workprocess.

Now you know the server and the pid.Lets logon to the server and check it:

sidadm> ps -efa | grep <process>

Yes it is the correct workprocess. In transaction SM50 of the corresponding application server you can identify the workprocess with the determined pid and restart the workprocess in order to clear the handle.

Repeat this procedure for each INACTIVE handle. After clearing the last handle the drop statement will proceed.

After dropping PSAPTEMP1 you can delete the directory of PSAPTEMP1

orasid> rmdir /oracle/SID/sapdata1/temp1_1/

After new db-checks the new temporary tablespace PSAPTEMP appear in the DB02OLS.

Please excuse the usage of the old transaction DB02OLD bit I don't like the "look and feel" of ALV-grids. I prefer the old list processing of sap.

I hope you enjoyed the blog.

Labels in this area