Name lob index in add column clause

From: <Joel.Patterson_at_crowley.com>
Date: Thu, 9 Jul 2009 15:46:38 -0400
Message-ID: <0684DA55864E404F8AD2E2EBDFD557DA03031A80_at_JAXMSG01.crowley.com>


I just create an SR using MOS for this..... Not. I went to submit it and it did nothing, (and not the first time). I go to select 'service requests' and it says I'm about to loose all my data... hmmm (the warning seems new as that good news was unexpected). So I select it all and save it in a new email -- (cause not first time). Sure enough I hit F5 to refresh and its gone.

But I'll try here and LazyDBA now to see if this is impossible before attempting the SR process over again.


I can name a lob segment and lob index when creating a table that contains a CLOB data type. (select * from user_lobs), they are both named.

But I cannot name the LOB INDEX wen adding a CLOB column to a table via alter table.
How to do that? See below.

This create table names both successfully.

Create table dippitydodah ...
....
TABLESPACE "DATA1"

      LOB ("RESUME")
           STORE AS RESUME_LOB_SG (
                  TABLESPACE "DATA1" ENABLE STORAGE IN ROW
                  CHUNK 8192 PCTVERSION 10 NOCACHE LOGGING
                  STORAGE (
                      INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645
                      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT

)
INDEX RESUME_LOB_IX ( TABLESPACE INDEX1 STORAGE (MAXEXTENTS UNLIMITED)
)
)

This statement names the lob segment.

ALTER TABLE employees ADD (resume CLOB)
  LOB (resume) STORE AS resume_LOG_seg (TABLESPACE index1)

I can NOT run this statement that names the lob index, and not sure of syntax since it is not successful.

ALTER TABLE employees ADD (resume CLOB)
  LOB (resume) STORE AS resume_LOG_seg (TABLESPACE index1)   INDEX RESUME_LOB_IX (TABLESPACE INDEX1 ); Actually I get ORA-02461: Inappropriate use of the INDEX option with this one.

P.S. I'll be back in the morning.



TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jul 09 2009 - 14:46:38 CDT

Original text of this message