F1:
Finding tables with LOBS:
|
select * from
dba_lobs where tablespace_name='TOPREP_DAT'; |
OR
|
select * from
dba_lobs where tablespace_name='TOPREP_DAT' and segment_name in (select
segment_name from dba_segments where
tablespace_name='TOPREP_DAT') |
select distinct(compression) from dba_lobs;
select distinct(securefile) from dba_lobs;
To find LOBS and list them in ascending order size wise:
|
select segment_name from ( select
owner,segment_name,segment_type,sum(bytes/1024/1024/1024) GB from
dba_segments where tablespace_name='TOPREP_DAT' and segment_name in (select segment_name from
dba_lobs where owner='TOPOLOGY_RPT' and (securefile='NO' OR compression in
('NO','NONE'))) group by segment_name,segment_type,owner order by GB ) Then select
one of the segment and search in dba_lobs: select *
from dba_lobs where segment_name='SYS_LOB0000242285C00004$$'; |
Not working well query:
select * from dba_lobs where
segment_name in (select segment_name from (select owner,segment_name,segment_type,sum(bytes/1024/1024/1024)
GB from dba_segments where
tablespace_name='TOPREP_DAT'
and segment_name in (select segment_name
from dba_lobs where owner='TOPOLOGY_RPT') group by
segment_name,segment_type,owner order by GB) )
Topology_rpt.H_RADCOMVPMS_TOPOLOGY: (Columns_name:
Interface_IP)
select owner,table_name from dba_tables where table_name
like 'H_RADCOMVPMS_TOPOLOGY';
|
--Verify table and LOB size
|
SELECT (SELECT SUM(S.BYTES)/1024/1024/1024 FROM dba_SEGMENTS
S where
(S.SEGMENT_NAME like UPPER('H_RADCOMVPMS_TOPOLOGY'))) + (SELECT SUM(S.BYTES)/1024/1024/1024
-- The Lob Segment Size FROM dba_SEGMENTS S, dba_LOBS L WHERE (L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME like UPPER('H_RADCOMVPMS_TOPOLOGY') )) + (SELECT SUM(S.BYTES)/1024/1024/1024
-- The Lob Index size FROM dba_SEGMENTS S, dba_INDEXES I WHERE (I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME like UPPER('H_RADCOMVPMS_TOPOLOGY') AND INDEX_TYPE = 'LOB' )) "TOTAL
TABLE SIZE" FROM DUAL; Size: 0.00018310546875 |
|
select table_name, column_name, securefile,compression,tablespace_name from dba_lobs where TABLE_NAME like 'H_RADCOMVPMS_TOPOLOGY';
Set timing
on; ALTER TABLE
TOPOLOGY_RPT. H_RADCOMVPMS_TOPOLOGY MOVE LOB(INTERFACE_IP) STORE AS securefile (COMPRESS HIGH) online parallel
16; Post
activity: select table_name, column_name, securefile,compression,tablespace_name from dba_lobs where TABLE_NAME like 'H_RADCOMVPMS_TOPOLOGY';
|
--Verify table and LOB size
|
SELECT (SELECT SUM(S.BYTES)/1024/1024/1024 FROM dba_SEGMENTS
S where
(S.SEGMENT_NAME like UPPER('H_RADCOMVPMS_TOPOLOGY'))) + (SELECT SUM(S.BYTES)/1024/1024/1024
-- The Lob Segment Size FROM dba_SEGMENTS S, dba_LOBS L WHERE (L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME like UPPER('H_RADCOMVPMS_TOPOLOGY') )) + (SELECT SUM(S.BYTES)/1024/1024/1024
-- The Lob Index size FROM dba_SEGMENTS S, dba_INDEXES I WHERE (I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME like UPPER('H_RADCOMVPMS_TOPOLOGY') AND INDEX_TYPE = 'LOB' )) "TOTAL
TABLE SIZE" FROM DUAL; |
select segment_name from (
select owner,segment_name,segment_type,sum(bytes/1024/1024/1024)
GB from dba_segments where
--tablespace_name='TOPREP_DAT' and
segment_name in (select segment_name from dba_lobs where
--owner='TOPOLOGY_RPT' and
(owner not in
('SYS','SYSTEM','AUDSYS','%SYS%','XDB','WMSYS','APEX_050000') or owner not like
('%SYS%')) and
(securefile='NO' OR
compression in ('NO','NONE')))
group by segment_name,segment_type,owner order by GB
) ;
select * from dba_lobs where
segment_name='SYS_LOB0000242282C00006$$';
select owner,table_name from dba_tables where table_name
like 'NODE_INFO_ARCHIVE_DAILY_STAGE';
|
OWNER |
TABLE_NAME |
COLUMN_NAME |
SEGMENT_NAME |
TABLESPACE_NAME |
|
TOPOLOGY_RPT |
NODE_INFO_ARCHIVE_DAILY_STAGE |
RESPONSE |
SYS_LOB0000242282C00006$$ |
TOPREP_DAT |
|
COMPRESSION |
DEDUPLICATION |
IN_ROW |
FORMAT |
PARTITIONED |
SECUREFILE |
|
NONE |
NONE |
YES |
ENDIAN NEUTRAL |
NO |
NO |
select table_name,
column_name, securefile,compression,tablespace_name from dba_lobs where
TABLE_NAME like 'NODE_INFO_ARCHIVE_DAILY_STAGE';
|
TABLE_NAME |
COLUMN_NAME |
SECUREFILE |
COMPRESSION |
TABLESPACE_NAME |
|
NODE_INFO_ARCHIVE_DAILY_STAGE |
RESPONSE |
NO |
NONE |
TOPREP_DAT |
--before size 1140.864
|
SELECT (SELECT SUM(S.BYTES)/1024/1024/1024 FROM dba_SEGMENTS
S where
(S.SEGMENT_NAME like UPPER('NODE_INFO_ARCHIVE_DAILY_STAGE'))) + (SELECT SUM(S.BYTES)/1024/1024/1024
-- The Lob Segment Size FROM dba_SEGMENTS S, dba_LOBS L WHERE (L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME like UPPER('NODE_INFO_ARCHIVE_DAILY_STAGE') )) + (SELECT SUM(S.BYTES)/1024/1024/1024
-- The Lob Index size FROM dba_SEGMENTS S, dba_INDEXES I WHERE (I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME like UPPER('NODE_INFO_ARCHIVE_DAILY_STAGE') AND INDEX_TYPE = 'LOB' )) "TOTAL
TABLE SIZE" FROM DUAL; |
Set timing on;
Set timing on;
ALTER TABLE TOPOLOGY_RPT.NODE_INFO_ARCHIVE_DAILY_STAGE MOVE
LOB(RESPONSE) STORE AS securefile ;
ALTER TABLE TOPOLOGY_RPT.NODE_INFO_ARCHIVE_DAILY_STAGE MOVE
LOB(RESPONSE) (COMPRESS HIGH) online parallel 32;
No comments:
Post a Comment