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:
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