Temporary tablespace takes ages to drop

A simple command like 'DROP TABLESPACE TEMP;' in a regular database can become excessively slow.  The reason behind this is quite simple, even if the users defaults to a different temporary tablespace there could still be sessions holding temporary segments there.  It's just a matter of finding them and killing them  to free the blocks on the temporary segments and you're good to go.

The operation of dropping the temporary tablespace looks like this on Enterprise Manager:

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

order by tablespace;

Sessions still reported using the target temporary tablespace have to be killed.

After it is unlocked the process runs immediately.

ORA-00600 [2663], [12], ....

After not having spotted ORA-00600 for a while a new one showed up.

ORA-00600: internal error code, arguments: [2663], [12], [3665664941], [12], [3884717804], [], [], [], [], [], [], []


This happened out of the blue after a switchover back from a Datagurad environment.  Basically this is due to a bug, the patch is already included in 11.2.0.2.0 and 11.2.0.3.0 patch sets, but it is not enabled by default, it is activated after setting a parameter in the spfile (pfile) parameter file.


alter system set "_ktb_debug_flags"=8 ;

Fixed.