Segment Size 조회(테이블/인덱스 사이즈 조회)

 

• 세그먼트 조회 SQL문 정리

 

================================================================
- Segment Size Check
================================================================
 
-- Segment Size check
 
select owner,
       segment_name,
       round(sum(bytes)/1024/1024,3) as seg_mb
  from dba_segments
 where segment_name in ('SegmentName')
group by owner, segment_name
order by partition_name;
 
 
-- Segment Size Detail Check(Partition Table)
select owner,
       segment_name,
       partition_name,
       tablespace_name,
       round(sum(bytes)/1024/1024,3) as seg_mb
  from dba_segments
 where segment_name in ('SegmentName')
group by owner, segment_name,partition_name,tablespace_name
order by partition_name;
 
 
================================================================
- Index Size Check
================================================================
 
select s.owner,
       s.segment_name,
       round(sum(s.bytes)/1024/1024,3) as seg_mb
  from (
         select owner,
                index_name
           from dba_indexes
          where table_owner = 'TableOwner'
            and table_name = 'TableName'
       ) i, dba_segments s
 where i.owner = s.owner
   and i.index_name = s.segment_name
group by s.owner, s.segment_name
order by s.segment_name;
 
 
================================================================
- LOB Segment Size Check
================================================================
 
select owner,
       segment_name,
       round(sum(bytes)/1024/1024,3) as seg_mb
  from dba_segments
 where tablespace_name = 'TableSpaceName' --Lob Segment Tablespace
group by owner, segment_name
order by seg_mb desc;
 
select lp.table_owner,
       lp.table_name,
       lp.column_name,
       lp.lob_name,
       lp.partition_name,
       lp.lob_partition_name,
       --lp.lob_indpart_name,
       s.tablespace_name,
       round(s.bytes/1024/1024,3) as lob_seg_mb
  from dba_lobs l, dba_lob_partitions lp, dba_segments s
 where l.table_name = 'TableName'
   and l.owner = lp.table_owner
   and l.table_name = lp.table_name
   and l.column_name = lp.column_name
   and lp.table_owner = s.owner
   and lp.lob_name = s.segment_name
   and lp.lob_partition_name = s.partition_name
   --and lp.partition_name = 'PR_201709'
order by lp.table_owner, lp.table_name, lp.lob_name, lp.partition_name;