Help with moving lobsegments and lobindexes....
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 25 Jan 2008 13:22:34 -0500
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<mailto:mark.bobak@xxxxxxxxxxxxxxx>
www.proquest.com<http://www.proquest.com>
www.csa.com<http://www.csa.com>
ProQuest...Start here.
Other related posts:
