Pages

Thursday, July 30, 2009

DROP temporary tablespace takes long time

The DROP temporary tablespace command takes long time and in fact it hangs because of the entries left in v$sort_usage.

Create a new temporary tablespace.
CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/oradata/TEMP2_01.dbf' SIZE 1000M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 32M;

Enable autoextend for this temporary tablespace.
ALTER DATABASE TEMPFILE '/oradata/TEMP2_01.dbf' AUTOEXTEND ON NEXT 100M;

Assign this tablespace for all users as default temporary tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;

Reset the database connections on App servers OR kill all the inactive sessions corresponding to the entries in v$sort_usage. You can find out those sessions by

SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;

Drop the previous temporary tablespace.
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;