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

After reading Hemant Sir’s blog “GLOBAL TEMPORARY TABLEs and GATHER_TABLE_STATS ” (http://hemantoracledba.blogspot.com/2011/01/global-temporary-tables-and.html) i tried the same on my system and again learned something new 🙂

Time to share it :).

Session 1 –> Anand’s session
Session 2 –> SYS as sysdba

Session 2
==========

16:05:05 SYS@MATRIX> @session

Username        OSUSER                 SID    SERIAL# SPID                     STATUS   MODULE       Term   MACHINE                           PROGRAM              LAST_ACTIVE_CALL_IN_MINS LOGON_TIME
--------------- --------------- ---------- ---------- ------------------------ -------- --------------- ------ ------------------------------ -------------------- ------------------------ --------------------
ANAND           ananprak               198         61 5796                     INACTIVE SQL*Plus     ananpr APPLICATIONS\ananprak-lap      sqlplus.exe                   .066666667 16-JAN-2011 16:05:02
                                                                                                        ak-lap

SYS             ananprak               134        253 1588                     ACTIVE   sqlplus.exe  ananpr APPLICATIONS\ananprak-lap      sqlplus.exe                            0 16-JAN-2011 15:15:16
                                                                                                        ak-lap


Elapsed: 00:00:00.03
16:05:06 SYS@MATRIX> @lock_details

Username          SID Term   Table Name                     COMMAND                   Lock Held         Lock Requested  ID1 - ID2          Lock Type
---------- ---------- ------ ------------------------------ ------------------------- -------------------- -------------------- ------------------ ----------------------------------------
ANAND             198 ananpr None                           BACKGROUND                Share             NONE            100-0              AE - ????
                      ak-lap

SYS               134 ananpr SYS.ORA$BASE                   SELECT                    Share             NONE            100-0              AE - ????
                      ak-lap


Elapsed: 00:00:00.04
16:05:08 SYS@MATRIX>

Session 1
===========

16:06:24 ANAND@MATRIX> create global temporary table MY_GTT_PRESERVE (object_id number, object_name varchar2(30)) on commit PRESERVE rows;

Table created.

Session 2
=========

16:06:59 SYS@MATRIX> @lock_details

Username          SID Term   Table Name                     COMMAND                   Lock Held         Lock Requested  ID1 - ID2          Lock Type
---------- ---------- ------ ------------------------------ ------------------------- -------------------- -------------------- ------------------ -----------
ANAND             198 ananpr SYS.ORA$BASE                   Create Table              Share             NONE            100-0              AE - ????
                      ak-lap

Session 1
==========

16:06:27 ANAND@MATRIX> insert into MY_GTT_PRESERVE select object_id, object_name from dba_objects where object_id is not null;

72747 rows created.

Session 2
==========

16:07:00 SYS@MATRIX> @lock_details

Username          SID Term   Table Name                     COMMAND                   Lock Held         Lock Requested  ID1 - ID2          Lock Type
---------- ---------- ------ ------------------------------ ------------------------- -------------------- -------------------- ------------------ ------------------------------------
ANAND             198 ananpr None                           INSERT                    Row Exclusive     NONE            75829-2            TO - Temporary Table Object Enqueue
                      ak-lap

                             SYS.ORA$BASE                   INSERT                    Share                NONE                 100-0              AE - ????
                             None                           INSERT                    Exclusive            NONE                 196610-1153        TX - Transaction enqueue lock
                             None                           INSERT                    Row Exclusive        NONE                 75829-1            TO - Temporary Table Object Enqueue
                             ANAND.MY_GTT_PRESERVE          INSERT                    Row Exclusive        NONE                 75829-0            TM - DML enqueue lock

Remember i haven’t done commit yet.After commit the lock details are

Session 2
==========

16:08:40 SYS@MATRIX> @lock_details

Username          SID Term   Table Name                     COMMAND                   Lock Held         Lock Requested  ID1 - ID2          Lock Type
---------- ---------- ------ ------------------------------ ------------------------- -------------------- -------------------- ------------------ ------------------------------------
ANAND             198 ananpr None                           BACKGROUND                Share             NONE            100-0              AE - ????
                      ak-lap

                             None                           BACKGROUND                Row Exclusive        NONE                 75829-1            TO - Temporary Table Object Enqueue

