If there is a session that uses the temp tablespace you are trying to drop, the drop operation starts waiting. The drop operation is completed when all sessions using temp are finished.
If you want the drop immediately, after changing the default temp tablespace of the database, either restart the database, or find and terminate the sessions that use the temp tablespace you are trying to drop.
Who is Using Temp Tablespace
You can use the following query to find sessions that use temp.
|
1 |
SQL> select tu.username,s.sid,s.serial# from v$tempseg_usage tu, v$session s where tu.session_addr=s.saddr; |
You can kill the related sessions as follows by using the session information identified by the above query.
|
1 |
SQL> alter system kill session 'sid,serial#'; |
