查看Lob对象大小的一些常用SQL

--查看表的lob字段和每个字段的大小
select a.owner,
       a.table_name,
       a.column_name,
       a.segment_name,
       c.column_id,
       round(bytes / 1024 / 1024 / 1024, 2) size_gb
  from dba_lobs a, dba_segments b, dba_tab_columns c
 where a.segment_name = b.segment_name
   and a.owner = 'FLEXCUBE01'
   and a.owner = b.owner
   and a.segment_name = b.segment_name
   and segment_type like 'LOB%'
   and a.table_name not like 'SYS%'
      --and b.bytes > 100 * 1024 * 1024
   and a.owner = c.owner
   and a.table_name = c.table_name
   and a.column_name = c.column_name
 order by 2, 5;

--查看表的lob字段总大小
select a.owner,
       table_name,
       round(sum(bytes / 1024 / 1024 / 1024), 2) size_gb
  from dba_lobs a, dba_segments b
 where a.segment_name = b.segment_name
   and a.owner = 'FLEXCUBE01'
   and a.owner = b.owner
   and a.segment_name = b.segment_name
   and segment_type like 'LOB%'
   and a.table_name not like 'SYS_EXPORT_SCHEMA%'
   and bytes > 100 * 1024 * 1024
 group by a.owner, a.table_name;

--查看表上每个索引的大小
select a.owner,
       b.table_name,
       b.index_name,
       round(a.bytes / 1024 / 1024 / 1024, 2) index_gb
  from dba_segments a, dba_indexes b
 where a.owner = b.table_owner
   and a.owner = 'FLEXCUBE01'
   and a.segment_name = b.index_name
   and a.segment_type like 'INDEX%'
 order by 2, 4 desc;

--查看每个表的索引总大小
select a.owner,
       b.table_name,
       sum(round(a.bytes / 1024 / 1024 / 1024, 2)) index_gb
  from dba_segments a, dba_indexes b
 where a.owner = b.table_owner
   and a.owner = 'FLEXCUBE01'
   and a.segment_name = b.index_name
   and a.segment_type like 'INDEX%'
 group by a.owner, b.table_name;

--查看带lob字段的表各自所占用的空间
select a.owner,
       a.segment_name,
       round(a.bytes / 1024 / 1024 / 1024, 2) size_gb,
       c.lob_gb,
       d.index_gb,
       (round(a.bytes / 1024 / 1024 / 1024, 2) + c.lob_gb + d.index_gb) as total_gb
  from dba_segments a,
       (select a.owner,
               table_name segment_name,
               round(sum(bytes / 1024 / 1024 / 1024), 2) lob_gb
          from dba_lobs a, dba_segments b
         where a.segment_name = b.segment_name
           and a.owner = 'FLEXCUBE01'
           and a.owner = b.owner
           and a.segment_name = b.segment_name
           and segment_type like 'LOB%'
           and a.table_name not like 'SYS_EXPORT_SCHEMA%'
           and bytes > 100 * 1024 * 1024
         group by a.owner, a.table_name) c,
       (select a.owner,
               b.table_name,
               sum(round(a.bytes / 1024 / 1024 / 1024, 2)) index_gb
          from dba_segments a, dba_indexes b
         where a.owner = b.table_owner
           and a.owner = 'FLEXCUBE01'
           and a.segment_name = b.index_name
           and a.segment_type like 'INDEX%'
         group by a.owner, b.table_name) d
 where a.segment_name = c.segment_name
   and a.owner = 'FLEXCUBE01'
   and a.owner = c.owner
   and a.segment_name = c.segment_name
   and a.segment_name not like 'SYS_EXPORT_SCHEMA%'
   and a.owner = d.owner
   and a.segment_name = d.table_name
 order by 6 desc;

--查看非lob表的表大小和索引大小
select a.owner,
       a.segment_name,
       round(a.bytes / 1024 / 1024 / 1024, 2) size_gb,
       d.index_gb,
       (round(a.bytes / 1024 / 1024 / 1024, 2) + d.index_gb) as total_gb
  from dba_segments a,
       (select a.owner,
               b.table_name,
               sum(round(a.bytes / 1024 / 1024 / 1024, 2)) index_gb
          from dba_segments a, dba_indexes b
         where a.owner = b.table_owner
           and a.owner = 'FLEXCUBE01'
           and a.segment_name = b.index_name
           and a.segment_type like 'INDEX%'
         group by a.owner, b.table_name) d
 where a.owner = 'FLEXCUBE01'
   and a.segment_name not like 'SYS_EXPORT_SCHEMA%'
   and a.bytes > 1024 * 1024 * 1024 * 0.1
   and a.owner = d.owner
   and a.segment_name = d.table_name
 order by 5 desc;

