알티베이스 버전 5.3.3 에서 memory tablespace 사용량을 조회 쿼리 몇가지

#memory tablespace 사용량을 조회

select 'MEM TOTAL SIZE' tablespace_name, round(MEM_MAX_DB_SIZE/1024/1024)  as 'max size(M)'   ,

round((MEM_MAX_DB_SIZE - MEM_ALLOC_PAGE_COUNT*32*1024)/1024/1024) as 'free size(MB)',

round(MEM_ALLOC_PAGE_COUNT*32*1024/1024/1024)  as 'alloc size(MB)',

round((MEM_MAX_DB_SIZE - MEM_ALLOC_PAGE_COUNT*32*1024)/MEM_MAX_DB_SIZE*100, 2) as 'free %'

from v$database

union all

select SPACE_NAME tablespace_name, 

case2(space_id =1, 0, round(case2(MAXSIZE=0,CURRENT_SIZE,MAXSIZE)/1024/1024)) as 'total size(MB)',

case2(space_id =1, 0, trunc((case2(MAXSIZE=0,CURRENT_SIZE,MAXSIZE) - CURRENT_SIZE)/1024/1024)) as 'free size(MB)',

round(ALLOC_PAGE_COUNT*32*1024/1024/1024)  as 'alloc size(MB)',

case2(space_id =1, 0, trunc((case2(MAXSIZE=0,CURRENT_SIZE,MAXSIZE) - CURRENT_SIZE)/case2(MAXSIZE=0,CURRENT_SIZE,MAXSIZE)*100,2)) 'free %'

from  v$mem_tablespaces

where SPACE_ID <> 0;

# 메모리 인덱스

SELECT  C.USER_NAME index_owner,

        E.INDEX_NAME index_name,
        case2(E.INDEX_TYPE=1,'B-TREE', E.INDEX_TYPE=2,'R-TREE') INDEX_TYPE
        , '(Memory)'||'16 bytes * ROWCOUNT ' 'allocate(M)'
        , table_name
FROM X$INDEX B,
     SYSTEM_.SYS_USERS_ C,
     V$MEM_TABLESPACES D,
     SYSTEM_.SYS_INDICES_ E,
     SYSTEM_.SYS_TABLES_ F
WHERE B.INDEX_ID = E.INDEX_ID
  AND E.USER_ID = C.USER_ID
  AND F.USER_ID = E.USER_ID
  AND F.TBS_ID = D.SPACE_ID
  AND F.TABLE_OID = B.TABLE_OID
  and c.user_id <> 1;

# 메모리 테이블

SELECT USER_NAME table_owner, TABLE_NAME table_name, ROUND((FIXED_ALLOC_MEM + VAR_ALLOC_MEM)/1024/1024 ) 'allocate(M)', 'MEMORY' as TYPE
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 B.USER_ID <> 1
  AND TABLE_TYPE = 'T';