Name lob index in add column clause

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


Other related posts: