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

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

When I tried to drop a Global temporary table during deployment, then I found "ORA-14452: attempt to create, alter or drop an index on temporary table already in use" error.

Why?

For a session specifict GTT, i.e. once created with ON COMMIT PRESERVE ROWS, the session needs to truncate the GTT (Or end the session whereby table will get truncated).

Steps to perform task ( see my sample example):

1) Create Global Temporary table ( GTT)

drop table lab.REQUEST_SAMPLE_GTT;
create global temporary table LAB.REQUEST_SAMPLE_GTT
(
  testid            VARCHAR2(50) not null,
  frequency                NUMBER(2),
  units                    VARCHAR2(50),
  teststatus               NUMBER(3),
  createdby                VARCHAR2(50),
  createddate              DATE,
)
on commit preserve rows;
-- Create/Recreate indexes
create index lab.GT_IDX_TESTID on lab.REQUEST_SAMPLE_GTT (testid);

2) Doing some transaction with various sessions through pl-sql program.

3) Trying to drop and re-create GTT with adding a new column:

SQL> drop table lab.REQUEST_SAMPLE_GTT;

Table dropped.
SQL> create global temporary table lab.REQUEST_SAMPLE_GTT
(
  testid            VARCHAR2(50) not null,
  frequency                NUMBER(2),
  units                    VARCHAR2(50),
  teststatus               NUMBER(3),
  createdby                VARCHAR2(50),
  createddate              DATE,
  updateddate   DATE,
)
on commit preserve rows;

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

To troubleshoot the issue :

Step-1: Find the session id (sid) from v$log using below query:

select  *   from  gv$lock   where id1 = (select  object_id from  dba_objects
where owner = 'LAB' and object_name = 'REQUEST_SAMPLE_GTT');

Assume sid value came as 1034.

Step-2: Find the serial# and other information to the perticular schema and user.

SQL> select * from gv$session where sid=1034;

Step-3: Now cross-check session details with respect to schema and then kill the session.


SQL> alter system kill session '1034,12747' immediate;

session altered.

Step-4: Now try to drop and re-create the GTT. Sure it will work.

SQL> drop table lab.REQUEST_SAMPLE_GTT;

Table dropped.

SQL> create global temporary table lab.REQUEST_SAMPLE_GTT
(
  testid            VARCHAR2(50) not null,
  frequency                NUMBER(2),
  units                    VARCHAR2(50),
  teststatus               NUMBER(3),
  createdby                VARCHAR2(50),
  createddate              DATE,
  updateddate   DATE,
)
on commit preserve rows;

Table created.
SQL> create index lab.GT_IDX_TESTID on lab.REQUEST_SAMPLE_GTT (testid);

Index created.

Hope it will work for you.