altibase 4.3.1 테이블 사용량 확인 쿼리

메모리 테이블 사용량 확인

select
  b.user_name || '.' || a.table_name table_name,
  round((c.fixed_alloc_mem+c.var_alloc_mem)/1024/1024,3) alloc,
  round((c.fixed_used_mem+c.var_used_mem)/1024/1024,3) used,
  round((c.fixed_used_mem+c.var_used_mem)*100/ (c.fixed_alloc_mem+c.var_alloc_mem),2) '%USED'
from
 system_.sys_tables_ a,
 system_.sys_users_ b,
 v$memtbl_info c
where a.user_id = b.user_id
  and a.table_oid = c.table_oid
  --and a.tbs_id=0
  and b.user_name <> 'SYSTEM_'
  and a.table_type='T'
 order by 2 desc;

물리 디스크 각 테이블당 사용량 확인 (version 4만 허용)

SELECT /* for Disk Tables */
             C.NAME TBS_NAME,
             B.USER_NAME||'.'||A.TABLE_NAME TABLE_NAME,
             (C.A_EXTENT_PAGE_COUNT*C.PAGE_SIZE*D.EXTENT_TOTAL_COUNT)/1024/1024 'ALLOC(M)',
             (E.disk_page_cnt*8)/1024 'USED(M)',
             A.TABLE_ID,
             REPLICATION_COUNT REPL_COUNT
FROM    SYSTEM_.SYS_TABLES_ A,
             SYSTEM_.SYS_USERS_ B,
             V$TABLESPACES C,
             V$SEGMENT D,
             V$DISKTBL_INFO E
WHERE  A.USER_ID = B.USER_ID AND
             A.TBS_ID = C.ID AND
             D.TABLE_OID = A.TABLE_OID AND
             D.TABLE_OID = E.TABLE_OID AND
             D.SEGMENT_TYPE='TABLE' AND
             A.TBS_ID != 0 AND A.USER_ID != 1;

물리 디스크 스페이스 사용량 확인

  

select
      rpad(ts.name, 20)             as Phy_tbs_name,
      ts.max                        as Phy_max_size,
      ts.init                       as Phy_alloc_size,
      (ts.init-ts.used)             as Phy_free_size,
      ts.used                       as Phy_used_size,
      trunc(ts.used/ts.init*100,2)  as Phy_used_rate
  from(
      select  ts.name,
           trunc(sum(df.maxsize)*8/1024,0) max,
           trunc(sum(df.initsize)*8/1024,0) init,
           trunc((ts.ALLOCATED_PAGE_COUNT*ts.page_size)/1024/1024,0) used
      from  v$tablespaces TS,
           v$datafiles DF
      where  ts.id = df.spaceid
      group by ts.name, ts.page_size, ts.total_page_count, ts.ALLOCATED_PAGE_COUNT
   ) ts