Hi All,
We were trying to reorganize objects in a production environment by expdp/impdp. Everything was going ok until we hit a lobsegment that was 200gb in size:
Oracle, EE, 11.2.0.4
AIX, 6.1
select owner, segment_name, segment_type, round(bytes/1024/1024,2) size_in_mb
from dba_segments
where segment_name='KLONG'
and bytes/1024/1024/1024 > 50
order by 4 desc;
OWNER SEGMENT_NAME SEGMENT_TYPE SIZE_IN_GB
-------------------- ----------------------------------- ------------------ ----------
PRODLIVELINK SYS_LOB0000052068C00004$$ LOBSEGMENT 200.98
select owner, TABLE_NAME,COLUMN_NAME,SEGMENT_NAME,TABLESPACE_NAME from dba_lobs where SEGMENT_NAME='SYS_LOB0000052068C00004$$'
OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME
-------------------- -------------------- ------------------------------ ----------------------------------- ------------------------------
PRODLIVELINK KLONG SEGMENT SYS_LOB0000052068C00004$$ PROD_LIVE_LINK_TS
select owner, segment_name, segment_type, round(bytes/1024/1024,2) size_in_mb
from dba_segments
where segment_name='KLONG';
OWNER SEGMENT_NAME SEGMENT_TYPE SIZE_IN_MB
-------------------- ----------------------------------- ------------------ ----------
PRODLIVELINK KLONG TABLE 649.44
Is it common to have the lobsegment object a lot bigger than the table it's associated with? Also can we find out how much we can shrink a lobsegment by without actually shrinking it?
looking at v$session_longops, the export of the lobsegment was estimated at 14hrs roughly, so I don't want to imagine what the import time will be - Do you know if moving the table online to a different
tablespace, rebuilding the indexes, and then moving the lobsegment to the new tablespace would be faster than an export/import?
Is there another way of doing this?
Can you please share your experience and/or ideas.
Much appreciated.