ORA-14452

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

Cause : 
 An attempt was made to create, alter or drop an index on temporary table which is already in use.

Solution :
All the sessions using the session-specific temporary table have to truncate table and all the transactions using transaction specific temporary table have to end their transactions.

Example :

SQL>CREATE GLOBAL TEMPORARY TABLE Osama ON COMMIT PRESERVE ROWS
AS SELECT * FROM employee ;


SQL> DELETE FROM Osama;
258 rows deleted.

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

Fixing :

SQL> TRUNCATE TABLE Osama;
Table truncated.

SQL> DROP TABLE Osama;
Table dropped.


Done & Enjoy 

Osama Mustafa

Comments

  1. great!

    But I don't understand why is the table in use after create command, or after commit command

    SQL> CREATE GLOBAL TEMPORARY TABLE Osama ON COMMIT PRESERVE ROWS
    2 AS SELECT * FROM dual ;

    Table created
    SQL> DROP TABLE Osama;

    DROP TABLE Osama

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

    Table truncated
    SQL> drop table Osama;

    Table dropped
    SQL> CREATE GLOBAL TEMPORARY TABLE Osama ON COMMIT PRESERVE ROWS
    2 AS SELECT * FROM dual ;

    Table created
    SQL> DELETE FROM Osama;

    1 row deleted
    SQL> commit;

    Commit complete
    SQL> DROP TABLE Osama;

    DROP TABLE Osama

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

    Table truncated
    SQL> drop table Osama;

    Table dropped

    SQL>


    ReplyDelete

Post a Comment

Popular posts from this blog

Connection refused; No available router to destination On Weblogic Node Manager Log

How to change SGA in Oracle Rac

SGA_MAX_SIZE & SGA_TARGET / MEMORY_TARGET & MEMORY_MAX_TARGET