CODEWITHSUNDEEP
oracle-database
Yoga
Yoga

Reputation: 293

Create index on Global temporary table

Is it possible to create index on Global temporary table? If Yes, Do we need to create in a single session.

I tried the following way and ended up with the below issue.

Session 1:

CREATE GLOBAL TEMPORARY TABLE "SFE_ADM"."DUMMY_GLO" 
   (    "C1" VARCHAR2(6 CHAR)
   ) ON COMMIT PRESERVE ROWS ;

CREATE INDEX DUMMY_GLO_IDX ON DUMMY_GLO (C1) TABLESPACE SFE_I1;

Session 1:

CREATE GLOBAL TEMPORARY TABLE "SFE_ADM"."DUMMY_GLO" 
       (    "C1" VARCHAR2(6 CHAR)
       ) ON COMMIT PRESERVE ROWS ;

Session 2:

CREATE INDEX DUMMY_GLO_IDX ON DUMMY_GLO (C1) TABLESPACE SFE_I1;

Session 1:

CREATE GLOBAL TEMPORARY TABLE "SFE_ADM"."DUMMY_GLO" 
       (    "C1" VARCHAR2(6 CHAR)
       ) ON COMMIT PRESERVE ROWS ;

Session 2:

TRUNCATE TABLE DUMMY_GLO
CREATE INDEX DUMMY_GLO_IDX ON DUMMY_GLO (C1) TABLESPACE SFE_I1;

For all the above tries i got an error message saying.

getting an error while creating index for temporary table
Error starting at line : 1 in command -
CREATE INDEX DUMMY_GLO_IND ON DUMMY_GLO (C1) TABLESPACE SFE_I1
Error at Command Line : 1 Column : 42
Error report -
SQL Error: ORA-14451: unsupported feature with temporary table
14451. 00000 -  "unsupported feature with temporary table"
*Cause:    An attempt was made to create an IOT, specify physical attributes,
           specify partition or parallel clause.
*Action:   do not do that

Upvotes: 2

Views: 25744

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132670

Yes you can create an index on a temporary table, but you cannot specify a tablespace for it:

SQL> CREATE INDEX DUMMY_GLO_IDX ON DUMMY_GLO (C1);

Index created.

I'm not sure why you are trying to use different sessions. A global temporary table and its index are only to be created once, just like a normal table. Then many sessions can use the table at once, without seeing each other's data.

Upvotes: 7

Related Questions