RE: Name lob index in add column clause

ALTER TABLE employees ADD (resume CLOB)

LOB (resume) STORE AS resume_LOG_seg

    ( -- start LOB

     TABLESPACE index1

     INDEX RESUME_LOB_IX           ( -- start INDEX

              TABLESPACE INDEX1            ) -- end INDEX

     ); -- end LOB

At least my database (10.2.0.4 on Win 32-bit) agreed with it.

Regards,

Michael Rosenblum

Dulcian Inc

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Joel.Patterson_at_crowley.com

Sent: Thursday, July 09, 2009 3:47 PM

To: oracle-l_at_freelists.org

Subject: 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<http://jobs.lazydba.com/> To Subscribe : http://www.LazyDBA.com<http://www.lazydba.com/> To unsubscribe: http://www.lazydba.com/unsubscribe.html

--

http://www.freelists.org/webpage/oracle-l







--
http://www.freelists.org/webpage/oracle-l