3

In Oracle when you try to drop a Global Temporary Table that is in use you get the following exception:

ORA-14452: attempt to create, alter or drop an index on temporary table already in use

This is caused by a session that is using the GTT in a current transaction. Is there a way to query the data dictionary to determine which sessions are blocking the drop? I know I can get the sessions with in progress transactions with something like this, but I would like to narrow it down further.

select * from v$session where session_id in (select session_id from dba_locks where lock_type='Transaction');

2 Answers 2

4

The session(s) using the temporary table can be queried as:

select sid from v$lock where type = 'TO' 
and id1 = (select object_id from dba_objects where object_name = '&temp_table_name');

Then you can kill these sessions or have them release the locks gracefully.

1
  • 1
    This is exactly what I was looking for. I generalized your query to look for all locks for GTTs and to do so on RAC. Here is what I am using: SELECT inst_id, sid, username, osuser, machine, terminal, program FROM gv$session WHERE (inst_id, sid) IN (SELECT inst_id, sid FROM gv$lock WHERE type = 'TO'); Jul 30, 2015 at 13:10
2

Here's what happens:

When you create a global temporary table with the clause "on commit preserve rows", table will be locked until you restart the database. I had this issue recently and was able to reproduce it. I created a simple article describing it (in portuguese but the commands are in english):

https://guilhermemesquitadba.wordpress.com/2015/07/15/ora-14450-attempt-to-access-a-transactional-temp-table-already-in-use/

Also, you won't be able to query the session, since it's internal to Oracle and it's not a common session.

4
  • 1
    This is not the behavior we are experiencing. Through trial and error we find the session that is using the temporary table and have the user commit or roll back the session. When they do so the drop succeeds. Jul 29, 2015 at 20:41
  • 1
    Can not be queried? You have provided the method for querying the sessions in your blog post. Restart the database just for this? Killing the session(s) will suffice, but you do not even need to kill the session(s). Performing a truncate table g; in your session will release the locks of your session on an on commit preserve rows temporary table. Jul 29, 2015 at 21:26
  • 1
    @Leigh : if the table is created with the 'on commit preserve rows' option I also experienced the behavior that Guilherme describes, but exiting the session is sufficient to release the lock (or truncation the table in the sesion as Balasz describes). wihout 'on commit preserve rows' a rollback is sufficient to release the lock
    – miracle173
    Jul 29, 2015 at 21:40
  • 1
    I agree, however, in the real world, it doesn't work 100% as by the docs, and it's important to make sure this session that is holding the lock isn't a PSEUDO session. A note from Doc ID 100859.1 (it's specifying the problem with Windows NT, but the concept applies): "Killing the session will not clear the locks. The session on the remote database will remain idle waiting for input until the network read times out [...] - So, if it's enough to truncate the table, ok good, if it's not, then "wait for the session to release the lock by itself or restart the database might be options... Jul 29, 2015 at 22:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.