Re: Help with moving lobsegments and lobindexes....
- From: "Stefan Knecht" <knecht.stefan@xxxxxxxxx>
- To: Mark.Bobak@xxxxxxxxxxxx
- Date: Sat, 26 Jan 2008 10:38:08 +0100
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@CENTRAL> create table t (x blob) lob(x) store as "T_LOB_X" (tablespace
users);
Table created.
sys@CENTRAL> select column_name,segment_name from user_lobs where
table_name='T';
COLUMN_NAME SEGMENT_NAME
------------------------------ ------------------------------
X T_LOB_X
sys@CENTRAL> alter table t move lob (x) store as "T_NEW_LOB" (tablespace
sites);
Table altered.
sys@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@xxxxxxxxxxxx> 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@xxxxxxxxxxxxxxx
> 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@xxxxxxxxxxxx
http://www.trivadis.com
OCP 9i/10g SCSA SCNA
=========================
Other related posts:
