ORA-014452 – Removing temporary table locks

I was faced with an interesting problem this morning that required a custom solution. During a process to refresh a non-production database with production data, I encountered this error:

SQL> drop user old_user cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-14452: attempt to create, alter or drop an index on temporary table already
in use

It seems that there was a lock on a temporary table owned by the user I was trying to drop. I combined a couple of queries I had to generate ‘kill session’ statements based on the locking sessions:

select ‘alter system kill session ‘||””||sid||’,’||serial#||’,@’||inst_id||””||’ immediate;’
from gv$session
where sid in(
select /*+ RULE */ sid
from gv$access
where owner = ‘OLD_USER’
and object in(select table_name
from dba_tables
where owner = ‘OLD_USER’
and temporary = ‘Y’)
)

Notice that the statements generated can be used to terminate sessions on clustered instances that I may not be connected to. If this was a non-clustered system, then the query would look like this:

select ‘alter system kill session ‘||””||sid||’,’||serial#||””||’ immediate;’
from v$session
where sid in(
select /*+ RULE */ sid
from gv$access
where owner = ‘OLD_USER’
and object in(select table_name
from dba_tables
where owner = ‘OLD_USER’
and temporary = ‘Y’)
)

Once the session or sessions holding the locks on the temporary objects are killed, I was able to drop the user.  As a safety measure, you should ALWAYS export the user’s objects prior to the drop.

Note that I used the RULE hint in my sub-query.  I tend to use this hint for the reason that queries against the v$access view will hang if the system statistics are not up to date. I cover this in a paper I wrote that is available upon request.