Introduction
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.
Scenario
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
/oracle/SID/sapdata1/temp1_1/temp1.data1
We want to rename the temporary tablespace to PSAPTEMP.
Solution
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"
SQL> CREATE TEMPORARY TABLESPACE "PSAPTEMP" TEMPFILE '/oracle/SID/sapdata1/temp_1/temp.data1' SIZE 20000M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M;
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.
- SAP Managed Tags:
