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

Sometimes, we face ORA-14452 during drop or alter. SO, in this article, we are going to learn how to solve “ORA-14452: attempt to create” this error.

We face this issue if the table is currently used in any other session, and we trying to drop or alter the table.

Find Session Details

Let’s find in which session the table is in used, using the below query:

SQL> set lin 300 pagesize 200
SQL> select  * from  v$lock
        where id1 = ( select  object_id from  dba_objects
        where owner = 'SYS'
        and object_name = 'TEMP_TABLE');

Output from the above query:

ORA-14452

Here we found the session-id 56 which is using the table.

ORA-14452 SOLUTION

To solve our problem we need to kill the associated session using the below query.

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

Kill_Command
---------------------------------------------------
alter system kill session '56,21918' ;

The above query will return the session kill query as per SID. So, just execute the query and then try to drop or alter the table.

SQL> alter system kill session '56,21918' ;

System altered.

Now, try to drop the table.

SQL> drop table TEMP_TABLE purge;

Table dropped.

This time we were able to drop the table.

I hope you found this article helpful, if yes please write in the comment box and follow us on social media.

Read More: YouTube

Leave a Comment

%d bloggers like this:
Enable Notifications    OK No thanks