Re: Move LOB index to a diff.tbs

I'm fairly sure that you can't specify the lob locator index tablespace name, even though Oracle will accept the syntax. The lob locator indexes go wherever the lob goes. You can, however, specify different buffer pools for the table/lob segment/lob index. See my example below.

-Jamie

create table lob_test
( ID number,
  COLLECTION_DATE date,
  DATA clob
)
storage (buffer_pool recycle)
tablespace data
lob (data) store as

	(enable storage in row 
	 chunk 8192 
	 pctversion 0 
	 nocache 
	 tablespace lobs 
	 storage (buffer_pool recycle)
	 index (tablespace lob_indexes storage (buffer_pool recycle))
	)

select index_name, buffer_pool, tablespace_name from user_indexes
where table_name = 'LOB_TEST';

INDEX_NAME                     BUFFER_ TABLESPACE_NAME
------------------------------ ------- ------------------------------
SYS_IL0000054437C00003$$       RECYCLE LOBS

alter table lob_test move tablespace RMAN lob (data) store as
( tablespace lob_indexes
  index (tablespace lobs));

select index_name, buffer_pool, tablespace_name from user_indexes
where table_name = 'LOB_TEST'; 2 3

INDEX_NAME                     BUFFER_ TABLESPACE_NAME
------------------------------ ------- ------------------------------
SYS_IL0000054440C00003$$       RECYCLE LOB_INDEXES

On Tue, 21 Sep 2004 11:11:00 -0700, GovindanK <gkatteri_at_fastmail.fm> wrote:
> Subject : Move LOB index to a diff.tbs
>
> Hello Team
>
> Can anyone tell me how to move a LOB Segment(Index) to a
> diff.tablespace?
> ALTER TABLE tname MOVE TABLESPACE newtbs LOB(CONTENT) STORE AS
> (TABLESPACE newtbs DISABLE STORAGE IN ROW) takes care of Data Segment
> only and not Index Segment?
>
> Is this provision is avbl. in 9iRel2 OR is it that i missed something.
>
> thx
>
> GovindanK

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