Re: move lob tables

From: Brian Tkatch <SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK>
Date: Thu, 21 Jun 2001 14:53:31 GMT
Message-ID: <3b32095e.252569687@news.alt.net>

On Wed, 20 Jun 2001 22:02:03 +0100, "Eric Parker" <system.development.team_at_bt.com> wrote:

>This is one that I was hoping somebody would be able to answer.
>I also would like to move a LOB index.
>
>Thanks
>

SELECT

        'ALTER TABLE '
        || LOBS.Table_Name
        || ' MOVE TABLESPACE <Table TB> LOB('
        || lobs.Column_Name
        || ') STORE AS (TABLESPACE <Lob TB>);'
FROM
        sys.dba_lobs lobs,
        sys.dba_segments segs
WHERE 
          lobs.Segment_Name = segs.Segment_Name

AND lobs.owner = '<owner>';

The indexes move with the LOBS whether youy want them to or not.

Talking about the lob_index_clause in ALTER TABLE:

This clause is deprecated as of Oracle8i. Oracle generates an index for each LOB column. The LOB indexes are system named and system managed, and reside in the same tablespace as the LOB data segments.

It is still possible for you to specify this clause in some cases. However, Oracle Corporation strongly recommends that you no longer do so. In any event, do not put the LOB index in a different tablespace from the LOB data.

See Also: Oracle8i Migration for information on how Oracle manages LOB indexes in tables migrated from earlier versions

HTH,
Brian Received on Thu Jun 21 2001 - 09:53:31 CDT