<jsp:directive.page pageEncoding="UTF-8" />
select 
  owner,
  segment_name,
  partition_name,
  AVG_ROW_LEN,
  NUM_ROWS,
  ROWS_PER_BLOCK,
  KB,
  EMPTY_KB,
  USED_KB,
  EST_KB
from 
 (
  select /*+ ORDERED */
    ds.owner,
    ds.segment_name,
    ' ' partition_name,  /* パーティション名は存在しないため、ダミー値' 'を取得する */
    ds.segment_type,
     /* avg_row_lenがnullの場合は"*"、0の場合は"?"、それ以外の場合はavg_row_lenの値を表示 */
    lpad(decode(avg_row_len,null,'***',0,'?',avg_row_len),4) AVG_ROW_LEN,
     /* num_rowsがnullの場合は"*"、0の場合は"?"、それ以外の場合はnum_rowsの値を表示 */
    lpad(decode(num_rows,null,'***',num_rows),9) NUM_ROWS,
     /* ブロックサイズからオーバヘッド、PCT_FREEを引いて、ブロック中で実際に使用可能サイズを求める */
    ceil((vp.db_block_size - KCBH - UB4 - KTBBH - (ini_trans - 1) * KTBIT - KDBH) * 
          (1 - pct_free / 100) - KDBT
         ) FREE_PER_BLOCK,
     /* 1行に何件はいるかを求めるため、上で求めた使用可能領域をavg_row_lenで割り、小数点以下を切り捨てる */
    lpad(decode(avg_row_len,null,'***',0,'?',
            trunc((ceil((vp.db_block_size - KCBH - UB4 - KTBBH - (ini_trans - 1) * KTBIT - KDBH) * 
            (1 - pct_free / 100) - KDBT))/avg_row_len)),10
         ) ROWS_PER_BLOCK,
    ds.bytes/1024 KB,
    lpad(decode(dt.BLOCKS,null,'***',(dt.BLOCKS+1)*vp.db_block_size/1024),8) USED_KB,
     /* ブロック当りに格納可能な件数を求め、num_rowsをその値で割ることで、必要なブロック数を求める。
        その値にセグメント・ヘッダー分として、1ブロックを追加する */
    lpad(decode(avg_row_len,null,'***',0,'?',
            ((ceil(num_rows/(trunc((ceil
             ((vp.db_block_size - KCBH - UB4 - KTBBH - (ini_trans - 1) * KTBIT - KDBH) * 
             (1 - pct_free / 100) - KDBT))/avg_row_len))))+1) * vp.db_block_size/1024),8) EST_KB,
    lpad(decode(EMPTY_BLOCKS,null,'***',EMPTY_BLOCKS*vp.db_block_size/1024),8) EMPTY_KB
  from 
    (select type_size KCBH from v$type_size where type = 'KCBH') ,
    (select type_size UB4 from v$type_size where type = 'UB4') ,
    (select type_size KTBBH from v$type_size where type = 'KTBBH') ,
    (select type_size KTBIT from v$type_size where type = 'KTBIT') ,
    (select type_size KDBH from v$type_size where type = 'KDBH') ,
    (select type_size KDBT from v$type_size where type = 'KDBT') ,
    (select value db_block_size from sys.v_$parameter where name = 'db_block_size') vp,
    sys.dba_tables   dt,
    sys.dba_segments ds
  where dt.owner like '%'
    and dt.table_name like  '%' 
    and dt.owner not in 
       ('SYS','SYSTEM','WMSYS','CTXSYS','MDSYS','ORDSYS','AURORA$JIS$UTILITY$','OUTLN','OSE$HTTP$ADMIN')
    and ds.segment_type = 'TABLE'
    and ds.owner = dt.owner
    and ds.segment_name = dt.table_name
  union all
  select /*+ ORDERED */
    ds.owner,
    ds.segment_name,
    ds.partition_name,
    ds.segment_type,
    lpad(decode(avg_row_len,null,'***',0,'?',avg_row_len),4) AVG_ROW_LEN,
    lpad(decode(num_rows,null,'***',num_rows),9) NUM_ROWS,
    ceil((vp.db_block_size - KCBH - UB4 - KTBBH - (ini_trans - 1) * KTBIT - KDBH) * 
         (1 - pct_free / 100) - KDBT
        ) FREE_PER_BLOCK,
    lpad(decode(avg_row_len,null,'***',0,'?',
           trunc((ceil((vp.db_block_size - KCBH - UB4 - KTBBH - (ini_trans - 1) * KTBIT - KDBH) * 
           (1 - pct_free / 100) - KDBT))/avg_row_len)),10
        ) ROWS_PER_BLOCK,
    ds.bytes/1024 KB,
    lpad(decode(dtp.BLOCKS,null,'***',(dtp.BLOCKS+1)*vp.db_block_size/1024),8) USED_KB,
    lpad(decode(avg_row_len,null,'***',0,'?',
           ((ceil(num_rows/(trunc((ceil
             ((vp.db_block_size - KCBH - UB4 - KTBBH - (ini_trans - 1) * KTBIT - KDBH) * 
             (1 - pct_free / 100) - KDBT))/avg_row_len))))+1) * vp.db_block_size/1024),8) EST_KB,
    lpad(decode(EMPTY_BLOCKS,null,'***',EMPTY_BLOCKS*vp.db_block_size/1024),8) EMPTY_KB
  from 
    (select type_size KCBH from v$type_size where type = 'KCBH') ,
    (select type_size UB4 from v$type_size where type = 'UB4') ,
    (select type_size KTBBH from v$type_size where type = 'KTBBH') ,
    (select type_size KTBIT from v$type_size where type = 'KTBIT') ,
    (select type_size KDBH from v$type_size where type = 'KDBH') ,
    (select type_size KDBT from v$type_size where type = 'KDBT') ,
    (select value db_block_size from sys.v_$parameter where name = 'db_block_size') vp,
    sys.dba_tab_partitions  dtp,
    sys.dba_segments         ds
  where dtp.table_owner like upper('&TAB_OWNER') 
    and dtp.table_name like  upper('&TAB_NAME') 
    and dtp.table_owner not in 
      ('SYS','SYSTEM','WMSYS','CTXSYS','MDSYS','ORDSYS','AURORA$JIS$UTILITY$','OUTLN','OSE$HTTP$ADMIN')
    and ds.segment_type = 'TABLE PARTITION'
    and ds.owner = dtp.table_owner
    and ds.segment_name = dtp.table_name
    and ds.partition_name = dtp.partition_name
)
order by 
  owner,
  KB,
  segment_name,
  partition_name
