Help with moving lobsegments and lobindexes....

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%')

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<mailto:mark.bobak_at_il.proquest.com> www.proquest.com<http://www.proquest.com> www.csa.com<http://www.csa.com>

ProQuest...Start here.

--

http://www.freelists.org/webpage/oracle-l Received on Fri Jan 25 2008 - 12:22:34 CST