Solution to ORA-14452 errors

A session-level temporary table was created recently and the following error was reported when deleting: ORA-14452: an attempt to create, change, or delete an index in a temporary table in use, first from

A session-level temporary table was created recently and the following error was reported when deleting: ORA-14452: an attempt to create, change, or delete an index in a temporary table in use, first from

A session-level temporary table was created recently and the following error was reported when deleting: ORA-14452: an attempt to create, change, or delete an index in a temporary table in use

The solution is as follows:

1. First query the object_id of the table from user_objects or dba_objects:

Log on to sysdba here:
C: \ Documents ents and Settings \ Administrator> sqlplus sys/Oracle @ orcl as sysdba

SQL> select object_id from dba_objects where object_name = UPPER ('rp _ retail001 ');

OBJECT_ID
----------
74317

2. Know the session of the table based on the retrieved object_id:

SQL> set linesize 1000
SQL> select * from v $ lock where id1 = 74317;

Addr kaddr sid ty ID1 ID2 LMODE REQUEST CTIME BLOCK
--------------------------------------------------------------------------------------------------------
000007FFC70967C0 000007FFC70967E0 166 TO 74317 1 3 0 424 0

3. Check the SID and SERIAL of the session in the v $ session view #:

SQL & gt; select serial # from v $ session where sid = 166;

SERIAL #
----------
51595

4. Kill these processes:

SQL> alter system kill session '1995 ';

The system has been changed.

5. Log On As a normal user and delete the temporary table:

SQL> conn dylan/abc123 @ orcl
Connected.
SQL> drop table rp_retail001;

The table has been deleted.

-------------------------------------------
Made by dylan.

,