Page History

...

알티베이스 5.5.1 이상 버전에서 사용할 수 있다.

휘발성(VOLATILE) 테이블스페이스 사용량도 볼 수 있게 되었다. 알티베이스 5.5.1부터 휘발성 메모리 테이블스페이스의 정보를 저장하는 V$VOL_TABLESPACES가 추가되었다.

Code Block

title	알티베이스 5.5.1 이상
language	sql

SELECT ID TBS_ID      ,
DECODE(TYPE, 0, 'MEMORY_DICTIONARY', 1, 'MEMORY_SYS_DATA', 2, 'MEMORY_USER_DATA', 8, 'VOLATILE_USER_DATA') TBS_TYPE ,
    , NAME  TBS_NAME ,
    ,   TO_CHAR(DECODE(M.MAXSIZEMAX/1024/1024, 140737488322560, D.MEM_MAX_DB_SIZE'999,999,999') 'MAX(M)' ,
0 , T.TOTAL_PAGE_COUNT * T.PAGE_SIZE, M.MAXSIZE)   TO_CHAR(TOTAL/1024/1024, '999,999,999') 'MAXTOTAL(M)' ,
    ,   TO_CHAR( M.ALLOC_PAGE_COUNT * T.PAGE_SIZE / 1024 / 1024, '999,999,999') 'TOTALALLOC(M)' ,
    ,   TO_CHAR(NVL(M.ALLOC_PAGE_COUNT-M.FREE_PAGE_COUNT,T.TOTAL_PAGE_COUNT)*PAGE_SIZEUSED/1024/1024, '999,999,999') 'ALLOCUSED(M)' ,
    ,   TO_CHAR(NVL(MT.USEDROUND((USED/1024/1024), 0)/ROUND((TOTAL/1024/1024), 0))*100, '999,999,999') 'USEDUSAGE(M%)' ,
    , TO_CHAR(DECODE(MAXSIZE, 140737488322560, (M.ALLOC_PAGE_COUNT-M.FREE_PAGE_COUNT)*T.PAGE_SIZE/    STATE ,
       AUTOEXTEND
  FROM (SELECT ID TBS_ID ,
               DECODE(TYPE, 0, 'MEMORY_DICTIONARY', 1, 'MEMORY_SYS_DATA', 2, 'MEMORY_USER_DATA', 8, 'VOLATILE_USER_DATA') TBS_TYPE ,
               NAME TBS_NAME ,
               DECODE(M.MAXSIZE, 140737488322560, D.MEM_MAX_DB_SIZE , 0 , (MT.ALLOCTOTAL_PAGE_COUNT-M.FREE * T.PAGE_SIZE, M.MAXSIZE) MAX ,
               M.ALLOC_PAGE_COUNT) /* T.TOTAL_PAGE_COUNTSIZE TOTAL ,
               NVL(M.ALLOC_PAGE_COUNT-M.FREE_PAGE_COUNT), * T.TOTAL_PAGE_SIZE/ M.MAXSIZE) * 100 , '99.99') 'USAGE(%)'
 COUNT)*PAGE_SIZE ALLOC ,
               NVL(MT.USED, 0) USED ,
   ,             DECODE(T.STATE, 1, 'OFFLINE', 2, 'ONLINE', 5, 'OFFLINE BACKUP', 6, 'ONLINE BACKUP', 128, 'DROPPED', 'DISCARDED') STATE ,
    ,            DECODE(M.AUTOEXTEND_MODE, 1, 'ON', 'OFF') 'AUTOEXTEND'
          FROM V$DATABASE D ,
    ,            V$TABLESPACES T ,
    ,            (SELECT SPACE_ID ,
            ,            SPACE_NAME ,
            ,            ALLOC_PAGE_COUNT ,
            ,            FREE_PAGE_COUNT ,
            ,            DECODE(MAX_SIZE, 0, (SELECT VALUE1
                                              FROM V$PROPERTY
                                             WHERE NAME = 'VOLATILE_MAX_DB_SIZE'), MAX_SIZE) AS MAXSIZE ,
            ,            AUTOEXTEND_MODE
                  FROM V$VOL_TABLESPACES
                 UNION ALL
                SELECT SPACE_ID ,
            ,            SPACE_NAME ,
            ,            ALLOC_PAGE_COUNT ,
            ,            FREE_PAGE_COUNT ,
            ,            MAXSIZE ,
            ,            AUTOEXTEND_MODE
                  FROM V$MEM_TABLESPACES ) M LEFT OUTER JOIN(SELECT TABLESPACE_ID, ROUND(SUM((FIXED_USED_MEM + VAR_USED_MEM))/(1024*1024),3) USED
                  FROM V$MEMTBL_INFO
                 GROUP BY TABLESPACE_ID ) MT ON M.SPACE_ID = MT.TABLESPACE_ID
         WHERE T.ID = M.SPACE_ID) ;

알티베이스 5.3.3 용 쿼리이다. 휘발성 메모리 테이블스페이스 정보는 나오지 않는다.

Code Block

title	알티베이스 5.3.3 용 쿼리
language	sql

SELECT ID TBS_ID ,
    , DECODE(TYPE, 0, 'MEMORY_DICTIONARY', 1, 'MEMORY_SYS_DATA', 2, 'MEMORY_USER_DATA', 8, 'VOLATILE_USER_DATA') TBS_TYPE
   TBS_TYPE ,
       TBS_NAME ,
       TO_CHAR(MAX/1024/1024, '999,999,999') 'MAX(M)' ,
       TO_CHAR(TOTAL/1024/1024, '999,999,999') 'TOTAL(M)' ,
    , NAME TBS_NAME
   TO_CHAR(ALLOC/1024/1024, '999,999,999') 'ALLOC(M)' ,
  , DECODE(MAXSIZE, 140737488322560, 'UNDEFINED', 0, ROUND(ALLOCATED_PAGE_COUNT*PAGE_SIZE     TO_CHAR(USED/1024/1024, '999,999,999') 'USED(M)' ,
       TO_CHAR((ROUND((USED/1024/1024), 20), /ROUND(MAXSIZE(TOTAL/1024/1024), 20))*100, 'MAX(M999') 'USAGE(%)' ,
       STATE ,
ROUND(ALLOCATED_PAGE_COUNT * PAGE_SIZE / 1024 / 1024, 2) 'TOTAL(M)'       AUTOEXTEND
  FROM (SELECT ID TBS_ID ,
          , ROUND(NVL(M.ALLOC_PAGE_COUNT-M.FREE_PAGE_COUNT, TOTAL_PAGE_COUNT)*PAGE_SIZE/1024/1024, 2) 'ALLOC(M)'     DECODE(TYPE, 0, 'MEMORY_DICTIONARY', 1, 'MEMORY_SYS_DATA', 2, 'MEMORY_USER_DATA', 8, 'VOLATILE_USER_DATA') TBS_TYPE ,
       , MT.USED 'USED(M)'      NAME TBS_NAME ,
               DECODE(MAXSIZE, 140737488322560, ROUND((M.ALLOC_PAGE_COUNT-M.FREE_PAGE_COUNT)*PAGE_SIZE/ D.MEM_MAX_DB_SIZE*100, 2), 0, ROUND((M.ALLOCALLOCATED_PAGE_COUNT-M.FREE_PAGE_COUNT)*PAGE_SIZE/(, MAXSIZE) MAX ,
               ALLOCATED_PAGE_COUNT * PAGE_SIZE)*100, 2), ROUND(( TOTAL ,
               NVL(M.ALLOC_PAGE_COUNT-M.FREE_PAGE_COUNT, TOTAL_PAGE_COUNT)*PAGE_SIZE/MAXSIZE*100 ALLOC ,
2)) 'USAGE(%)'              MT.USED USED ,
               DECODE(STATE, 1, 'OFFLINE', 2, 'ONLINE', 5, 'OFFLINE BACKUP', 6, 'ONLINE BACKUP', 128, 'DROPPED', 'DISCARDED') STATE ,
    ,            DECODE(AUTOEXTEND_MODE, 1, 'ON', 'OFF') 'AUTOEXTEND'
          FROM V$DATABASE D ,
               V$TABLESPACES T ,
               V$MEM_TABLESPACES M ,
               (SELECT TABLESPACE_ID,
               ROUND(        SUM((FIXED_USED_MEM + VAR_USED_MEM))/(1024*1024), 3) USED
                  FROM V$MEMTBL_INFO
                 GROUP BY TABLESPACE_ID) MT
         WHERE T.ID = M.SPACE_ID
           AND ID = MT.TABLESPACE_ID ) ;

알티베이스 4.3.9 용 쿼리이다.
알티베이스 4 버전에서는 사용자가 메모리 테이블스페이를 생성할 수 있는 기능을 제공하지 않았기 때문에 'SYS_TBS_MEMORY'라는 하나의 테이블스페이스만 나온다.

Code Block

title	알티베이스 4 버전 용 쿼리
language	sql

SELECT 'SYS_TBS_MEMORY' AS TABLESPACE_NAME 
     , ROUND(MEM_MAX_DB_SIZE/1024/1024, 2) AS 'MAX(M)' 
     , ROUND(MEM_ALLOC_PAGE_COUNT * 32 / 1024, 2) AS 'TOTAL(M)' 
     , ROUND((MEM_ALLOC_PAGE_COUNT - MEM_FREE_PAGE_COUNT) * 32 / 1024, 2) AS 'ALLOC(M)' 
     , ROUND(MTBL.USED/1024/1024, 2) AS 'USED(M)' 
     , ROUND(((MEM_ALLOC_PAGE_COUNT - MEM_FREE_PAGE_COUNT) * 32 * 1024 / MEM_MAX_DB_SIZE) * 100, 2) AS 'USAGE(%)'
  FROM V$DATABASE DB ,
       (SELECT SUM(FIXED_USED_MEM+VAR_USED_MEM) AS USED
          FROM V$MEMTBL_INFO ) MTBL ;

...

Versions Compared

Old Version 9

New Version 10

Key