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

1 Answer

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

David Miller

David Miller

Executive Financial & Market Analyst

David Miller brings 15 years of experience in global economics, personal finance strategy, and market dynamics. He specializes in turning complex economic trends into actionable insights for everyday readers.

Share this article
Twitter Facebook Pinterest