Re: Help with moving lobsegments and lobindexes....

From: Stefan Knecht <knecht.stefan_at_gmail.com>
Date: Sat, 26 Jan 2008 10:38:08 +0100
Message-ID: <486b2b610801260138n1f5a0347mf7b7af82c48ed9a1@mail.gmail.com>


Hi Mark

And another thing to consider -- you never have to worry about the LOBINDEX segment -- in fact, you can't to anything to/with/about it. Oracle handles that transparently under the covers.

So it's enough to move the LOBSEGMENT to a new tablespace, the LOBINDEX will follow.

This still applies with Securefiles (improved LOB datatype) in 11g BTW.

Oh, and if you don't like the system generated names, give them your own. Use the STORE AS "<Your Lob Name>" clause in CREATE TABLE or ALTER TABLE statements:

sys_at_CENTRAL> create table t (x blob) lob(x) store as "T_LOB_X" (tablespace users);

Table created.

sys_at_CENTRAL> select column_name,segment_name from user_lobs where table_name='T';

COLUMN_NAME                    SEGMENT_NAME
------------------------------ ------------------------------
X                              T_LOB_X

sys_at_CENTRAL> alter table t move lob (x) store as "T_NEW_LOB" (tablespace sites);

Table altered.

sys_at_CENTRAL> select column_name,segment_name from user_lobs where table_name='T';

COLUMN_NAME                    SEGMENT_NAME
------------------------------ ------------------------------
X                              T_NEW_LOB



Cheers

Stefan

On Jan 25, 2008 7:22 PM, Bobak, Mark <Mark.Bobak_at_proquest.com> wrote:

> Ok, I'm a little confused here, and looking for some help.
>
>
>
> This is 10gR2 (10.2.0.3) on Linux.
>
>
>
> I'm trying to move some LOBSEGMENTs and LOBINDEXes from one tablespace to
> another.
>
>
>
> If I run this query:
>
> 1 select tablespace_name,
>
> 2 owner,
>
> 3 segment_type,
>
> 4 segment_name
>
> 5 from dba_segments
>
> 6 where (tablespace_name like 'PQDS%'
>
> 7 or tablespace_name like 'PQDM%'
>
> 8 or tablespace_name like 'PQDL%')
>
> 9* and segment_type like 'LOB%'
>
> SQL> /
>
>
>
> TABLESPACE_NAME OWNER
> SEGMENT_TYPE SEGMENT_NAME
>
> ------------------------------ ------------------------------
> ------------------ ------------------------------
>
> PQDMDATA ADDS
> LOBSEGMENT SYS_LOB0000076746C00005$$
>
> PQDMDATA ADDS
> LOBINDEX SYS_IL0000076746C00005$$
>
> PQDMDATA ADDS
> LOBSEGMENT SYS_LOB0000076892C00005$$
>
> PQDMDATA ADDS
> LOBINDEX SYS_IL0000076892C00005$$
>
> PQDMDATA ADDS
> LOBSEGMENT SYS_LOB0000076980C00013$$
>
> PQDMDATA ADDS
> LOBINDEX SYS_IL0000076980C00013$$
>
> PQDLINDX ADDS
> LOBINDEX SYS_IL0000078274C00002$$
>
> PQDLINDX ADDS
> LOBSEGMENT SYS_LOB0000078274C00002$$
>
>
>
> 8 rows selected.
>
>
>
> I see 4 lobsegments and 4 lobindexes, that I want to move. First off,
> they're system generated names, so I have no idea what table/column they are
> associated with. I want to move all of these into a tablespace called
> PQDDATA.
>
>
>
> So, I run another query:
>
> SQL> select table_name , column_name from dba_tab_columns where
> owner='ADDS' and data_type like '%LOB'
>
> 2 /
>
>
>
> TABLE_NAME COLUMN_NAME
>
> ------------------------------ ------------------------------
>
> PM_TAGS PMTG_TAG_VALUE
>
> MODULE_PARAMETERS MPAR_LOV_QUERY
>
> CREATE$JAVA$LOB$TABLE LOB
>
> TEMP_SOH_TEST XML_DATA
>
> RECOMMENDED_LIST RLT_DOCUMENT
>
>
>
>
>
> And I see 5 tables that have LOB columns. So, based on MetaLink Doc ID
> 100548.1, I try to move these by doing something like:
>
> alter table pm_tags move lob(PMTG_TAG_VALUE) store as (tablespace
> pqddata);
>
>
>
> This command is syntactically correct, and succeeds, returning "Table
> altered.", but, the logsegment and lobindex haven't actually moved.
>
>
>
> Can anyone offer me a clue or suggestion as to what I'm missing here?
> Also, does anyone know how to map the system generated lobsegment and
> lobindex names back to a specific table/column?
>
>
>
> AdvThanksance,
>
>
>
> -Mark
>
>
>
> *--
> Mark J. Bobak*
> *Senior Database Administrator, System & Product Technologies*
> ProQuest
> 789 E. Eisenhower, Parkway, P.O. Box 1346
> Ann Arbor MI 48106-1346
> +1.734.997.4059 or +1.800.521.0600 x 4059
> mark.bobak_at_il.proquest.com
> www.proquest.com
> www.csa.com
>
> *ProQuest...*Start here.
>
>
>

-- 
=========================

Stefan P Knecht
Senior Consultant
Infrastructure Managed Services

Trivadis AG
Europa-Strasse 5
CH-8152 Glattbrugg

Phone +41-44-808 70 20
Fax +41-808 70 12
Mobile +41-79-571 36 27
stefan.knecht_at_trivadis.com
http://www.trivadis.com

OCP 9i/10g SCSA SCNA
=========================

--
http://www.freelists.org/webpage/oracle-l
Received on Sat Jan 26 2008 - 03:38:08 CST

Original text of this message