This discussion is archived
9 Replies Latest reply: May 2, 2011 1:50 PM by John Spencer RSS

ORA-14452 attempt to create, alter or drop an index on temporary table

536885 Newbie
Currently Being Moderated
Hi,

How can we drop a Global Temporary Table?

I get the below mentioned error messsage when I try to drop a Global Tempary table :
"ORA-14452: attempt to create, alter or drop an index on temporary table already in use"

I have tried closing all my sessions and even removing the reference of the global temporary table from my procedure but still get the same error.

Any help is appreciated.

Thanks.
  • 1. Re: ORA-14452 attempt to create, alter or drop an index on temporary table
    Solomon Yakobson Guru
    Currently Being Moderated
    orauser34 wrote:

    I have tried closing all my sessions
    Well, you have to try harder ;). Anyway, issue:
    select  *
      from  v$lock
      where id1 = (
                   select  object_id
                     from  dba_objects
                     where owner = 'global-temp-table-owner-name-in-upper-case'
                       and object_name = 'global-temp-table-name-in-upper-case'
                  );
    SY.
  • 2. Re: ORA-14452 attempt to create, alter or drop an index on temporary table
    Suri Pro
    Currently Being Moderated
    Hi,

    If you are not completing any transaction in any session and if you try to drop the table then you will get this error.

    I guess if you execute Solomon query you will be able to understand is there any active session which is locking this object.

    Thanks,
    Suri
  • 3. Re: ORA-14452 attempt to create, alter or drop an index on temporary table
    Peter Gjelstrup Guru
    Currently Being Moderated
    Suri wrote:
    If you are not completing any transaction in any session and if you try to drop the table then you will get this error.
    True, but not the cmplete answer. For a session specifict GTT, i.e. one created with ON COMMIT PRESERVE ROWS, the session needs to truncate the GTT (Or end the session whereby table will get truncated).

    Regards
    Peter
  • 4. Re: ORA-14452 attempt to create, alter or drop an index on temporary table
    Solomon Yakobson Guru
    Currently Being Moderated
    Peter Gjelstrup wrote:

    i.e. one created with ON COMMIT PRESERVE ROWS
    Query I posted will show holding sessions regardless of GTT ON COMMIT setting.

    SY.
  • 6. Re: ORA-14452 attempt to create, alter or drop an index on temporary table
    536885 Newbie
    Currently Being Moderated
    Thanks for your help.

    Had a follow up question.
    Does this error indicate that there is a bug in the code?
    Do I need to do anything to the code besides killing the hanging session so that I do not get this error again?
  • 7. Re: ORA-14452 attempt to create, alter or drop an index on temporary table
    Peter Gjelstrup Guru
    Currently Being Moderated
    Does this error indicate that there is a bug in the code?
    Difficult to tell when not knowing what that code is, what you are trying to do and why you are doing it.

    Let's say you have two sessions:

    DROP_SESSION - This is the session trying to drop the Global Temporary Table (GTT)

    LOCK_SESSION - This is the session using that GTT, hence preventing DROP_SESSION from dropping it.

    Note: They could be the same session.


    If you by "code" mean code as in a program executed by DROP_SESSION, you definitely have a problem.

    Application code should NOT drop objects, unless it is very special code (Like an ETL process) and
    this is code is executed in controlled down-time windows where no ordinary users have access.

    And dropping a GTT is meaningless and seems to defy the purpose of a GTT - Define it once, like other tables,
    and use over and over.

    If this is your case, solution is Don't drop it.



    If you by "code" mean code as in a program executed by LOCK_SESSION, you need to tell more about the GTT.

    How is it created, ON COMMIT DELETE, or ON COMMIT PRESERVE?

    In either case, it brings the question why are you dropping it?
    - Is this part of a one time operation, then why are you dropping objects while code is using them?

    - Is it not part of a one time operation, see above Don't drop it .

    The LOCK_SESSION may have legitimate need for using that GTT.
    It could also be a bug, if an application has a long time transaction that is "never" committed.
    It could also be a bug, if an application have data in that GTT during the entire session, and it should not have.

    Impossible to tell.

    Do I need to do anything to the code besides killing the hanging session so that I do not get this error again?
    Again, I don't know what that code is. And you have said nothing about a hanging session, which session would that be?

    You need to run SY's query to find who is using the GTT, find the responsible for that session, and tell him to complete his session.


    Still and again, I'm not sure of what you are doing, but one thing I do know. You should not be dropping GTTs (Or other objects)
    as a usual operation in an Oracle database.

    Regards
    Peter
  • 8. Re: ORA-14452 attempt to create, alter or drop an index on temporary table
    536885 Newbie
    Currently Being Moderated
    Thanks Peter.

    I need to drop the GTT because I need to add a column to it

    It will be a one time operation and my project is still in the Development phase.

    The GTT is created with "ON COMMIT PRESERVE ROW". Does this leading to any locking issue?
  • 9. Re: ORA-14452 attempt to create, alter or drop an index on temporary table
    John Spencer Oracle ACE
    Currently Being Moderated
    orauser34 wrote:
    Thanks Peter.

    I need to drop the GTT because I need to add a column to it
    No you don't:
    SQL> create global temporary table temp1 (
      2     col1 number,
      3     col2 varchar2(10))
      4  on commit preserve rows;
      
    Table created.
      
    SQL> alter table temp1 add (col3 date);
      
    Table altered.
    It will be a one time operation and my project is still in the Development phase.

    The GTT is created with "ON COMMIT PRESERVE ROW". Does this leading to any locking issue?
    No more locking issues than delete rows. If a session is actively using the table, then it will be locked, at least as far as DDL is concerned. If you need to keep the contents of the table for a session across commits, and there are many valid reasons why you might, then you need to have it as preserve rows.

    john

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points