Monday, September 17, 2012

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

2 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