달력

3

« 2024/3 »

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
.. .. ..
메모리 테이블 사용량 확인

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

.
:
Posted by .07274.