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

This error appears when you try to issue a DDL against a global temporary table thats created with “on commit preserve rows” clause.:-

create global temporary table TEST (object_id number, object_name varchar2(30)) on commit PRESERVE rows;

Such tables get bound to the session with the first insert and then this binding is ONLY removed when the session exits or by truncate. If the binding exists DDL cannot be performed on these temporary tables and if you try a DDL on such tables you would receive ORA-14452 attempt to create, alter or drop an index on temporary table already in use.

In Session1: We would Insert a record to the global temporary table, as per the theory Session1 should be bind to the temporary table and no other session (regardless of the privilege) would be able to perform DDL on the temp table.

Sesssion1:

SQL> insert into TEST values ('1','V');
1 row created.
SQL> commit;
Commit complete.

Now from Session2 we would try to drop the temporary table.

Session2:

SQL> drop table TEST;
drop table TEST
*
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already in use.

As expected we received ORA-14452, now there are two ways we could fix this:-

Solution1: Truncate the temp table from Session1 and then drop the table from any session:-

Session1:

SQL> truncate table TEST;
Table truncated.

Session2:

SQL> drop table TEST;
Table dropped.

Solution2: Identify the session thats bind with the global temporary table and then kill the session and then drop the table.

Below query can be used to identify the session thats bind with GTT:-

SQL> select  *
from v$lock
where id1 = (
select object_id
from dba_objects
where owner = 'SYS' -- Change the owner according to your env.
and object_name = 'TEST' -- Change the Table name as per your use case.
);

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

00000002A18073F8 00000002A1807450 12 TO 44349871 1 3 0 81 0

Now this session can be killed using ALTER SYSTEM KILL SESSION, you can use following query to create a kill command:-

select 'alter system kill session ' || ''''|| sid || ',' || serial# || ''' ;' "Kill_Command" from v$session where sid in (12);

Once the bind session is killed you can drop the temporary table from any session.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.