--查看某用户下单个表的大小分配情况
select a.owner,
       a.table_name,
       a.num_rows,
       a.avg_row_len,
       round(a.avg_row_len * a.num_rows / 1024 / 1024, 2) real_MB,
       round(b.seg_bytes_mb, 2) seg_mb,
       round(b.seg_bytes_mb, 2) -
       round(a.avg_row_len * a.num_rows / 1024 / 1024, 2) as high_mb,
       decode(a.num_rows,
              0,
              100,
              (1 - round(a.avg_row_len * a.num_rows / 1024 / 1024 /
                         b.seg_bytes_mb,
                         2)) * 100) || '%' frag_percent
  from dba_tables a,
       (select owner, segment_name, sum(bytes / 1024 / 1024) seg_bytes_mb
          from dba_segments
         group by owner, segment_name) b
 where a.table_name = b.segment_name
   and a.owner = b.owner
   and a.owner in ('FLEXCUBE01')
   and round(b.seg_bytes_mb, 2) > 100
      --and a.table_name='T_ZDW_DOWN_SYNC_REC'
   and decode(a.num_rows,
              0,
              100,
              (1 - round(a.avg_row_len * a.num_rows / 1024 / 1024 /
                         b.seg_bytes_mb,
                         2)) * 100) > 5
 order by 8 desc;

--表空间监控：
--set linesize 180 pagesize 150
select a.tablespace_name,
       trunc((a.bytes - nvl(f.bytes, 0)) / 1048576) used_mb,
       trunc(a.bytes / 1048576) alloc_size_mb,
       trunc((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 1) alloc_used_pct,
       trunc(a.maxbytes / 1048576) max_size_mb,
       trunc((a.bytes - nvl(f.bytes, 0)) / a.maxbytes * 100, 1) max_used_pct
  from (select tablespace_name,
               sum(bytes) bytes,
               sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) bytes
          from dba_free_space
         group by tablespace_name) f
 where a.tablespace_name = f.tablespace_name(+)
 order by max_used_pct;

--查看每个数据文件的使用情况
select tablespace_name,
       file_name,
       a.file_id,
       bytes / 1024 / 1024 file_size,
       round(a.free_size, 2) free_size,
       autoextensible,
       increment_by next,
       round(maxbytes / 1024 / 1024) max_size,
       round(((bytes / 1024 / 1024) - a.free_size) / (bytes / 1024 / 1024) * 100,
             2) used_rate
  from dba_data_files b,
       (select file_id, sum(bytes) / 1024 / 1024 free_size
          from dba_free_space
         group by file_id) a
 where b.file_id = a.file_id
   and tablespace_name = 'FCC_DATA_SMALL'
 order by 2;

--空间多余100M以上的才回收
select a.file#,
       a.name,
       round(a.bytes / 1024 / 1024, 2) file_size,
       round((a.bytes - hwm * a.block_size) / 1024 / 1024, 2) release_mb,
       'alter database datafile ''' || a.name || ''' resize ' ||
       ceil(hwm * a.block_size / 1024 / 1024 + 30) || 'm;' resize_sql
  from v$datafile a,
       (select file_id, max(block_id + blocks - 1) hwm
          from dba_extents
         group by file_id) b
 where a.file# = b.file_id(+)
   and (a.bytes - hwm * a.block_size) / 1024 / 1024 > 100;

--查看某用户下单个表的大小分配情况
select a.owner,
       a.table_name,
       a.num_rows,
       a.avg_row_len,
       round(a.avg_row_len * a.num_rows / 1024 / 1024, 2) real_mb,
       round(b.seg_bytes_mb, 2) seg_mb,
       round(b.seg_bytes_mb, 2) -
       round(a.avg_row_len * a.num_rows / 1024 / 1024, 2) as high_mb,
       decode(a.num_rows,
              0,
              100,
              (1 - round(a.avg_row_len * a.num_rows / 1024 / 1024 /
                         b.seg_bytes_mb,
                         2)) * 100) || '%' frag_percent,
       a.row_movement,
       c.stale_stats,
       c.last_analyzed
  from dba_tables a,
       (select owner, segment_name, sum(bytes / 1024 / 1024) seg_bytes_mb
          from dba_segments
         group by owner, segment_name) b,
       dba_tab_statistics c
 where a.table_name = b.segment_name
   and a.owner = b.owner
   and a.owner in ('FLEXCUBE01')
   and a.owner = c.owner
   and a.table_name = c.table_name
   and c.object_type = 'TABLE'
   and round(b.seg_bytes_mb, 2) > 100
      --and a.table_name='T_ZDW_DOWN_SYNC_REC'
   and decode(a.num_rows,
              0,
              100,
              (1 - round(a.avg_row_len * a.num_rows / 1024 / 1024 /
                         b.seg_bytes_mb,
                         2)) * 100) > 5
 order by 8 desc, 2;