What is TO-enqueue??

As per Metalink

The TO enqueue protects concurrent DDL operations on temporary tables. It is acquired in exclusive(X) mode during ALTER, DROP and CREATE operations on a temporary table or its indexes.

Acquired in SX mode when performing DML against a temporary object.Acquired in S mode for DDL index operations.

The purpose of the lock is to prevent DDL from being issued against an object while DML is occurring.
**************************************************************
The documentation on this situation is not very clear:
This is expected behaviour with clause “on commit preserve rows”. DDL cannot be performed on these temporary tables which are in use or bound to a session.
These tables get bound to the session with the first insert. The binding is removed when the session exits or by truncate.

So lets try to drop the table

Session 1
=========

16:10:10 ANAND@MATRIX> drop table MY_GTT_PRESERVE;
drop table MY_GTT_PRESERVE
           *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already in use

Session 2
==========

16:39:31 SYS@MATRIX> @lock_details

Username     SID Term   Table Name                     COMMAND                   Lock Held            Lock Requested       ID1 - ID2          Lock Type
---------- ----- ------ ------------------------------ ------------------------- -------------------- -------------------- ------------------ ----------------------------
ANAND        198 ananpr None                           BACKGROUND                Share                NONE                 100-0              AE - ????
                 ak-lap

                        None                           BACKGROUND                Row Exclusive        NONE         65921-1            TO - Temporary Table Object Enqueue
                        None                           BACKGROUND                Row Exclusive        NONE         75829-1            TO - Temporary Table Object Enqueue

*NOTE --> ID1/ID2 VALUES: Id1 is the object number (obj#) of the temporary table object and id2 is always set to 1.*

16:45:04 SYS@MATRIX> select owner,object_name,object_type,status from dba_objects where object_id in (65921,75829);

OWNER                          OBJECT_NAME                                                                                                              OBJECT_TYPE         STATUS
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------
MDSYS                          SDO_GEOR_DDL__TABLE$$                                                                                                    TABLE               VALID
ANAND                          MY_GTT_PRESERVE                                                                                                          TABLE               VALID

I don’t know why and where from MDSYS.SDO_GEOR_DDL__TABLE$$ is being called.Even 10046 of the Session 1 shows this table being called.

Lets try truncating and then dropping the table.

Session 1
==========

16:47:25 ANAND@MATRIX> truncate table MY_GTT_PRESERVE;

Table truncated.

Session 2
==========

Username     SID Term   Table Name                     COMMAND                   Lock Held            Lock Requested       ID1 - ID2          Lock Type
---------- ----- ------ ------------------------------ ------------------------- -------------------- -------------------- ------------------ ------------------------------
ANAND        198 ananpr SYS.ORA$BASE                   TRUNCATE TABLE            Share                NONE                 100-0              AE - ????
                 ak-lap

                        None                           TRUNCATE TABLE            Row Exclusive        NONE         65921-1            TO - Temporary Table Object Enqueue

After truncating table MY_GTT_PRESERVE the “TO – Temporary Table Object Enqueue” lock is no more it.Now we can drop the table.

Session 1
==========

16:48:43 ANAND@MATRIX> drop table MY_GTT_PRESERVE;

Table dropped.

The above is only seen in case of GTT created with “on commit preserve rows” and not with “on commit delete rows”.When the table is created with “on commit delete rows”, the session does not acquire “TO – Temporary Table Object Enqueue” on it (the table) and hence can be dropped.

What ever i have mentioned above is as per my observation.It might not be totally correct.Share your valuable thoughts on it 🙂

Advertisements

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

  1. For what I know, the TO locks are held because during a user’s session rows were inserted into a temporary table and the session has not finished yet.
    A commit was performed, but the session itself is still active. As a result, while the session remains connected AND THE INFORMATION ON TEMPORARY TABLE ARE NOT DELETED, oracle holds the TO lock on this table to prevent other sessions from trying to alter it.

    That kind of makes sense. Because if the information must be preserved after commit it must be stored somewhere.
    That place for that is the temporary segments. So the table itself cannot be changed while there are active segments for it.

    A solution would be using “on commit delete rows” temporary tables.
    Or, if that is not possible, change the application for deleting the rows inserted on a “on commit perserve rows” temporary table as soon as they are used in order to release the locks.

    Is that what you mean?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s