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;
1 comment:
Thanks Anand
Your blog helped.
Post a Comment