CODEWITHSUNDEEP
sqloracle-databaseoracle12c
d-_-b
d-_-b

Reputation: 4512

Why I can't create an index on temporary table?

First I created a temporary table

CREATE GLOBAL TEMPORARY TABLE TMP ON COMMIT PRESERVE ROWS AS
SELECT *
FROM A

then

INSERT INTO TMP
SELECT *
FROM B

COMMIT

finally

CREATE INDEX IDX ON TMP (COLA, COLB, COLC);

Upon creating the index, I got the following error

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

I followd these steps in the same session.

What I want to do(with temporary table) is

  1. Disable/Drop index
  2. Insert large data
  3. Enable/Create index

How can I acheive this?

Upvotes: 2

Views: 2807

Answers (3)

Ezio Freitas
Ezio Freitas

Reputation: 1

When you insert lines into a temp table it gets locked and the index can't be created. You should create the index before inserting the data:

CREATE GLOBAL TEMPORARY TABLE tmp ON COMMIT PRESERVE ROWS  AS
        SELECT *
          FROM scott.dept;

CREATE INDEX idx ON
    tmp (deptno);

ora-14452: tentativa de criar, alterar ou eliminar um índice em uma tabela temporária que já está sendo usada
14452. 00000 -  "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.
*Action:   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.


TRUNCATE TABLE tmp;

Table TMP truncado.

CREATE INDEX idx ON tmp (deptno); 

Index idx criado.

INSERT INTO tmp
    SELECT *
      FROM scott.dept;

4 linhas inserido.

COMMIT;

Commit concluído.

Upvotes: 0

Vecchiasignora
Vecchiasignora

Reputation: 1315

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

This error occurs when it is tried to perform a DDL on a global temporary table with on commit preserve rows when the session trying to do the DDL has already made a DML on the table. In order to do a DDL, the table must first be either truncated or the session must be exited.

http://www.adp-gmbh.ch/ora/err/ora_14452.html

Upvotes: 3

BobC
BobC

Reputation: 4432

I have to admit to being surprised by this. The only solution I have come up with so far is to re-establish the session

SQL> drop table tmp;

Table dropped.

SQL> 
SQL> CREATE GLOBAL TEMPORARY TABLE TMP ON COMMIT PRESERVE ROWS AS
  2  SELECT *
  3  FROM emp
  4  ;

Table created.

SQL> 
SQL> 
SQL> INSERT INTO TMP
  2  SELECT *
  3  FROM emp
  4  ;

14 rows created.

SQL> 
SQL> COMMIT
  2  ;

Commit complete.

SQL> 
SQL> connect scott/tiger
Connected.
SQL> 
SQL> CREATE INDEX IDX ON TMP (empno);

Index created.

Upvotes: 1

Related Questions