Home » SQL & PL/SQL » SQL & PL/SQL » ORA-14451 , creating index for a temporary table
ORA-14451 , creating index for a temporary table [message #235070] Fri, 04 May 2007 00:01 Go to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
i have a temporary table like this


CREATE GLOBAL TEMPORARY TABLE TEMP( 
  ID  NUMBER) ON COMMIT DELETE ROWS; 



now i am creating a non unique index to it using script



DECLARE

    i_tblsp   VARCHAR2(30); 

    l_sqlstmt VARCHAR2(32767);

    l_inxname varchar2(30);

    l_cnt     NUMBER; 

BEGIN

    --

    SELECT tablespace_name

    INTO i_tblsp

    FROM user_indexes WHERE index_name like 'PK1';

----

    begin

        select index_name into l_inxname from user_indexes 

        where index_name like 'IX_TEMP';

    exception 
	when no_data_found then

        l_sqlstmt := 'CREATE INDEX IX_TEMP ON TEMP(ID) ';

        l_sqlstmt := l_sqlstmt ||' TABLESPACE '||i_tblsp ;

        dbms_output.put_line(l_sqlstmt); 

        EXECUTE IMMEDIATE l_sqlstmt;

    end;

----

EXCEPTION

    WHEN OTHERS THEN

        DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

/




i am getting this error
ORA-14451 unsupported feature with temporary table

how to solve this?


Re: ORA-14451 , creating index for a temporary table [message #235072 is a reply to message #235070] Fri, 04 May 2007 00:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Don't specify a tablespace. As you can see, an index on a GTT has no tablespace:
SQL> drop table faq;

Table dropped.

SQL> create global temporary table faq
  2  (id number) on commit delete rows;

Table created.

SQL> create index faq_i on faq(id);

Index created.

SQL> select tablespace_name from user_indexes where index_name = 'FAQ_I';

TABLESPACE_NAME
------------------------------


SQL> drop index faq_i;

Index dropped.

SQL> create index faq_i on faq(id) tablespace users;
create index faq_i on faq(id) tablespace users
                      *
ERROR at line 1:
ORA-14451: unsupported feature with temporary table


SQL> create index faq_i on faq(id) tablespace temp;
create index faq_i on faq(id) tablespace temp
                      *
ERROR at line 1:
ORA-14451: unsupported feature with temporary table


SQL> create index faq_i on faq(id) tablespace undo;
create index faq_i on faq(id) tablespace undo
                      *
ERROR at line 1:
ORA-14451: unsupported feature with temporary table


SQL> create index faq_i on faq(id) tablespace system;
create index faq_i on faq(id) tablespace system
                      *
ERROR at line 1:
ORA-14451: unsupported feature with temporary table

[Updated on: Fri, 04 May 2007 00:10]

Report message to a moderator

Re: ORA-14451 , creating index for a temporary table [message #235079 is a reply to message #235070] Fri, 04 May 2007 00:42 Go to previous messageGo to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
thanks but on which tablespace does this index gets created?

i dont want it to be the default system tablespace, as
we are doing this on a product

can it be created on any temporary tablespace then?

Re: ORA-14451 , creating index for a temporary table [message #235082 is a reply to message #235079] Fri, 04 May 2007 00:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Check my previous post!
The tablespace_name is empty.
GTTs exist in memory, as do their indexes.
Re: ORA-14451 , creating index for a temporary table [message #235085 is a reply to message #235070] Fri, 04 May 2007 00:52 Go to previous messageGo to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
thanks a lot for this info..i got to learn a new thing today

Cool
Re: ORA-14451 , creating index for a temporary table [message #343088 is a reply to message #235070] Tue, 26 August 2008 04:27 Go to previous messageGo to next message
Assaf
Messages: 2
Registered: August 2008
Junior Member
great post. thanks a lot.
Re: ORA-14451 , creating index for a temporary table [message #343093 is a reply to message #235082] Tue, 26 August 2008 04:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I thought GTTs lived in the Temporary tablespace rather than a memory only constructs.
Re: ORA-14451 , creating index for a temporary table [message #343221 is a reply to message #343093] Tue, 26 August 2008 12:07 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Me too. Create a GTT with an index, then have a look in V$TEMPSEG_USAGE before and after inserting a row in the table, then again after committing. I get one DATA and one INDEX segment showing up in tablespace TEMP for the duration of the transaction (10.2).
Re: ORA-14451 , creating index for a temporary table [message #343678 is a reply to message #343221] Wed, 27 August 2008 16:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
uh oh...

I stand corrected, I think..
Re: ORA-14451 , creating index for a temporary table [message #343696 is a reply to message #343678] Wed, 27 August 2008 18:01 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:927229360974
Previous Topic: Query everything, everywhere? (all rows, columns)
Next Topic: Passing parameter from Crystal to Oracle
Goto Forum:
  


Current Time: Sun Dec 11 05:59:42 CST 2016

Total time taken to generate the page: 0.07817 seconds