1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

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

Discussion in 'SQL PL/SQL' started by jagadekara, Aug 10, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    ORA-14452: attempt to create, alter or drop an index on temporary table already in use

    Solution
    ---------

    Hi All,

    Recently I came across the above error.

    I created one Global Temporary Table and insert the data in to that table like below.

    Code (SQL):
    CREATE global TEMPORARY TABLE params
    (param_name             varchar2(30 CHAR),
     param_num_value        NUMBER
    )ON commit preserve ROWS;


    INSERT INTO params
    SELECT 'my_param' , level FROM dual CONNECT BY level < 1002;
    commit;

    After that I don't need that table. So I decided to drop that table.

    Code (SQL):
    DROP TABLE params;
    Then the following error came.

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


    Then I browse internet lot of time and finally came to know that

    For a session specifict GTT, i.e. one created with ON COMMIT PRESERVE ROWS, the session needs to truncate the GTT.


    Code (SQL):
    TRUNCATE TABLE params;
    then drop the table.

    Code (SQL):
    DROP TABLE params;
    Now table dropped successfully.

    Generally if we have created any index on that table and if we want to drop that index that time also same error will come.
    So first truncate table then drop.

    Note: Take care while truncating and dropping. I am not responsible for data lost.