RE: Name lob index in add column clause
- From: Michael Rosenblum <mrosenblum@xxxxxxxxxxx>
- To: "Joel.Patterson@xxxxxxxxxxx" <Joel.Patterson@xxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 9 Jul 2009 16:23:00 -0400
Syntax issue (see the order of comments):
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Joel.Patterson@xxxxxxxxxxx
Sent: Thursday, July 09, 2009 3:47 PM
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
