Friday, December 14, 2007

Drop temporary tablespace hang

I issued a drop tablespace temp and it hang. If you are in the same situation check the above:

select username,session_num,session_addr from v$sort_usage
where tablespace='[Name of the temp tablespace you want to drop]';

if this query return rows use the session_num to find the sid from the v$session.

select sid,serial#,status from v$session where serial#=[session_num];

Alternatively use the session_addr.

select sid,serial#,status from v$session where saddr='[session_addr]';

and then kill the session at will.

alter system kill session '[sid],[serial#]' immediate;

If in RAC then check each instance !!!

10 comments:

  1. This is really very very useful.

    we had same problem. We issued the command as per the steps and it simply worked very well.

    Thanks.

    ReplyDelete
  2. Hi, you should check if the sort is running on the tablespace temp. Maybe you have more than one temporary tablespaces. To verify select also tablespace_name from v$sort_usage.

    ReplyDelete
  3. Thanks for your remark.I edited the post.

    ReplyDelete
  4. Nice post (was helpful).

    Just thought of mentioning that you need to correct the kill statement from:

    alter system kill '[sid],[serial#]' immediate;

    to:

    alter system kill session '[sid],[serial#]' immediate;

    You missed mentioning "session".

    Keep up the good work.

    Regards
    Zaffer Khan
    http://oracleendeavor.blogspot.com/

    ReplyDelete
  5. Thanks for your comment Zaffer. I edited the kill statement.

    Regards

    Agis

    ReplyDelete
  6. THis is really useful, it worked for me in production database. :)

    ReplyDelete
  7. Fantastic. I could not find this even in oracle support site, forums or anywhere

    ReplyDelete
  8. Superb it helped us. thanks mate.

    ReplyDelete