[Altibase] 모니터링 쿼리 가이드

Altibase

[Altibase] 모니터링 쿼리 가이드

bbugge 2019. 6. 5. 13:13

- Session

+ 전체 세션 개수

SELECT COUNT(*) TOTAL_SESSION_CNT FROM V$SESSION ;

+ 세션 정보

SELECT A.ID SESSION_ID
     , A.DB_USERNAME USER_NAME
     , REPLACE2(REPLACE2(A.COMM_NAME, 'SOCKET-', NULL), '-SERVER', NULL) CLIENT_IP
     , A.CLIENT_APP_INFO             -- 알티베이스 4 버전에서 사용 시 제거
     , A.CLIENT_PID
     , A.SESSION_STATE
     , DECODE(A.AUTOCOMMIT_FLAG, 1, 'ON', 'OFF') AUTOCOMMIT
     , DECODE(A.LOGIN_TIME, 0, '-', TO_CHAR(TO_DATE('1970010109', 'YYYYMMDDHH') + A.LOGIN_TIME / (24*60*60), 'YY/MM/DD HH:MI:SS')) LOGIN_TIME
     , DECODE(A.IDLE_START_TIME, 0, '-', TO_CHAR(TO_DATE('1970010109', 'YYYYMMDDHH') + A.IDLE_START_TIME / (24*60*60), 'YY/MM/DD HH:MI:SS')) IDLE_TIME
     , NVL(LTRIM(B.QUERY), 'NONE') CURRENT_QUERY
  FROM V$SESSION A LEFT OUTER JOIN V$STATEMENT B ON A.CURRENT_STMT_ID = B.ID ;

+ SYSDBA 권한으로 접속 중인 세션 정보

SELECT A.ID SESSION_ID
     , A.DB_USERNAME USER_NAME
     , REPLACE2(REPLACE2(A.COMM_NAME, 'SOCKET-', NULL), '-SERVER', NULL) CLIENT_IP
     , A.CLIENT_APP_INFO              -- 알티베이스 4 버전에서 사용 시 제거
     , A.CLIENT_PID
     , A.SESSION_STATE
     , DECODE(A.AUTOCOMMIT_FLAG, 1, 'ON', 'OFF') AUTOCOMMIT
     , DECODE(A.LOGIN_TIME, 0, '-', TO_CHAR(TO_DATE('1970010109', 'YYYYMMDDHH') + A.LOGIN_TIME / (24*60*60), 'MM/DD HH:MI:SS')) LOGIN_TIME
     , DECODE(A.IDLE_START_TIME, 0, '-', TO_CHAR(TO_DATE('1970010109', 'YYYYMMDDHH') +A.IDLE_START_TIME / (24*60*60), 'MM/DD HH:MI:SS')) IDLE_TIME
     , NVL(LTRIM(B.QUERY), 'NONE') CURRENT_QUERY
  FROM V$SESSION A LEFT OUTER JOIN V$STATEMENT B ON A.CURRENT_STMT_ID = B.ID
WHERE A.SYSDBA_FLAG = 1 ;

- Statement

+ TIMED_STATISTICS 활성화 방법

ALTER SYSTEM SET TIMED_STATISTICS=1;

+ 전체 쿼리 개수

SELECT COUNT(*) AS TOTAL_STMT_CNT FROM V$STATEMENT ;

+ 쿼리 정보

SELECT SESSION_ID
     , ID STMT_ID
     , TX_ID
     , ROUND((PARSE_TIME+VALIDATE_TIME+OPTIMIZE_TIME)/1000000, 1) PREPARE_TIME
     , ROUND(FETCH_TIME/1000000, 1) FETCH_TIME
     , ROUND(EXECUTE_TIME/1000000, 1) EXECUTE_TIME
     , ROUND(TOTAL_TIME/1000000, 1) TOTAL_TIME
     , EXECUTE_FLAG
     , DECODE(LAST_QUERY_START_TIME, 0, '-', TO_CHAR(TO_DATE('1970010109', 'YYYYMMDDHH') + LAST_QUERY_START_TIME / (24*60*60), 'MM/DD HH:MI:SS')) LAST_START_TIME
     , NVL(LTRIM(QUERY), 'NONE') QUERY
  FROM V$STATEMENT
ORDER BY EXECUTE_TIME DESC ;

+ 현재 수행 중인 쿼리 개수

SELECT COUNT(*) AS ACTIVE_STMT_CNT
FROM V$STATEMENT
WHERE EXECUTE_FLAG = 1 ;

+ 장시간으로 수행 쿼리 정보

SELECT SESSION_ID
     , ID STMT_ID
     , TX_ID
     , ROUND((PARSE_TIME+VALIDATE_TIME+OPTIMIZE_TIME)/1000000, 1) PREPARE_TIME
     , ROUND(FETCH_TIME/1000000, 1) FETCH_TIME
     , ROUND(EXECUTE_TIME/1000000, 1) EXECUTE_TIME
     , ROUND(TOTAL_TIME/1000000, 1) TOTAL_TIME
     , DECODE(LAST_QUERY_START_TIME, 0, '-', TO_CHAR(TO_DATE('1970010109', 'YYYYMMDDHH') + LAST_QUERY_START_TIME / (24*60*60), 'MM/DD HH:MI:SS')) LAST_START_TIME
     , NVL(LTRIM(QUERY), 'NONE') QUERY
  FROM V$STATEMENT
WHERE EXECUTE_FLAG = 1
   AND EXECUTE_TIME/1000000 > 600   -- 쿼리 수행 시간 조건을 변경하면 이 조건의 값을 변경하면 된다. 값은 초 단위이다.
ORDER BY EXECUTE_TIME DESC ;

+ 장시간 수행되는 DML트랜잭션의 마지막 쿼리 정보

SELECT ST.SESSION_ID
     , SS.COMM_NAME CLIENT_IP
     , SS.CLIENT_PID
     , SS.CLIENT_APP_INFO
     , (BASE_TIME - TR.FIRST_UPDATE_TIME) AS UTRANS_TIME
     , ROUND(EXECUTE_TIME/1000000, 1) EXECUTE_TIME
     , ROUND(TOTAL_TIME/1000000, 1) TOTAL_TIME
     , DECODE(LAST_QUERY_START_TIME, 0, '-', TO_CHAR(TO_DATE('1970010109', 'YYYYMMDDHH') + LAST_QUERY_START_TIME / (24*60*60), 'MM/DD HH:MI:SS')) LAST_START_TIME
     , NVL(LTRIM(ST.QUERY), 'NONE') QUERY
  FROM V$TRANSACTION TR,
       V$STATEMENT ST,
       V$SESSIONMGR,
       V$SESSION SS
WHERE TR.ID = ST.TX_ID
   AND ST.SESSION_ID = SS.ID
   AND TR.FIRST_UPDATE_TIME != 0  -- 0:read only transaction
   AND (BASE_TIME - TR.FIRST_UPDATE_TIME) > 3600    -- 수행 시간 조건을 변경하고자 할 경우 이 조건의 값을 변경하면 된다. 값은 초 단위이다.
ORDER BY UTRANS_TIME DESC ;

+ 풀 스캔 쿼리 정보

SELECT SESSION_ID
     , S.COMM_NAME CLIENT_IP
     , S.CLIENT_PID
     , S.CLIENT_APP_INFO
     , DECODE(LAST_QUERY_START_TIME, 0, '-', TO_CHAR(TO_DATE('1970010109', 'YYYYMMDDHH') + LAST_QUERY_START_TIME / (24*60*60), 'MM/DD HH:MI:SS')) LAST_START_TIME
     , ROUND((PARSE_TIME+VALIDATE_TIME+OPTIMIZE_TIME)/1000000, 1) PREPARE_TIME
     , ROUND(FETCH_TIME/1000000, 1) FETCH_TIME
     , ROUND(EXECUTE_TIME/1000000, 1) EXECUTE_TIME
     , ROUND(TOTAL_TIME/1000000, 1) TOTAL_TIME
     , NVL(LTRIM(QUERY), 'NONE') QUERY
  FROM V$STATEMENT T,
       V$SESSION S
WHERE S.ID = T.SESSION_ID
   AND (MEM_CURSOR_FULL_SCAN > 0 OR DISK_CURSOR_FULL_SCAN > 0)
   AND UPPER(QUERY) NOT LIKE '%INSERT%'
ORDER BY EXECUTE_TIME DESC ;

+ 풀 스캔 쿼리 수행 횟수 통계

SELECT COUNT(EXECUTE_SUCCESS) EXECUTE_CNT
     , SUBSTR(LTRIM(QUERY), 1, 40) QUERY
  FROM V$STATEMENT
WHERE (MEM_CURSOR_FULL_SCAN > 0 OR DISK_CURSOR_FULL_SCAN > 0)
   AND UPPER(QUERY) NOT LIKE '%INSERT%'
GROUP BY QUERY
ORDER BY EXECUTE_CNT DESC ;

+ 세션 별 쿼리 목록

SELECT SESSION_ID
     , ID STMT_ID
     , TX_ID
     , SUBSTR(QUERY, 1, 100) QUERY
  FROM V$STATEMENT
ORDER BY 1, 2 ;

+ 세션 당 statement 생성 수

SELECT SE.ID SESSION_ID
     , COUNT(DISTINCT ST.ID) CURR_STMT_CNT_PER_SESSION
  FROM V$SESSION SE
     , V$STATEMENT ST
WHERE 1=1
   AND SE.ID=ST.SESSION_ID
GROUP BY SE.ID
ORDER BY SE.ID;

- Service Thread

+ 서비스 스레드 상태

SELECT RUN_MODE
     , STATE
     , COUNT(*) CNT
  FROM V$SERVICE_THREAD
GROUP BY RUN_MODE, STATE ;

+ 서비스 스레드 경합 확인

SELECT NAME,
       MISS_COUNT,
       TRY_COUNT,
       ROUND(MISS_COUNT/TRY_COUNT*100, 2) PER
  FROM V$MUTEX
WHERE NAME = 'MMT_SERVICE_THREAD_MUTEX'
ORDER BY 4 DESC ;

- Transaction & Lock

+ Transaction & lock 정보

SELECT TX.ID TX_ID
     , WAIT_FOR_TRANS_ID BLOCKED_TX_ID
     , DECODE(TX.STATUS,
                 0, 'BEGIN',
                 1, 'PRECOMMIT',
                 2, 'COMMIT_IN_MEMORY',
                 3, 'COMMIT',
                 4, 'ABORT',
                 5, 'BLOCKED',
                 6, 'END') STATUS
     , DECODE(TX.LOG_TYPE, 0, U1.USER_NAME, 'REPLICATION') USER_NAME
     , DECODE(TX.LOG_TYPE, 0, TX.SESSION_ID, RT.REP_NAME) SESSION_ID
     , DECODE(TX.LOG_TYPE, 0, ST.COMM_NAME, RR.PEER_IP) CLIENT_IP
     , DECODE(ST.AUTOCOMMIT_FLAG, 1, 'ON', 'OFF') AUTOCOMMIT
     , L.LOCK_DESC
     , DECODE(TX.FIRST_UPDATE_TIME,
                 0, '0',
                 TO_CHAR(TO_DATE('1970010109', 'YYYYMMDDHH') + TX.FIRST_UPDATE_TIME / (60*60*24), 'MM/DD HH:MI:SS')) FIRST_UPDATE_TIME
     , U2.USER_NAME||'.'||T.TABLE_NAME TABLE_NAME
     , DECODE(TX.LOG_TYPE, 0, SUBSTR(ST.QUERY, 1, 10), 'REMOTE TX_ID '||REMOTE_TID) CURRENT_QUERY
     , DECODE(TX.DDL_FLAG, 0, 'NON-DDL', 'DDL') DDL
     , DECODE(TX.FIRST_UNDO_NEXT_LSN_FILENO, -1, '-', TX.FIRST_UNDO_NEXT_LSN_FILENO) 'LOGFILE#'
  FROM V$TRANSACTION TX,
       V$LOCK L
       LEFT OUTER JOIN (SELECT ST.*, SS.AUTOCOMMIT_FLAG, SS.DB_USERID, SS.COMM_NAME
                          FROM V$STATEMENT ST, V$SESSION SS
                         WHERE SS.ID = ST.SESSION_ID
                           AND SS.CURRENT_STMT_ID = ST.ID) ST ON L.TRANS_ID = ST.TX_ID
       LEFT OUTER JOIN V$REPRECEIVER_TRANSTBL RT ON L.TRANS_ID = RT.LOCAL_TID
       LEFT OUTER JOIN V$REPRECEIVER RR ON RT.REP_NAME = RR.REP_NAME
       LEFT OUTER JOIN V$LOCK_WAIT LW ON L.TRANS_ID = LW.TRANS_ID
       LEFT OUTER JOIN SYSTEM_.SYS_USERS_ U1 ON ST.DB_USERID = U1.USER_ID,
       SYSTEM_.SYS_TABLES_ T
       LEFT OUTER JOIN SYSTEM_.SYS_USERS_ U2 ON T.USER_ID = U2.USER_ID
WHERE TX.ID = L.TRANS_ID
   AND T.TABLE_OID = L.TABLE_OID
   AND TX.STATUS != 6
ORDER BY TX.ID, ST.ID, TX.FIRST_UPDATE_TIME DESC ;

- redo logfile

+ 리두로그 파일 정보

SELECT OLDEST_ACTIVE_LOGFILE OLDEST_LOGFILE
     , CURRENT_LOGFILE CURRENT_LOGFILE
     , CURRENT_LOGFILE-OLDEST_ACTIVE_LOGFILE LOGFILE_GAP
  FROM V$ARCHIVE ;

+ 리두로그 파일 prepare 대기 누적횟수

SELECT LF_PREPARE_WAIT_COUNT FROM V$LFG ;

- GC

+ 메모리 GC gap

SELECT GC_NAME
     , SCNOFTAIL
     , MINMEMSCNINTXS
     , ADD_OID_CNT-GC_OID_CNT GC_GAP
  FROM V$MEMGC ;

+ 메모리 DB GC가 대기하고 있는 트랜잭션에서 수행중인 쿼리

SELECT SESSION_ID
     , TOTAL_TIME
     , EXECUTE_TIME
     , TX_ID
     , QUERY
  FROM V$STATEMENT
WHERE TX_ID IN (SELECT ID
                   FROM V$TRANSACTION
                  WHERE MEMORY_VIEW_SCN = (SELECT MINMEMSCNINTXS FROM V$MEMGC LIMIT 1))
   AND EXECUTE_FLAG = 1
ORDER BY 2 DESC ;

- Memory

+ 알티베이스의 메모리 사용 현황

SELECT NAME
     , ROUND(ALLOC_SIZE/1024/1024) 'ALLOC(M)'
     , ROUND(MAX_TOTAL_SIZE/1024/1024) 'MAX_TOTAL(M)'
  FROM V$MEMSTAT
ORDER BY 3 DESC ;

+ ALTIBASE의 메모리 사용량 합계

SELECT ROUND(SUM(ALLOC_SIZE)/1024/1024) 'ALLOC(M)'
, ROUND(SUM(MAX_TOTAL_SIZE)/1024/1024) 'MAX_TOTAL(M)'
FROM V$MEMSTAT ;

- Tablespace (TBS)

+ 메모리 테이블 스페이스 사용량

// 버전 5.5.1이상. VOLATILE 테이블스페이스 사용량 조회 가능

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.MAXSIZE, 140737488322560, D.MEM_MAX_DB_SIZE , 0 , T.TOTAL_PAGE_COUNT * T.PAGE_SIZE, M.MAXSIZE) /1024/1024, '999,999,999') 'MAX(M)'
     , TO_CHAR( M.ALLOC_PAGE_COUNT * T.PAGE_SIZE / 1024 / 1024, '999,999,999') 'TOTAL(M)'
     , TO_CHAR(NVL(M.ALLOC_PAGE_COUNT-M.FREE_PAGE_COUNT,T.TOTAL_PAGE_COUNT)*PAGE_SIZE/1024/1024, '999,999,999') 'ALLOC(M)'
     , TO_CHAR(NVL(MT.USED, 0), '999,999,999') 'USED(M)'
     , TO_CHAR(DECODE(MAXSIZE, 140737488322560, (M.ALLOC_PAGE_COUNT-M.FREE_PAGE_COUNT)*T.PAGE_SIZE/ D.MEM_MAX_DB_SIZE ,0, (M.ALLOC_PAGE_COUNT-M.FREE_PAGE_COUNT) / T.TOTAL_PAGE_COUNT , (M.ALLOC_PAGE_COUNT-M.FREE_PAGE_COUNT) * T.PAGE_SIZE/ M.MAXSIZE) * 100 , '99.99') 'USAGE(%)'
     , 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
        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 용 쿼리. VOLATILE 테이블스페이스 정보 나오지 않음.

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(MAXSIZE, 140737488322560, 'UNDEFINED', 0, ROUND(ALLOCATED_PAGE_COUNT*PAGE_SIZE/1024/1024, 2), ROUND(MAXSIZE/1024/1024, 2)) 'MAX(M)'
     , ROUND(ALLOCATED_PAGE_COUNT * PAGE_SIZE / 1024 / 1024, 2) 'TOTAL(M)'
     , ROUND(NVL(M.ALLOC_PAGE_COUNT-M.FREE_PAGE_COUNT, TOTAL_PAGE_COUNT)*PAGE_SIZE/1024/1024, 2) 'ALLOC(M)'
     , MT.USED 'USED(M)'
     , DECODE(MAXSIZE, 140737488322560, ROUND((M.ALLOC_PAGE_COUNT-M.FREE_PAGE_COUNT)*PAGE_SIZE/ MEM_MAX_DB_SIZE*100, 2), 0, ROUND((M.ALLOC_PAGE_COUNT-M.FREE_PAGE_COUNT)*PAGE_SIZE/(ALLOCATED_PAGE_COUNT * PAGE_SIZE)*100, 2), ROUND((M.ALLOC_PAGE_COUNT-M.FREE_PAGE_COUNT)*PAGE_SIZE/MAXSIZE*100, 2)) 'USAGE(%)'
     , 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 용 쿼리. 'SYS_TBS_MEMORY' 라는 하나의 테이블 스페이스만 나옴.

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 ;

+ 전체 메모리 테이블스페이스 사용량

SELECT MEM_MAX_DB_SIZE/1024/1024 'MAX(M)'
    , ROUND(MEM_ALLOC_PAGE_COUNT*32/1024, 2) 'TOTAL(M)'
    , TRUNC((MEM_ALLOC_PAGE_COUNT-MEM_FREE_PAGE_COUNT)*32/1024, 2) 'ALLOC(M)'
    , (SELECT ROUND(SUM((FIXED_USED_MEM + VAR_USED_MEM))/(1024*1024), 3)
         FROM V$MEMTBL_INFO) 'USED(M)'
    , TRUNC(((MEM_ALLOC_PAGE_COUNT-MEM_FREE_PAGE_COUNT)*32*1024)/MEM_MAX_DB_SIZE, 4)*100 'USAGE(%)'
FROM V$DATABASE ;

+ 디스크 테이블 스페이스 사용량

// 버전 5.5.1 이상. 언두 테이블스페이스의 사용량도 확인 가능.

SELECT T.ID TBS_ID
     , DECODE(TYPE, 3, 'SYSTEM_DISK_DATA', 4, 'DISK_USER_DATA', 5, 'SYSTEM_DISK_TEMP', 6, 'USER_DISK_TEMP', 7, 'SYSTEM_DISK_UNDO') TBS_TYPE
     , NAME TBS_NAME
     , TO_CHAR((D.MAX * PAGE_SIZE / 1024 /1024), '999,999,999') 'MAX(M)'
     , TO_CHAR((TOTAL_PAGE_COUNT * PAGE_SIZE)/1024/1024, '999,999,999') 'TOTAL(M)'
     , DECODE(TYPE, 7, TO_CHAR((U.TOTAL_EXT_CNT*PROP.EXTENT_SIZE)/1024/1024, '999,999,999')
                     , TO_CHAR((ALLOCATED_PAGE_COUNT * PAGE_SIZE)/1024/1024, '999,999,999')) 'ALLOC(M)'
     , DECODE(TYPE, 3, TO_CHAR(NVL(DS.USED, 0)/1024/1024, '999,999,999'),
                    4, TO_CHAR(NVL(DS.USED, 0)/1024/1024, '999,999,999'),
                    7, TO_CHAR(((U.TX_EXT_CNT+U.USED_EXT_CNT+U.UNSTEALABLE_EXT_CNT) * PROP.EXTENT_SIZE)/1024/1024, '999,999,999')
                     , LPAD('-', 12))'USED(M)'
     , DECODE(TYPE, 7, TO_CHAR((((U.TX_EXT_CNT+U.USED_EXT_CNT+U.UNSTEALABLE_EXT_CNT) * PROP.EXTENT_SIZE)/(D.MAX*PAGE_SIZE))*100, '999.99'),
                    3, TO_CHAR(NVL(DS.USED, 0)/(D.MAX*PAGE_SIZE)* 100, '99.99'),
                    4, TO_CHAR(NVL(DS.USED, 0)/(D.MAX*PAGE_SIZE)* 100, '99.99')
                     , TO_CHAR((ALLOCATED_PAGE_COUNT/D.MAX) * 100, '99.99')) 'USAGE(%)'
     , DECODE(STATE, 1, 'OFFLINE', 2, 'ONLINE', 5, 'OFFLINE BACKUP', 6, 'ONLINE BACKUP', 128, 'DROPPED', 'DISCARDED') STATE
     , D.AUTOEXTEND
  FROM V$TABLESPACES T LEFT OUTER JOIN(SELECT SPACE_ID , SUM(TOTAL_USED_SIZE) USED
                                         FROM X$SEGMENT
                                        GROUP BY SPACE_ID) DS ON DS.SPACE_ID = T.ID
     , (SELECT SPACEID
             , SUM(DECODE(MAXSIZE, 0, CURRSIZE, MAXSIZE)) AS MAX
             , DECODE(MAX(AUTOEXTEND), 1, 'ON', 'OFF') 'AUTOEXTEND'
          FROM V$DATAFILES
         GROUP BY SPACEID ) D
     , V$DISK_UNDO_USAGE U
     , (SELECT VALUE1 EXTENT_SIZE
          FROM V$PROPERTY
         WHERE NAME = 'SYS_UNDO_TBS_EXTENT_SIZE') PROP
WHERE T.ID = D.SPACEID ;

// 버전 5.3.3, 5.3.5 용 쿼리. 언두 테이블스페이스와 임시 테이블스페이스의 실 사용량(USED)을 구할 수 없음.

SELECT NAME TBS_NAME
     , TO_CHAR(D.MAX * PAGE_SIZE / 1024 /1024, '999,999,999') 'MAX(M)'
     , TO_CHAR(TOTAL_PAGE_COUNT*PAGE_SIZE/1024/1024, '999,999,999') 'TOTAL(M)'
     , DECODE(TYPE, 7, TO_CHAR((SELECT (SUM(TOTAL_EXTENT_COUNT*PAGE_COUNT_IN_EXTENT)*PAGE_SIZE)/1024/1024
                          FROM V$UDSEGS)+ (SELECT (SUM(TOTAL_EXTENT_COUNT*PAGE_COUNT_IN_EXTENT)*PAGE_SIZE)/1024/1024
                          FROM V$TSSEGS), '999,999,999') , /* UNDO */
                       TO_CHAR((ALLOCATED_PAGE_COUNT*PAGE_SIZE)/1024/1024, '999,999,999')) 'ALLOC(M)'
     , DECODE(TYPE, 3, TO_CHAR(NVL(DS.USED, 0)/1024/1024, '999,999,999'),
                    4, TO_CHAR(NVL(DS.USED, 0)/1024/1024, '999,999,999') /* SYS_TEMP */
                     , LPAD('-', 12)) 'USED(M)'
     , DECODE(TYPE, 7, TO_CHAR(((SELECT SUM(TOTAL_EXTENT_COUNT*PAGE_COUNT_IN_EXTENT)
                                  FROM V$UDSEGS)+ (SELECT SUM(TOTAL_EXTENT_COUNT*PAGE_COUNT_IN_EXTENT)
                                  FROM V$TSSEGS)) / D.MAX* 100,  '999.99') ,          /* UNDO */
                    3, TO_CHAR(NVL(DS.USED, 0)/(D.MAX*PAGE_SIZE)* 100,  '999.99') ,
                    4, TO_CHAR(NVL(DS.USED, 0)/(D.MAX*PAGE_SIZE)* 100,  '999.99') ,   /* TEMP */
                       TO_CHAR(ALLOCATED_PAGE_COUNT / D.MAX * 100,  '99.99') ) 'USAGE(%)'    -- USAGE(%) : MAX대비 USED. TEMP, UNDO 의 경우 MAX대비 ALLOC
     , DECODE(STATE, 1, 'OFFLINE', 2, 'ONLINE', 5, 'OFFLINE BACKUP', 6, 'ONLINE BACKUP', 128, 'DROPPED', 'DISCARDED') STATE
     , D.AUTOEXTEND
  FROM V$TABLESPACES T LEFT OUTER JOIN (SELECT SPACE_ID , SUM(TOTAL_USED_SIZE) USED
                                          FROM X$SEGMENT
                                         GROUP BY SPACE_ID ) DS ON DS.SPACE_ID = T.ID
     ,(SELECT SPACEID
             , SUM(DECODE(MAXSIZE, 0, CURRSIZE, MAXSIZE)) AS MAX
             , DECODE(MAX(AUTOEXTEND), 1, 'ON', 'OFF') 'AUTOEXTEND'
          FROM V$DATAFILES
         GROUP BY SPACEID ) D
WHERE T.ID = D.SPACEID
;

// 버전 5.1.5 용 쿼리. 디스크 테이블의 구조 변경으로 디스크 테이블스페이스의 실 사용량(USED)을 구할 수 없고 할당 크기(ALLOC)만 알 수 있음. 언두 테이블스페이스와 임시 테이블스페이스의 실 사용량 구할 수 없음.

SELECT NAME TBS_NAME
     , TO_CHAR(ROUND(D.MAX * PAGE_SIZE / 1024 /1024, 2)) 'MAX(M)'
     , ROUND(TOTAL_PAGE_COUNT * PAGE_SIZE / 1024 / 1024, 2) 'TOTAL(M)'
     , DECODE(TYPE, 7, ROUND((SELECT (SUM(total_page_count) * PAGE_SIZE)/1024/1024
                                FROM V$undo_seg)+
                             (SELECT (SUM(ALLOC_PAGE_COUNT) * PAGE_SIZE)/1024/1024
                                FROM v$tss_seg), 2)
                     , ROUND(ALLOCATED_PAGE_COUNT * PAGE_SIZE / 1024 / 1024, 2)) 'ALLOC(M)'
     , DECODE(TYPE, 7, ROUND( ( (SELECT SUM(total_page_count) FROM V$undo_seg) +
                                (SELECT SUM(ALLOC_PAGE_COUNT) FROM v$tss_seg ) ) / D.MAX  * 100, 2)
                     , ROUND(ALLOCATED_PAGE_COUNT / D.MAX * 100, 2))             'USAGE(%)'
      , DECODE(STATE, 1, 'OFFLINE', 2, 'ONLINE', 5, 'OFFLINE BACKUP', 6, 'ONLINE BACKUP', 128, 'DROPPED', 'DISCARDED') STATE
      , D.AUTOEXTEND
  FROM V$TABLESPACES T
       ,(SELECT  SPACEID
              , SUM(DECODE(MAXSIZE, 0, CURRSIZE, MAXSIZE)) AS MAX
              , DECODE(MAX(AUTOEXTEND), 1, 'ON', 'OFF') 'AUTOEXTEND'
           FROM V$DATAFILES
           GROUP BY SPACEID) D
WHERE T.ID = D.SPACEID
;

// 버전 4 용 쿼리

SELECT TBS.NAME TBS_NAME
     , TO_CHAR(ROUND(DAT.MAX * TBS.PAGE_SIZE / 1024 /1024, 2)) 'MAX(M)'
     , ROUND(TBS.TOTAL_PAGE_COUNT * TBS.PAGE_SIZE / 1024 / 1024, 2) 'TOTAL(M)'
     , DECODE(TBS.TYPE, 5, ROUND( UNDO.ALLOC * TBS.PAGE_SIZE/1024/1024, 2) /* UNDO TABLESPACE*/
                         , ROUND( TBS.ALLOCATED_PAGE_COUNT * TBS.PAGE_SIZE / 1024 / 1024, 2) ) 'ALLOC(M)'
     , DECODE(TBS.TYPE, 3, '-' /* TEMP TABLESPACE */
                      , 5, ROUND( UNDO.USED * TBS.PAGE_SIZE /1024/1024, 2) /* UNDO TABLESPACE*/
                         , DECODE(SEG.USED, '', 0, ROUND((SEG.USED * TBS.PAGE_SIZE * TBS.A_EXTENT_PAGE_COUNT)/1024/1024, 2)) /* USER TABLESPACE & SYS_TBS_DATA */) 'USED(M)'
     , DECODE(TBS.TYPE, 5, ROUND( UNDO.ALLOC / DAT.MAX * 100, 2) --UNDO
                         , ROUND( TBS.ALLOCATED_PAGE_COUNT / DAT.MAX * 100, 2) ) 'USAGE(%)'
     , DECODE(TBS.STATE, 1, 'ONLINE', 2, 'BEGIN BACKUP', 3, 'END BACKUP', 'NOT DEFINED') STATE
     , DAT.AUTOEXTEND
  FROM V$TABLESPACES TBS LEFT OUTER JOIN (SELECT SPACE_ID , SUM(EXTENT_TOTAL_COUNT) ALLOC , SUM(EXTENT_FULL_COUNT ) USED
                                            FROM X$SEGMENT
                                            GROUP BY SPACE_ID
                                         ) SEG ON TBS.ID = SEG.SPACE_ID
     ,(SELECT SPACEID
             , SUM(DECODE(MAXSIZE, 0, CURRSIZE, MAXSIZE)) AS MAX
             , DECODE(MAX(AUTOEXTEND), 1, 'ON', 'OFF') 'AUTOEXTEND'
          FROM V$DATAFILES
         GROUP BY SPACEID ) DAT
     , (SELECT SUM(ALLOCATED_PAGE_COUNT) ALLOC
             , SUM(USED_PAGE_COUNT) USED
          FROM V$UNDO_TBS ) UNDO
WHERE TBS.ID = DAT.SPACEID;

+ 디스크 언두 테이블스페이스 사용량

SELECT T.NAME TBS_NAME
     , ROUND(D.MAX * PAGE_SIZE / 1024 /1024, 2) 'MAX(M)'
     , ROUND((TOTAL_PAGE_COUNT * PAGE_SIZE) / 1024 / 1024, 2) 'TOTAL(M)'
     , ROUND((U.TOTAL_EXT_CNT*PROP.EXTENT_SIZE)/1024/1024, 2) 'ALLOC(M)'
     , ROUND(((U.TX_EXT_CNT+U.USED_EXT_CNT+U.UNSTEALABLE_EXT_CNT) * PROP.EXTENT_SIZE)/1024/1024, 2) 'USED(M)'
     , ROUND((((U.TX_EXT_CNT+U.USED_EXT_CNT+U.UNSTEALABLE_EXT_CNT) * PROP.EXTENT_SIZE)/(D.MAX*PAGE_SIZE))*100, 2) 'USAGE(%)'
     , DECODE(STATE,1,'OFFLINE',2,'ONLINE',5,'OFFLINE BACKUP',6,'ONLINE BACKUP',128,'DROPPED', 'DISCARDED') STATE
     , D.AUTOEXTEND
  FROM V$TABLESPACES T
     , (SELECT SPACEID
             , SUM(DECODE(MAXSIZE, 0, CURRSIZE, MAXSIZE)) AS MAX
             , DECODE(MAX(AUTOEXTEND),1,'ON','OFF') 'AUTOEXTEND'
          FROM V$DATAFILES
         GROUP BY SPACEID) D
     , V$DISK_UNDO_USAGE U
     , (SELECT VALUE1 EXTENT_SIZE FROM V$PROPERTY WHERE NAME = 'SYS_UNDO_TBS_EXTENT_SIZE') PROP
WHERE T.ID = D.SPACEID
   AND T.ID = 3 ;

+ 트랜잭션 별 언두 테이블스페이스 사용량

SELECT  DECODE(TX.SESSION_ID, -1, 'REP('||REP.REP_NAME||')', TX.SESSION_ID) SESSION_ID
      , TX.ID TX_ID
      , DECODE(TX.STATUS, 0, 'BEGIN', 1, 'PRECOMMIT', 2, 'COMMIT_IN_MEMORY', 3, 'COMMIT', 4, 'ABORT', 5, 'BLOCKED', 6, 'END') TX_STATUS
      , RPAD(DECODE(ST.EXECUTE_FLAG, NULL, 'REP('||REP.REP_NAME||')', 1, 'SQL ING', 0, 'SQL END'), 10) SQL_STATUS
      , RPAD(DECODE(TX.LOG_TYPE, 1, 'REP '||REP.PEER_IP||':'||REP.PEER_PORT, S.COMM_NAME||' PID:'||S.CLIENT_PID), 40) CLIENT_IP
      , RPAD(DECODE(TX.LOG_TYPE, 1, 'REP('||REP.REP_NAME||')', S.CLIENT_APP_INFO), 15) CLIENT_APP_INFO
      , RPAD(DECODE(S.AUTOCOMMIT_FLAG, 1, 'ON', 0, 'OFF', NULL, 'REP('||REP.REP_NAME||')'), 10) AUTOCOMMIT
      , RPAD(DECODE(TX.LOG_TYPE, 1, 'REP('||REP.REP_NAME||')', S.UTRANS_TIME_LIMIT), 15) UTRANS_TIMEOUT
      , DECODE(ST.LAST_QUERY_START_TIME, NULL, TO_CHAR(TO_DATE('1970010109', 'YYYYMMDDHH') + TX.FIRST_UPDATE_TIME / (60*60*24), 'YYYY-MM-DD HH:MI:SS'), TO_CHAR(TO_DATE('1970010109', 'YYYYMMDDHH') + ST.LAST_QUERY_START_TIME / (60*60*24), 'YYYY-MM-DD HH:MI:SS')) LAST_QUERY_START_TIME
      , ((((TSS.TOTAL_EXTENT_COUNT+TSS.TOTAL_EXTDIR_COUNT)*TSS.PAGE_COUNT_IN_EXTENT)+((UDS.TOTAL_EXTENT_COUNT+UDS.TOTAL_EXTDIR_COUNT)*UDS.PAGE_COUNT_IN_EXTENT))*TBS.PAGE_SIZE) /1024/1024 UNDO_USAGE
      , DECODE(TX.LOG_TYPE, 1, 'REMOTE_TX_ID : '||REP_TX.REMOTE_TID, SUBSTR(ST.QUERY, 1, 30)) QUERY
  FROM  V$TXSEGS TXS
      , V$TSSEGS TSS
      , V$UDSEGS UDS
      , V$TRANSACTION TX LEFT OUTER JOIN V$SESSION S ON TX.ID = S.TRANS_ID LEFT OUTER JOIN V$STATEMENT ST ON S.CURRENT_STMT_ID = ST.ID LEFT OUTER JOIN V$REPRECEIVER_TRANSTBL REP_TX ON TX.ID = REP_TX.LOCAL_TID LEFT OUTER JOIN V$REPRECEIVER REP ON REP_TX.REP_NAME = REP.REP_NAME
      , V$TABLESPACES TBS
WHERE 1=1
   AND UDS.SPACE_ID = 3
   AND TXS.ID = UDS.TXSEG_ENTRY_ID
   AND TXS.ID = TSS.TXSEG_ENTRY_ID
   AND TXS.TRANS_ID = TX.ID
   AND TBS.ID = UDS.SPACE_ID
;

+ 메모리 테이블스페이스 데이터 파일 체크포인트 경로

SELECT M.SPACE_ID TBS_ID,
      SPACE_NAME TBS_NAME,
      CHECKPOINT_PATH
FROM V$MEM_TABLESPACES M,
      V$MEM_TABLESPACE_CHECKPOINT_PATHS C
WHERE M.SPACE_ID = C.SPACE_ID ;

+ 메모리 테이블스페이스 데이터 파일

SELECT MEM_DATA_FILE DATAFILE_NAME
FROM V$STABLE_MEM_DATAFILES ;

+ 디스크 테이블스페이스 데이터 파일

SELECT B.NAME TBS_NAME,
       A.ID 'FILE#',
       A.NAME DATAFILE_NAME,
       CURRSIZE*8/1024 'ALLOC(M)',
       ROUND(CASE2(A.MAXSIZE=0, CURRSIZE, A.MAXSIZE)*8/1024) 'MAX(M)',
       DECODE(AUTOEXTEND, 0, 'OFF', 'ON') 'AUTOEXTEND'
  FROM V$DATAFILES A,
       V$TABLESPACES B
WHERE B.ID = A.SPACEID
ORDER BY B.NAME, A.ID
;

+ 디스크 테이블스페이스 데이터 파일 별 단일 페이지 Read I/O

SELECT B.NAME TBS_NAME,
       A.SINGLEBLKRDS READ_CNT_PER_PAGE,
       A.SINGLEBLKRDTIM READ_TIME_PER_PAGE,
       TRUNC(A.SINGLEBLKRDTIM/A.SINGLEBLKRDS,2) AVERAGE_TIME
  FROM V$FILESTAT A,
       V$DATAFILES B
WHERE A.SPACEID = B.SPACEID
       AND A.FILEID = B.ID
       AND A.SINGLEBLKRDS > 0
ORDER BY AVERAGE_TIME DESC
;

+ 임시 테이블스페이스 사용량

SELECT SUM(NORMAL_AREA_SIZE) SUM_NORMAL_AREA_SIZE
FROM X$TEMPTABLE_STATS
WHERE DROP_TIME = '19700101_090000' ;

+ 트랜잭션 별 임시 테이블스페이스 사용량

SELECT NORMAL_AREA_SIZE
FROM X$TEMPTABLE_STATS T,
V$STATEMENT STMT
WHERE T.TRANSACTION_ID = STMT.TX_ID ;

+ 전체 테이블스페이스 상태

SELECT NAME TBS_NAME,
      DECODE(STATE, 1, 'OFFLINE',
                    2, 'ONLINE',
                    5, 'OFFLINE BACKUP',
                    6, 'ONLINE BACKUP',
                    128, 'DROPPED', 'DISCARD') STATE
FROM V$TABLESPACES ;

- Disk Buffer

+ 디스크 버퍼 Hit Ratio

SELECT HIT_RATIO 'HIT_RATIO(%)' FROM V$BUFFPOOL_STAT ;

- Object

+ 메모리 테이블 사용량

// 버전 6.5.1 이상.

SELECT MT.USER_NAME
     , MT.TABLE_NAME
     , MT.PARTITION_NAME
     , TBS.NAME TBS_NAME
     , TO_CHAR((MU.FIXED_ALLOC_MEM+MU.VAR_ALLOC_MEM)/1024/1024, '999,999,999.9') 'ALLOC(M)'
     , TO_CHAR((MU.FIXED_USED_MEM+MU.VAR_USED_MEM)/1024/1024,   '999,999,999.9') 'USED(M)'
     , TO_CHAR(((MU.FIXED_USED_MEM+MU.VAR_USED_MEM)/(MU.FIXED_ALLOC_MEM+MU.VAR_ALLOC_MEM))*100, '999.9') 'USAGE(%)'
  FROM (SELECT U.USER_NAME
             , T.TABLE_NAME
             , DECODE(T.IS_PARTITIONED, 'F', T.TABLE_OID, 'T', P.PARTITION_OID) TABLE_OID
             , DECODE(T.IS_PARTITIONED, 'F', RPAD('-', 14), 'T', P.PARTITION_NAME) PARTITION_NAME
             , DECODE(T.IS_PARTITIONED, 'F', T.TBS_ID, 'T', P.TBS_ID) TBS_ID
          FROM SYSTEM_.SYS_USERS_ U
             , SYSTEM_.SYS_TABLES_ T LEFT OUTER JOIN SYSTEM_.SYS_TABLE_PARTITIONS_ P ON T.TABLE_ID = P.TABLE_ID
         WHERE 1=1
           AND U.USER_ID <> 1
           AND U.USER_ID = T.USER_ID
           AND T.TBS_ID IN (SELECT ID FROM V$TABLESPACES WHERE TYPE IN (1, 2, 8))
       ) MT
     , V$MEMTBL_INFO MU
     , V$TABLESPACES TBS
WHERE 1=1
   AND MT.TABLE_OID = MU.TABLE_OID
   AND TBS.ID = MT.TBS_ID
ORDER BY 1, 2, 3
   ;

// 버전 4 이상.

SELECT A.USER_NAME
     , B.TABLE_NAME
     , D.NAME TBS_NAME
     , TO_CHAR((C.FIXED_ALLOC_MEM+C.VAR_ALLOC_MEM)/1024/1024, '999,999,999.9') 'ALLOC(M)'
     , TO_CHAR((C.FIXED_USED_MEM+C.VAR_USED_MEM)/1024/1024,   '999,999,999.9') 'USED(M)'
     , TO_CHAR(((C.FIXED_USED_MEM+C.VAR_USED_MEM)/(C.FIXED_ALLOC_MEM+C.VAR_ALLOC_MEM))*100, '999.9') 'USAGE(%)'
  FROM SYSTEM_.SYS_USERS_ A
     , SYSTEM_.SYS_TABLES_ B
     , V$MEMTBL_INFO C
     , V$TABLESPACES D
WHERE 1=1
   AND A.USER_NAME <> 'SYSTEM_'
   AND B.TABLE_TYPE = 'T'
   AND A.USER_ID = B.USER_ID
   AND B.TABLE_OID = C.TABLE_OID
   AND B.TBS_ID = D.ID
ORDER BY USER_NAME, TBS_NAME, TABLE_NAME
;

+ 큐

SELECT A.USER_NAME
     , B.TABLE_NAME
     , D.NAME TABLESPACE_NAME
     , C.TABLE_OID
     , TO_CHAR((C.FIXED_ALLOC_MEM+C.VAR_ALLOC_MEM)/1024/1024, '999,999,999.9') 'ALLOC(M)'
     , TO_CHAR((C.FIXED_USED_MEM+C.VAR_USED_MEM)/1024/1024,   '999,999,999.9') 'USED(M)'
     , TO_CHAR(((C.FIXED_USED_MEM+C.VAR_USED_MEM)/(C.FIXED_ALLOC_MEM+C.VAR_ALLOC_MEM))*100, '999.9') 'USAGE(%)'
  FROM SYSTEM_.SYS_USERS_ A
     , SYSTEM_.SYS_TABLES_ B
     , V$MEMTBL_INFO C
     , V$TABLESPACES D
WHERE 1=1
   AND A.USER_NAME <> 'SYSTEM_'
   AND B.TABLE_TYPE = 'Q'
   AND A.USER_ID = B.USER_ID
   AND B.TABLE_OID = C.TABLE_OID
   AND B.TBS_ID = D.ID ;

+ Efficiency 가 낮은 메모리 테이블

// 할당 받은 전체 페이지에 비해 실제 사용이 낮은 테이블을 조회하는 쿼리.

// 버전 6.5.1 이상.

SELECT MT.USER_NAME
     , MT.TABLE_NAME
     , MT.PARTITION_NAME
     , TBS.NAME TBS_NAME
     , TO_CHAR((MU.FIXED_ALLOC_MEM+MU.VAR_ALLOC_MEM)/1024/1024, '999,999,999.9') 'ALLOC(M)'
     , TO_CHAR((MU.FIXED_USED_MEM+MU.VAR_USED_MEM)/1024/1024,   '999,999,999.9') 'USED(M)'
     , TO_CHAR(((MU.FIXED_USED_MEM+MU.VAR_USED_MEM)/(MU.FIXED_ALLOC_MEM+MU.VAR_ALLOC_MEM))*100, '999.9') 'USAGE(%)'
  FROM (SELECT U.USER_NAME
             , T.TABLE_NAME
             , DECODE(T.IS_PARTITIONED, 'F', T.TABLE_OID, 'T', P.PARTITION_OID) TABLE_OID
             , DECODE(T.IS_PARTITIONED, 'F', RPAD('-', 14), 'T', P.PARTITION_NAME) PARTITION_NAME
             , DECODE(T.IS_PARTITIONED, 'F', T.TBS_ID, 'T', P.TBS_ID) TBS_ID
          FROM SYSTEM_.SYS_USERS_ U
             , SYSTEM_.SYS_TABLES_ T LEFT OUTER JOIN SYSTEM_.SYS_TABLE_PARTITIONS_ P ON T.TABLE_ID = P.TABLE_ID
         WHERE 1=1
           AND U.USER_ID <> 1
           AND U.USER_ID = T.USER_ID
           AND T.TBS_ID IN (SELECT ID FROM V$TABLESPACES WHERE TYPE IN (1, 2, 8))
       ) MT
     , V$MEMTBL_INFO MU
     , V$TABLESPACES TBS
WHERE 1=1
   AND MT.TABLE_OID = MU.TABLE_OID
   AND TBS.ID = MT.TBS_ID
   AND ROUND((FIXED_ALLOC_MEM+VAR_ALLOC_MEM)/1024/1024, 2) >= 1024                                -- 메모리 테이블에 할당된 페이지가 1G 이상
   AND ROUND((FIXED_USED_MEM+VAR_USED_MEM)/(FIXED_ALLOC_MEM+VAR_ALLOC_MEM+0.01)*100, 2) <= 50     -- USAGE가 50% 이하
ORDER BY 1, 2, 3
   ;

// 버전 4 이상.

SELECT C.USER_NAME
     , B.TABLE_NAME
     , TO_CHAR((C.FIXED_ALLOC_MEM+C.VAR_ALLOC_MEM)/1024/1024, '999,999,999.9') 'ALLOC(M)'
     , TO_CHAR((C.FIXED_USED_MEM+C.VAR_USED_MEM)/1024/1024,   '999,999,999.9') 'USED(M)'
     , TO_CHAR(((C.FIXED_USED_MEM+C.VAR_USED_MEM)/(C.FIXED_ALLOC_MEM+C.VAR_ALLOC_MEM))*100, '999.9') 'USAGE(%)'
  FROM V$MEMTBL_INFO A ,
       SYSTEM_.SYS_TABLES_ B,
       SYSTEM_.SYS_USERS_ C
WHERE A.TABLE_OID = B.TABLE_OID
   AND B.USER_ID = C.USER_ID
   AND ROUND((FIXED_ALLOC_MEM+VAR_ALLOC_MEM)/1024/1024, 2) >= 1024                                -- 메모리 테이블에 할당된 페이지가 1G 이상
   AND ROUND((FIXED_USED_MEM+VAR_USED_MEM)/(FIXED_ALLOC_MEM+VAR_ALLOC_MEM+0.01)*100, 2) <= 50     -- USAGE가 50% 이하
   AND B.USER_ID <> 1
ORDER BY 'FREE(M)' DESC ;

+ 메모리 인덱스, 큐 인덱스 사용량

SELECT U.USER_NAME, T.TABLE_NAME TABLE_NAME
     , B.INDEX_NAME
     , LPAD(I.IS_PARTITIONED, 14) INDEX_PARTITIONED
     , ROUND(((USED_NODE_COUNT + PREPARE_NODE_COUNT) / 15 * 32768)/1024/1024, 1) AS 'SIZE(MB)'
  FROM V$MEM_BTREE_HEADER B
     , SYSTEM_.SYS_INDICES_ I
     , SYSTEM_.SYS_TABLES_ T
     , SYSTEM_.SYS_USERS_ U
WHERE 1=1
   AND B.INDEX_ID = I.INDEX_ID
   AND I.TABLE_ID = T.TABLE_ID
   AND B.INDEX_TBS_ID <> 0
   AND U.USER_ID = T.USER_ID
ORDER BY TABLE_NAME, B.INDEX_ID
;

+ 디스크 테이블

// 디스크 테이블의 사용량 정보.

// 버전 5.3.3 이상.

SELECT U.USER_NAME USER_NAME
     , TBL.TABLE_NAME TABLE_NAME
     , DECODE(TBL.IS_PARTITIONED, 'T', TBL.PARTITION_NAME, 'F', '-') PARTITIONED_TABLE
     , TBS.NAME TBS_NAME
     , TO_CHAR((D.MAX * TBS.PAGE_SIZE)/1024, '999,999,999,999') 'MAX(KB)'
     , TO_CHAR((TBS.EXTENT_PAGE_COUNT * TBS.PAGE_SIZE * SEG.TOTAL_EXTENT_COUNT)/1024, '999,999,999,999') 'ALLOC(KB)'
     , TO_CHAR(SEG.TOTAL_USED_SIZE/1024, '999,999,999,999') 'USED(KB)'
  FROM (SELECT TBL.USER_ID
             , DECODE(TBL.IS_PARTITIONED, 'F', TBL.TABLE_OID, 'T', PT.PARTITION_OID) TABLE_OID
             , TBL.TABLE_NAME
             , PT.PARTITION_NAME
             , DECODE(TBL.IS_PARTITIONED, 'F', TBL.TBS_ID, 'T', PT.TBS_ID) TBS_ID
             , TBL.IS_PARTITIONED
          FROM SYSTEM_.SYS_TABLES_ TBL LEFT OUTER JOIN SYSTEM_.SYS_TABLE_PARTITIONS_ PT ON TBL.TABLE_ID = PT.TABLE_ID
         WHERE TBL.TABLE_TYPE = 'T'
       ) TBL
     , (SELECT S.TABLE_OID, SUM(S.TOTAL_EXTENT_COUNT) TOTAL_EXTENT_COUNT, SUM(S.TOTAL_USED_SIZE) TOTAL_USED_SIZE
          FROM X$SEGMENT S
         WHERE S.SEGMENT_TYPE IN (6, 7) /* 6 : 테이블, 7 : LOB 데이터(6.1.1 이상), 5 : 인덱스 */
         GROUP BY S.TABLE_OID) SEG
     , SYSTEM_.SYS_USERS_ U
     , V$TABLESPACES TBS
     , (SELECT SPACEID
             , SUM(DECODE(MAXSIZE, 0, CURRSIZE, MAXSIZE)) AS MAX
             , DECODE(MAX(AUTOEXTEND),1,'ON','OFF') 'AUTOEXTEND'
          FROM V$DATAFILES
         GROUP BY SPACEID) D
WHERE 1=1
   AND SEG.TABLE_OID = TBL.TABLE_OID
   AND U.USER_ID = TBL.USER_ID
   AND D.SPACEID = TBL.TBS_ID
   AND TBS.ID = TBL.TBS_ID
ORDER BY USER_NAME, TABLE_NAME, PARTITIONED_TABLE
;

// 버전 5.1.5

SELECT U.USER_NAME USER_NAME
     , TBL.TABLE_NAME TABLE_NAME
     , DECODE(TBL.IS_PARTITIONED, 'T', TBL.PARTITION_NAME, 'F', '-') PARTITIONED_TABLE
     , TBS.NAME TBS_NAME
     , TO_CHAR((D.MAX * TBS.PAGE_SIZE)/1024/1024, '999,999,999') 'MAX(MB)'
     , TO_CHAR((TBS.EXTENT_PAGE_COUNT * TBS.PAGE_SIZE * SEG.EXTENT_TOTAL_COUNT)/1024/1024, '999,999,999') 'ALLOC(MB)'
  FROM (SELECT TBL.USER_ID
             , DECODE(TBL.IS_PARTITIONED, 'F', TBL.TABLE_OID, 'T', PT.PARTITION_OID) TABLE_OID
             , TBL.TABLE_NAME
             , PT.PARTITION_NAME
             , DECODE(TBL.IS_PARTITIONED, 'F', TBL.TBS_ID, 'T', PT.TBS_ID) TBS_ID
             , TBL.IS_PARTITIONED
          FROM SYSTEM_.SYS_TABLES_ TBL LEFT OUTER JOIN SYSTEM_.SYS_TABLE_PARTITIONS_ PT ON TBL.TABLE_ID = PT.TABLE_ID
       ) TBL
     , V$SEGMENT SEG
     , SYSTEM_.SYS_USERS_ U
     , V$TABLESPACES TBS
     , (SELECT SPACEID
             , SUM(DECODE(MAXSIZE, 0, CURRSIZE, MAXSIZE)) AS MAX
             , DECODE(MAX(AUTOEXTEND),1,'ON','OFF') 'AUTOEXTEND'
          FROM V$DATAFILES
         GROUP BY SPACEID) D
WHERE 1=1
   AND SEG.SEGMENT_TYPE = 'TABLE'  /* 'TABLE' : 테이블, 'INDEX' : 인덱스 */
   AND SEG.TABLE_OID = TBL.TABLE_OID
   AND U.USER_ID = TBL.USER_ID
   AND D.SPACEID = TBL.TBS_ID
   AND TBS.ID = TBL.TBS_ID
ORDER BY USER_NAME, PARTITIONED, TABLE_NAME, PARTITIONED_TABLE
;

// 버전 4.3.9

SELECT U.USER_NAME 'USER_NAME'
     , TBL.TABLE_NAME 'TABLE_NAME'
     , TBS.NAME 'TBS_NAME'
     , TO_CHAR((TBS.TOTAL_PAGE_COUNT * TBS.PAGE_SIZE)/1024 , '999,999,999') 'TBS_MAX(KB)'
     , TO_CHAR((TBS.A_EXTENT_PAGE_COUNT * TBS.PAGE_SIZE * SEG.EXTENT_TOTAL_COUNT)/1024, '999,999,999') 'ALLOC(KB)'
     , TO_CHAR((TBS.A_EXTENT_PAGE_COUNT * TBS.PAGE_SIZE * SEG.EXTENT_FULL_COUNT)/1024, '999,999,999') 'USED(KB)'
  FROM X$SEGMENT SEG
     , SYSTEM_.SYS_TABLES_ TBL
     , V$TABLESPACES TBS
     , SYSTEM_.SYS_USERS_ U
WHERE 1=1
   AND SEG.TABLE_OID = TBL.TABLE_OID
   AND SEG.SPACE_ID = TBL.TBS_ID
   AND SEG.SPACE_ID = TBS.ID
   AND TBL.USER_ID = U.USER_ID
   AND TBL.TABLE_TYPE = 'T'
   AND SEG.SEGMENT_TYPE = 6
ORDER BY USER_NAME, TABLE_NAME
;

+ 디스크 인덱스 사용량

// 디스크 인덱스의 사용량 정보.

// 버전 5.3.3 이상.

SELECT U.USER_NAME USER_NAME
     , I_LIST.TABLE_NAME
     , DECODE(I_LIST.PARTITION_NAME, NULL, '-', I_LIST.PARTITION_NAME) PARTITIONED_NAME
     , I_LIST.INDEX_NAME INDEX_NAME
     , DECODE(I_LIST.INDEX_PARTITION_NAME, NULL, '-', I_LIST.INDEX_PARTITION_NAME) PARTITIONED_INDEX
     , TBS.NAME TBS_NAME
     , TO_CHAR((D.MAX * TBS.PAGE_SIZE)/1024/1024, '999,999,999') 'MAX(MB)'
     , TO_CHAR((TBS.EXTENT_PAGE_COUNT * TBS.PAGE_SIZE * SEG.TOTAL_EXTENT_COUNT)/1024/1024, '999,999,999') 'ALLOC(MB)'
     , TO_CHAR(SEG.TOTAL_USED_SIZE/1024/1024, '999,999,999') 'USED(MB)'
  FROM (SELECT T.TABLE_NAME
             , PT.PARTITION_NAME
             , I.INDEX_NAME
             , PI.INDEX_PARTITION_NAME
             , DECODE(T.IS_PARTITIONED, 'F', I.TABLE_ID, 'T', PT.TABLE_ID) TABLE_ID
             , DECODE(T.IS_PARTITIONED, 'F', T.TABLE_OID, 'T', PT.PARTITION_OID) TABLE_OID
             , DECODE(I.IS_PARTITIONED, 'F', I.TBS_ID, 'T', PI.TBS_ID) TBS_ID
             , I.INDEX_ID
             , T.USER_ID
          FROM SYSTEM_.SYS_INDICES_ I LEFT OUTER JOIN SYSTEM_.SYS_INDEX_PARTITIONS_ PI ON PI.INDEX_ID = I.INDEX_ID
                                      LEFT OUTER JOIN SYSTEM_.SYS_TABLE_PARTITIONS_ PT ON PT.PARTITION_ID = PI.TABLE_PARTITION_ID
                                      LEFT OUTER JOIN SYSTEM_.SYS_TABLES_ T ON T.TABLE_ID = I.TABLE_ID ) I_LIST
     , X$SEGMENT SEG
     , V$INDEX I
     , V$TABLESPACES TBS
     , SYSTEM_.SYS_USERS_ U
     , (SELECT SPACEID
             , SUM(DECODE(MAXSIZE, 0, CURRSIZE, MAXSIZE)) AS MAX
             , DECODE(MAX(AUTOEXTEND),1,'ON','OFF') 'AUTOEXTEND'
          FROM V$DATAFILES
         GROUP BY SPACEID) D
WHERE 1=1
   AND SEG.TABLE_OID = I.TABLE_OID
   AND SEG.SEGMENT_PID = I.INDEX_SEG_PID
   AND SEG.SPACE_ID = I_LIST.TBS_ID
   AND I_LIST.INDEX_ID = I.INDEX_ID
   AND I_LIST.TABLE_OID = I.TABLE_OID
   AND I_LIST.TBS_ID = TBS.ID
   AND D.SPACEID = I_LIST.TBS_ID
   AND U.USER_ID = I_LIST.USER_ID
ORDER BY I_LIST.TABLE_NAME, I_LIST.INDEX_NAME, I_LIST.PARTITION_NAME, I_LIST.INDEX_PARTITION_NAME
;

// 버전 5.1.5

SELECT U.USER_NAME USER_NAME
     , I_LIST.TABLE_NAME
     , DECODE(I_LIST.PARTITION_NAME, NULL, '-', I_LIST.PARTITION_NAME) PARTITIONED_NAME
     , I_LIST.INDEX_NAME INDEX_NAME
     , DECODE(I_LIST.INDEX_PARTITION_NAME, NULL, '-', I_LIST.INDEX_PARTITION_NAME) PARTITIONED_INDEX
     , TBS.NAME TBS_NAME
     , TO_CHAR((D.MAX * TBS.PAGE_SIZE)/1024/1024, '999,999,999') 'MAX(MB)'
     , TO_CHAR((TBS.EXTENT_PAGE_COUNT * TBS.PAGE_SIZE * SEG.EXTENT_TOTAL_COUNT)/1024/1024, '999,999,999') 'ALLOC(MB)'
  FROM (SELECT T.TABLE_NAME
             , PT.PARTITION_NAME
             , I.INDEX_NAME
             , PI.INDEX_PARTITION_NAME
             , DECODE(T.IS_PARTITIONED, 'F', I.TABLE_ID, 'T', PT.TABLE_ID) TABLE_ID
             , DECODE(T.IS_PARTITIONED, 'F', T.TABLE_OID, 'T', PT.PARTITION_OID) TABLE_OID
             , DECODE(I.IS_PARTITIONED, 'F', I.TBS_ID, 'T', PI.TBS_ID) TBS_ID
             , I.INDEX_ID
             , T.USER_ID
          FROM SYSTEM_.SYS_INDICES_ I LEFT OUTER JOIN SYSTEM_.SYS_INDEX_PARTITIONS_ PI ON PI.INDEX_ID = I.INDEX_ID
                                      LEFT OUTER JOIN SYSTEM_.SYS_TABLE_PARTITIONS_ PT ON PT.PARTITION_ID = PI.TABLE_PARTITION_ID
                                      LEFT OUTER JOIN SYSTEM_.SYS_TABLES_ T ON T.TABLE_ID = I.TABLE_ID ) I_LIST
     , V$SEGMENT SEG
     , V$INDEX I
     , V$TABLESPACES TBS
     , SYSTEM_.SYS_USERS_ U
     , (SELECT SPACEID
             , SUM(DECODE(MAXSIZE, 0, CURRSIZE, MAXSIZE)) AS MAX
             , DECODE(MAX(AUTOEXTEND),1,'ON','OFF') 'AUTOEXTEND'
          FROM V$DATAFILES
         GROUP BY SPACEID) D
WHERE 1=1
   AND SEG.TABLE_OID = I.TABLE_OID
   AND SEG.SEGMENT_PID = I.INDEX_SEG_PID
   AND SEG.SPACE_ID = I_LIST.TBS_ID
   AND I_LIST.INDEX_ID = I.INDEX_ID
   AND I_LIST.TABLE_OID = I.TABLE_OID
   AND I_LIST.TBS_ID = TBS.ID
   AND D.SPACEID = I_LIST.TBS_ID
   AND U.USER_ID = I_LIST.USER_ID
ORDER BY I_LIST.TABLE_NAME, I_LIST.INDEX_NAME, I_LIST.PARTITION_NAME, I_LIST.INDEX_PARTITION_NAME
;

// 버전 4.3.9

SELECT U.USER_NAME AS 'USER_NAME'
     , TBL.TABLE_NAME AS 'TABLE_NAME'
     , IDX.INDEX_NAME AS 'INDEX_NAME'
     , TBS.NAME AS 'TBS_NAME'
     , TO_CHAR((TBS.TOTAL_PAGE_COUNT * TBS.PAGE_SIZE)/1024/1024 , '999,999,999') AS 'TBS_MAX(KB)'
     , TO_CHAR((TBS.A_EXTENT_PAGE_COUNT * TBS.PAGE_SIZE * SEG.EXTENT_TOTAL_COUNT)/1024/1024, '999,999,999') AS 'ALLOC(MB)'
     , TO_CHAR((TBS.A_EXTENT_PAGE_COUNT * TBS.PAGE_SIZE * SEG.EXTENT_FULL_COUNT )/1024/1024, '999,999,999') AS 'USED(MB)'
FROM  X$SEGMENT SEG
     , V$INDEX I
     , SYSTEM_.SYS_INDICES_ IDX
     , SYSTEM_.SYS_TABLES_ TBL
     , V$TABLESPACES TBS
     , SYSTEM_.SYS_USERS_ U
WHERE SEG.TABLE_OID = I.TABLE_OID
   AND SEG.SEGMENT_DESC = I.INDEX_SEG_DESC
   AND I.INDEX_ID = IDX.INDEX_ID
   AND IDX.TABLE_ID = TBL.TABLE_ID
   AND SEG.SPACE_ID = IDX.TBS_ID
   AND SEG.SPACE_ID = TBS.ID
   AND IDX.USER_ID = U.USER_ID
   AND TBL.TABLE_TYPE = 'T'
   AND SEG.SEGMENT_TYPE = 5
ORDER BY U.USER_NAME, TBL.TABLE_NAME, IDX.INDEX_NAME
   ;

+ 파티션 테이블 정보

// 파티션 테이블의 파티셔닝 방법, 파티셔닝 기준 등 파티션 테이블에 관한 정보를 보여주는 쿼리.

// 버전 6.5.1 이상.

SELECT U.USER_NAME
     , T.TABLE_NAME
     , P.PARTITION_NAME
     , DECODE(PM.PARTITION_METHOD, 0, RPAD('RANGE', 16), 1, RPAD('HASH', 16), 2, RPAD('LIST', 16)) PARTITION_METHOD
     , RPAD(P.PARTITION_ORDER, 15) PARTITION_ORDER
     , RPAD(PM.ROW_MOVEMENT, 12) ROW_MOVEMENT
     , P.PARTITION_MIN_VALUE
     , P.PARTITION_MAX_VALUE
     , RPAD(P.PARTITION_ACCESS, 6) 'ACCESS'                             -- 알티베이스 6.5.1 이전 버전은 삭제 후 사용
     , TO_CHAR(P.CREATED, 'YYYY-MM-DD HH:MI:SS') CREATED                -- 알티베이스 6.5.1 이전 버전은 삭제 후 사용
     , TO_CHAR(P.LAST_DDL_TIME, 'YYYY-MM-DD HH:MI:SS') LAST_DDL_TIME    -- 알티베이스 6.5.1 이전 버전은 삭제 후 사용
  FROM SYSTEM_.SYS_TABLES_ T
     , SYSTEM_.SYS_TABLE_PARTITIONS_ P
     , SYSTEM_.SYS_PART_TABLES_ PM
     , SYSTEM_.SYS_USERS_ U
WHERE 1=1
   AND U.USER_ID = T.USER_ID
   AND T.TABLE_ID = P.TABLE_ID
   AND PM.TABLE_ID = T.TABLE_ID ;

+ 시퀀스

SELECT USER_NAME
     , TABLE_NAME SEQ_NAME
     , MIN_SEQ MIN
     , CURRENT_SEQ
     , MAX_SEQ MAX
     , INCREMENT_SEQ INCREMENT
     , IS_CYCLE
     , CACHE_SIZE CACHE
  FROM V$SEQ A
     , SYSTEM_.SYS_USERS_ B
     , SYSTEM_.SYS_TABLES_ C
WHERE 1=1
   AND A.SEQ_OID = C.TABLE_OID
   AND B.USER_ID = C.USER_ID
   AND B.USER_NAME <> 'SYSTEM_' ;

+ 시노님

// 버전 5 이상.

SELECT NVL(U.USER_NAME, 'PUBLIC') SYNONYM_OWNER
     , S.SYNONYM_NAME
     , S.OBJECT_OWNER_NAME OBJECT_OWNER
     , S.OBJECT_NAME
     , TO_CHAR(S.LAST_DDL_TIME, 'YYYY-MM-DD HH:MI:SS') LAST_DDL_TIME
  FROM SYSTEM_.SYS_SYNONYMS_ S
       LEFT OUTER JOIN SYSTEM_.SYS_USERS_ U ON S.SYNONYM_OWNER_ID = U.USER_ID
WHERE 1=1
   --AND U.USER_ID <> 0       -- PUBLIC 시노님을 제외하고 싶은 경우 주석 제거하고 사용한다.
;

// 버전 4.3.9 이상.

SELECT NVL(U.USER_NAME, 'PUBLIC') SYNONYM_OWNER
     , S.SYNONYM_NAME
     , S.SCHEMA_NAME OBJECT_OWNER
     , S.OBJECT_NAME
  FROM SYSTEM_.SYS_SYNONYMS_ S LEFT OUTER JOIN SYSTEM_.SYS_USERS_ U ON S.USER_ID = U.USER_ID
;

+ PSM

SELECT A.USER_NAME
     , PROC_NAME PSM_NAME
     , DECODE(OBJECT_TYPE, 0, 'PROCEDURE', 1, 'FUNCTION', 3, 'TYPESET') PSM_TYPE
     , DECODE(STATUS, 0, 'VALID', 'INVALID') STATUS
     , TO_CHAR(B.CREATED, 'YYYY-MM-DD HH:MI:SS') CREATED
     , TO_CHAR(B.LAST_DDL_TIME, 'YYYY-MM-DD HH:MI:SS') LAST_DDL_TIME    -- 알티베이스 4 에서는 삭제 후 사용
  FROM SYSTEM_.SYS_USERS_ A
     , SYSTEM_.SYS_PROCEDURES_ B
WHERE 1=1
   AND A.USER_ID = B.USER_ID
   AND A.USER_NAME <> 'SYSTEM_'
ORDER BY 1, 2, 3;

+ PSM 생성구문

// PROC_NAME 조건에 PSM 이름을 입력하면 해당 PSM 의 생성 구문을 보여줌.

SELECT PARSE
  FROM SYSTEM_.SYS_PROC_PARSE_
WHERE PROC_OID = (SELECT PROC_OID
          FROM SYSTEM_.SYS_PROCEDURES_
         WHERE PROC_NAME = 'psm_name')   -- 조회하고자 하는 psm이름을 입력한다.
ORDER BY SEQ_NO ;

+ VIEW

SELECT A.USER_NAME
     , B.TABLE_NAME VIEW_NAME
     , DECODE(C.STATUS, 0, 'VALID', 'INVALID') STATUS
     , TO_CHAR(B.CREATED, 'YYYY-MM-DD HH:MI:SS') CREATED                 -- 알티베이스 4 에서는 삭제 후 사용
     , TO_CHAR(B.LAST_DDL_TIME, 'YYYY-MM-DD HH:MI:SS') LAST_DDL_TIME     -- 알티베이스 4 에서는 삭제 후 사용
  FROM SYSTEM_.SYS_USERS_ A
     , SYSTEM_.SYS_TABLES_ B
     , SYSTEM_.SYS_VIEWS_ C
WHERE 1=1
   AND A.USER_ID = B.USER_ID
   AND B.TABLE_ID = C.VIEW_ID
   AND B.TABLE_TYPE = 'V' ;

+ VIEW 생성 구문

SELECT PARSE
  FROM SYSTEM_.SYS_VIEW_PARSE_
WHERE VIEW_ID = (SELECT TABLE_ID
          FROM SYSTEM_.SYS_TABLES_
         WHERE TABLE_NAME = 'view_name')    -- 조회하고자 하는 view이름을 입력한다.
ORDER BY SEQ_NO ;

+ PACKAGE

// 패키지에 대한 정보를 보여줌.

// 버전 6.3.1 이상.

SELECT A.USER_NAME
     , PACKAGE_NAME
     , DECODE(PACKAGE_TYPE, 6, 'PACKAGE_SPEC', 7, 'PACKAGE_BODY') PACKAGE_TYPE
     , DECODE(STATUS, 0, 'VALID', 'INVALID') STATUS
     , TO_CHAR(B.CREATED, 'YYYY-MM-DD HH:MI:SS') CREATED
     , TO_CHAR(B.LAST_DDL_TIME, 'YYYY-MM-DD HH:MI:SS') LAST_DDL_TIME
  FROM SYSTEM_.SYS_USERS_ A
     , SYSTEM_.SYS_PACKAGES_ B
WHERE 1=1
   AND A.USER_ID = B.USER_ID
   AND A.USER_NAME <> 'SYSTEM_' ;

+ PACKAGE 서브프로그램

// 패키지에 포함된 서브프로그램(저장 프로시저와 저장 함수)에 대한 정보를 보여줌.

// 버전 6.3.1 이상.

SELECT USER_NAME
     , OBJECT_NAME
     , PACKAGE_NAME
     , DECODE(SUB_TYPE, 0, 'PROCEDURE', 1, 'FUNCTION') SUB_TYPE
     , PARA_NAME
     , PARA_ORDER
     , DECODE(INOUT_TYPE, 0, 'IN', 1, 'OUT', 2, 'IN OUT') INOUT_TYPE
     , DATA_TYPE
     , SIZE
     , DEFAULT_VAL
  FROM SYSTEM_.SYS_USERS_ A
     , SYSTEM_.SYS_PACKAGE_PARAS_ B
WHERE 1=1
   AND A.USER_ID = B.USER_ID
   AND A.USER_NAME <> 'SYSTEM_' ;

+ PACKAGE 생성구문

// PACKAGE_NAME 조건에 패키지 이름을 입력하면 해당 패키지의 생성 구문을 보여줌.

// 버전 6.3.1 이상

SELECT PARSE
     , DECODE(PACKAGE_TYPE, 6, 'PACKAGE_SPEC', 7, 'PACKAGE_BODY') PACKAGE_TYPE
  FROM SYSTEM_.SYS_PACKAGE_PARSE_
WHERE PACKAGE_OID IN (SELECT PACKAGE_OID
                         FROM SYSTEM_.SYS_PACKAGES_
                        WHERE PACKAGE_NAME = 'package_name')   -- 조회하고자 하는 패키지 이름을 입력한다.
ORDER BY SEQ_NO ;

+ 트리거

SELECT TR.USER_NAME
     , T.TABLE_NAME
     , TR.TRIGGER_NAME
     , RPAD(DECODE(TR.IS_ENABLE, 0, 'NO',
                                 1, 'YES'), 9) IS_ENABLE
     , RPAD(DECODE(TR.EVENT_TIME, 1, 'BEFORE',
                             2, 'AFTER',
                             3, 'INSTEAD OF'), 10) EVENT_TIME
     , RPAD(DECODE(TR.EVENT_TYPE, 1, 'INSERT',
                             2, 'DELETE',
                             4, 'UPDATE'), 10) EVENT_TYPE
     , DECODE(TR.GRANULARITY, 1, 'FOR EACH ROW',
                              2, 'FOR EACH STATEMENT') GRANULARITY
  FROM SYSTEM_.SYS_TABLES_ T
     , SYSTEM_.SYS_TRIGGERS_ TR
WHERE 1=1
   AND TR.TABLE_ID = T.TABLE_ID;

+ 작업(Job)

// 버전 6.5.1 이상.

SELECT JOB_NAME
     , EXEC_QUERY PROC_NAME
     , INTERVAL
     , LPAD(DECODE(INTERVAL_TYPE, 'YY', 'YEARLY',
                                  'MM', 'MONTHLY',
                                  'DD', 'DAILY',
                                  'HH', 'HOURLY',
                                  'MI', 'MINUTELY'), 13) INTERVAL_TYPE
     , LPAD(DECODE(STATE, 0, '-', 1, 'ING'), 5) STATE
     , LPAD(EXEC_COUNT, 10) EXEC_COUNT
     , LPAD(ERROR_CODE, 10) ERROR_CODE
     , TO_CHAR(START_TIME, 'YYYY-MM-DD HH:MI:SS') START_TIME
     , TO_CHAR(LAST_EXEC_TIME, 'YYYY-MM-DD HH:MI:SS') LAST_EXEC_TIME
     , TO_CHAR(END_TIME, 'YYYY-MM-DD HH:MI:SS') END_TIME
     , DECODE(IS_ENABLE, 'T', 'YES', 'F', 'NO') IN_ENABLE     -- 알티베이스 6.3.1 에서는 삭제 후 사용한다.
  FROM SYSTEM_.SYS_JOBS_;

- Privileges

+ 시스템 권한 (System Privilege)

SELECT A.USER_NAME GRANTEE
     , C.USER_NAME GRANTOR
     , REPLACE(D.PRIV_NAME, '_', ' ') PRIV_NAME
  FROM SYSTEM_.SYS_USERS_ A,
       SYSTEM_.SYS_GRANT_SYSTEM_ B,
       SYSTEM_.SYS_USERS_ C,
       SYSTEM_.SYS_PRIVILEGES_ D
WHERE C.USER_NAME <> 'SYSTEM_'
   AND A.USER_TYPE <> 'R'                 -- 이 조건은 알티베이스 6.5.1 이하 버전에서는 삭제 후 사용해야 한다.
   AND B.GRANTEE_ID = A.USER_ID
   AND B.GRANTOR_ID = C.USER_ID
   AND B.PRIV_ID = D.PRIV_ID
   --AND A.USER_NAME = 'user_name'
ORDER BY GRANTEE, GRANTOR ;

+ 객체 권한 (Object Privilege)

SELECT A.USER_NAME GRANTEE
     , C.USER_NAME GRANTOR
     , F.USER_NAME OBJECT_OWNER
     , OBJ.OBJ_NAME OBJECT_NAME
     , DECODE(OBJ.OBJ_TYPE, 'T', DECODE(OBJ.OBJ_TYPE1, 'T', 'TABLE', 'V', 'VIEW'),
                            'S', 'SEQUENCE',
                            'P', 'PROCEDURE',
                            'Y', 'External library',
                            'D', 'DIRECTORY') OBJECT_TYPE
     , REPLACE(D.PRIV_NAME, '_', ' ') PRIV_NAME
     , DECODE(B.WITH_GRANT_OPTION, 0, 'NO', 'YES') WITH_GRANT_OPTION
  FROM SYSTEM_.SYS_USERS_ A
     , SYSTEM_.SYS_GRANT_OBJECT_ B
     , SYSTEM_.SYS_USERS_ C
     , SYSTEM_.SYS_PRIVILEGES_ D
     , (SELECT TABLE_NAME as OBJ_NAME, TABLE_TYPE as OBJ_TYPE1, 'T' as OBJ_TYPE, TABLE_ID as obj_id, USER_ID
          FROM SYSTEM_.SYS_TABLES_ WHERE TABLE_TYPE IN ('V', 'T')
         UNION
        SELECT TABLE_NAME as OBJ_NAME, '' as OBJ_TYPE1, TABLE_TYPE as OBJ_TYPE, TABLE_ID as obj_id, USER_ID
          FROM SYSTEM_.SYS_TABLES_ WHERE TABLE_TYPE = 'S'
         UNION
        SELECT PROC_NAME as OBJ_NAME, '' as OBJ_TYPE1, 'P' as OBJ_TYPE, PROC_OID as obj_id, user_id
          FROM system_.SYS_PROCEDURES_
         UNION
        SELECT directory_NAME as OBJ_NAME, '' as OBJ_TYPE1, 'D' as OBJ_TYPE, DIRECTORY_ID as obj_id, user_id
          FROM SYSTEM_.SYS_DIRECTORIES_
         UNION                                                                                                 -- 6.3.1 이하에서는 삭제 후 사용
        SELECT library_name as OBJ_NAME, '' as OBJ_TYPE1, 'Y' as OBJ_TYPE, library_ID as obj_id, user_id       -- 6.3.1 이하에서는 삭제 후 사용
          FROM SYSTEM_.SYS_LIBRARIES_                                                                          -- 6.3.1 이하에서는 삭제 후 사용
       ) OBJ
     , SYSTEM_.SYS_USERS_ F
WHERE 1=1
   AND C.USER_NAME <> 'SYSTEM_'
   AND A.USER_TYPE <> 'R'        -- 이 조건은 알티베이스 6.5.1 이하 버전에서는 삭제 후 사용해야 한다.
   AND B.GRANTEE_ID = A.USER_ID
   AND B.GRANTOR_ID = C.USER_ID
   AND B.PRIV_ID = D.PRIV_ID
   AND B.OBJ_ID = OBJ.OBJ_ID
   AND OBJ.OBJ_TYPE = B.OBJ_TYPE
   AND OBJ.USER_ID = F.USER_ID
ORDER BY GRANTEE, GRANTOR, OBJECT_OWNER, OBJECT_NAME, PRIV_NAME ;

+ 롤(Role) 생성 정보

// 생성된 롤(Role) 목록과 롤에 부여된 시스템 권한 및 객체 권한을 확인할 수 있음.

// 버전 6.5.1 이상.

SELECT A.USER_NAME 'ROLE_NAME'
     , RPAD('SYSTEM', 9) PRIV_TYPE
     , '' OBJECT_NAME, '' OBJECT_TYPE
     , REPLACE(D.PRIV_NAME, '_', ' ') PRIV_NAME
     , '' WITH_GRANT_OPTION
  FROM SYSTEM_.SYS_USERS_ A,
       SYSTEM_.SYS_GRANT_SYSTEM_ B,
       SYSTEM_.SYS_PRIVILEGES_ D
WHERE 1=1
   AND A.USER_ID <> 0
   AND A.USER_TYPE = 'R'
   AND B.GRANTEE_ID = A.USER_ID
   AND B.PRIV_ID = D.PRIV_ID
UNION
SELECT A.USER_NAME 'ROLE_NAME'
     , RPAD('OBJECT', 9) PRIV_TYPE
     , F.USER_NAME||'.'||OBJ.OBJ_NAME OBJECT_NAME
     , DECODE(OBJ.OBJ_TYPE, 'T', DECODE(OBJ.OBJ_TYPE1, 'T', 'TABLE', 'V', 'VIEW'),
                            'S', 'SEQUENCE',
                            'P', 'PROCEDURE',
                            'Y', 'External library',
                            'D', 'DIRECTORY') OBJECT_TYPE
     , REPLACE(D.PRIV_NAME, '_', ' ') PRIV_NAME
     , DECODE(B.WITH_GRANT_OPTION, 0, 'NO', 'YES') WITH_GRANT_OPTION
  FROM SYSTEM_.SYS_USERS_ A
     , SYSTEM_.SYS_GRANT_OBJECT_ B
     , SYSTEM_.SYS_PRIVILEGES_ D
     , (SELECT TABLE_NAME as OBJ_NAME, TABLE_TYPE as OBJ_TYPE1, 'T' as OBJ_TYPE, TABLE_ID as obj_id, USER_ID
          FROM SYSTEM_.SYS_TABLES_ WHERE TABLE_TYPE IN ('V', 'T')
         UNION
        SELECT TABLE_NAME as OBJ_NAME, '' as OBJ_TYPE1, TABLE_TYPE as OBJ_TYPE, TABLE_ID as obj_id, USER_ID
          FROM SYSTEM_.SYS_TABLES_ WHERE TABLE_TYPE = 'S'
         UNION
        SELECT PROC_NAME as OBJ_NAME, '' as OBJ_TYPE1, 'P' as OBJ_TYPE, PROC_OID as obj_id, user_id
          FROM system_.SYS_PROCEDURES_
         UNION
        SELECT directory_NAME as OBJ_NAME, '' as OBJ_TYPE1, 'D' as OBJ_TYPE, DIRECTORY_ID as obj_id, user_id
          FROM SYSTEM_.SYS_DIRECTORIES_
         UNION
        SELECT library_name as OBJ_NAME, '' as OBJ_TYPE1, 'Y' as OBJ_TYPE, library_ID as obj_id, user_id
          FROM SYSTEM_.SYS_LIBRARIES_
       ) OBJ
     , SYSTEM_.SYS_USERS_ F
WHERE 1=1
   AND A.USER_ID <> 0
   AND A.USER_TYPE = 'R'
   AND B.GRANTEE_ID = A.USER_ID
   AND B.PRIV_ID = D.PRIV_ID
   AND B.OBJ_ID = OBJ.OBJ_ID
   AND OBJ.OBJ_TYPE = B.OBJ_TYPE
   AND OBJ.USER_ID = F.USER_ID
;

+ 롤(Role)을 부여받은 사용자 정보

// 버전 6.5.1 이상

SELECT U1.USER_NAME GRANTEE
     , U2.USER_NAME GRANTOR
     , RU.USER_NAME ROLE_NAME
  FROM SYSTEM_.SYS_USER_ROLES_ R
     , SYSTEM_.SYS_USERS_ RU
     , SYSTEM_.SYS_USERS_ U1
     , SYSTEM_.SYS_USERS_ U2
WHERE 1=1
   AND RU.USER_TYPE = 'R'
   AND R.ROLE_ID <> 0
   AND RU.USER_ID = R.ROLE_ID
   AND R.GRANTEE_ID = U1.USER_ID
   AND R.GRANTOR_ID = U2.USER_ID
;

+ 시스템 및 객체 권한 종류

SELECT DECODE(PRIV_TYPE, 1, 'OBJECT', 2, 'SYSTEM') PRIV_TYPE
, PRIV_NAME
FROM SYSTEM_.SYS_PRIVILEGES_
ORDER BY PRIV_TYPE, PRIV_NAME;

- Constraints

+ 전체 제약 조건 목록

// 버전 6.3.1 이상.

SELECT USER_NAME
     , TABLE_NAME OBJECT_NAME
     , DECODE(B.TABLE_TYPE, 'T', 'TABLE', 'Q', 'QUEUE', 'V', 'VIEW', 'SEQUENCE') OBJECT_TYPE
     , C.CONSTRAINT_NAME CONST_NAME
     , DECODE(C.CONSTRAINT_TYPE, 0, 'FOREIGN KEY',
                                 1, 'NOT NULL',
                                 2, 'UNIQUE',
                                 3, 'PRIMARY KEY',
                                 4, 'NULL',
                                 5, 'TIMESTAMP',
                                 6, 'LOCAL UNIQUE',
                                 7, 'CHECK') CONST_TYPE
     , COLUMN_NAME
     , CHECK_CONDITION          -- 알티베이스 6.3.1 이전 버전에서는 삭제 후 사용
  FROM SYSTEM_.SYS_USERS_ A,
       SYSTEM_.SYS_TABLES_ B,
       SYSTEM_.SYS_CONSTRAINTS_ C,
       SYSTEM_.SYS_COLUMNS_ D,
       SYSTEM_.SYS_CONSTRAINT_COLUMNS_ E
WHERE A.USER_ID=C.USER_ID
   AND B.TABLE_ID = C.TABLE_ID
   AND A.USER_ID = D.USER_ID
   AND A.USER_ID = E.USER_ID
   AND B.TABLE_ID = D.TABLE_ID
   AND B.TABLE_ID = E.TABLE_ID
   AND C.CONSTRAINT_ID = E.CONSTRAINT_ID
   AND D.COLUMN_ID = E.COLUMN_ID
   AND A.USER_NAME <> 'SYSTEM_'
ORDER BY USER_NAME, OBJECT_NAME, CONST_TYPE;

+ PK, FK, UNIQUE 관련 제약조건 및 테이블, 인덱스 목록

SELECT A.USER_NAME
     , B.TABLE_NAME
     , DECODE(C.CONSTRAINT_TYPE, 0, 'FK', 2, 'UNIQUE', 3, 'PK', 4, 'NULL') CONST_TYPE
     , C.CONSTRAINT_NAME CONST_NAME
     , DECODE(D.INDEX_NAME, C.CONSTRAINT_NAME, NULL, INDEX_NAME) INDEX_NAME
     , (SELECT TABLE_NAME
          FROM SYSTEM_.SYS_TABLES_
         WHERE TABLE_ID = C.REFERENCED_TABLE_ID) R_TABLE
     , (SELECT INDEX_NAME
          FROM SYSTEM_.SYS_INDICES_
         WHERE INDEX_ID = C.REFERENCED_INDEX_ID) R_INDEX
  FROM SYSTEM_.SYS_USERS_ A,
       SYSTEM_.SYS_TABLES_ B,
       SYSTEM_.SYS_CONSTRAINTS_ C LEFT OUTER JOIN SYSTEM_.SYS_INDICES_ D ON C.INDEX_ID = D.INDEX_ID
WHERE C.TABLE_ID = B.TABLE_ID
   AND A.USER_NAME <> 'SYSTEM_'
   AND C.USER_ID = A.USER_ID
   AND C.CONSTRAINT_TYPE IN (3, 0, 2, 6) --PK, FK, UNIQUE, LOCAL UNIQUE
ORDER BY TABLE_NAME, CONST_TYPE ;

+ 복합 인덱스 컬럼 구성 목록

SELECT D.USER_NAME
     , C.TABLE_NAME
     , B.INDEX_NAME
     , E.COLUMN_NAME
     , A.INDEX_COL_ORDER COL_ORDER
     , DECODE(A.SORT_ORDER, 'A', 'ASC', 'D', 'DESC') SORT
  FROM SYSTEM_.SYS_INDEX_COLUMNS_ A,
       SYSTEM_.SYS_INDICES_ B,
       SYSTEM_.SYS_TABLES_ C,
       SYSTEM_.SYS_USERS_ D,
       SYSTEM_.SYS_COLUMNS_ E
WHERE D.USER_NAME <> 'SYSTEM_'
   AND C.TABLE_TYPE = 'T'
   AND A.INDEX_ID = B.INDEX_ID
   AND A.TABLE_ID = C.TABLE_ID
   AND A.USER_ID = D.USER_ID
   AND A.COLUMN_ID = E.COLUMN_ID
ORDER BY USER_NAME, TABLE_NAME, INDEX_NAME, COL_ORDER ;

+ 인덱스 정보 요약

SELECT A.USER_NAME
     , C.INDEX_NAME
     , C.INDEX_ID
     , B.TABLE_NAME
     , NVL(D.NAME, 'SYS_TBS_MEMORY') TBS_NAME
     , C.IS_UNIQUE
     , C.COLUMN_CNT
  FROM SYSTEM_.SYS_USERS_ A,
       SYSTEM_.SYS_TABLES_ B,
       SYSTEM_.SYS_INDICES_ C LEFT OUTER JOIN V$TABLESPACES D ON C.TBS_ID = D.ID
WHERE A.USER_NAME <> 'SYSTEM_'
   AND B.TABLE_TYPE = 'T'
   AND C.TABLE_ID = B.TABLE_ID
   AND C.USER_ID = A.USER_ID
ORDER BY B.TABLE_NAME, C.INDEX_NAME ;

- Replication

+ 이중화 sender 정보

SELECT REP_NAME
     , PEER_IP REMOTE_IP
     , PEER_PORT REMOTE_REP_PORT
     , DECODE(STATUS, 0, 'STOP', 1, 'RUN', 2, 'RETRY') AS STAUS
     , REPL_MODE                                                    -- 알티베이스 4 버전에서는 이 컬럼을 삭제한다.
     , DECODE(NET_ERROR_FLAG, 0, 'OK', 'ERROR') AS NETWORK
     , XSN
  FROM V$REPSENDER ;

+ 이중화 receiver 정보

SELECT REP_NAME
     , PEER_IP REMOTE_IP
     , PEER_PORT REMOTE_REP_PORT
     , APPLY_XSN
  FROM V$REPRECEIVER ;

+ 이중화갭

SELECT REP_NAME
     , REP_SN
     , REP_LAST_SN
     , REP_GAP
     , READ_FILE_NO
     , START_FLAG        -- 알티베이스 5.3.3 이전 버전에서는 이 컬럼을 삭제 후 사용한다.
  FROM V$REPGAP ;

+ 이중화 전체 현황

SELECT A.REPLICATION_NAME REP_NAME
     , D.HOST_IP REMOTE_IP
     , NVL(TO_CHAR(E.REP_GAP), '-') AS REP_GAP
     , A.XSN RESTART_XSN
     , DECODE(B.PEER_PORT, NULL, 'OFF', 'ON') AS SENDER
     , DECODE(C.PEER_PORT, NULL, 'OFF', 'ON') AS RECEIVER
  FROM SYSTEM_.SYS_REPL_HOSTS_ D ,
       SYSTEM_.SYS_REPLICATIONS_ A
       LEFT OUTER JOIN V$REPSENDER B ON A.REPLICATION_NAME = B.REP_NAME
       LEFT OUTER JOIN V$REPRECEIVER C ON A.REPLICATION_NAME = C.REP_NAME
       LEFT OUTER JOIN (SELECT REP_NAME, MAX(REP_GAP) REP_GAP
                          FROM V$REPGAP
                         GROUP BY REP_NAME) E ON A.REPLICATION_NAME = E.REP_NAME
WHERE A.REPLICATION_NAME = D.REPLICATION_NAME
ORDER BY REP_NAME ;

+ 이중화를 수행하지 못해 누적된 리두로그 파일 측정

SELECT CASE2((BUFFER_MIN_SN < READ_SN),
                'REP BUFFER '||ROUND((BUFFER_MAX_SN-READ_SN)/(BUFFER_MAX_SN-BUFFER_MIN_SN)*100, 2)||' % LEFT ',
                (SELECT TO_CHAR(CUR_WRITE_LF_NO - READ_FILE_NO) FROM V$LFG, V$REPGAP)
            ) LOGFILE_FOR_REP
  FROM V$REPLOGBUFFER ;

+ 이중화 대상 테이블 목록

SELECT REPLICATION_NAME REP_NAME
     , LOCAL_USER_NAME||'.'||LOCAL_TABLE_NAME LOCAL_TBL
     , REMOTE_USER_NAME||'.'||REMOTE_TABLE_NAME REMOTE_TBL
  FROM SYSTEM_.SYS_REPL_ITEMS_
ORDER BY 1, 2 ;

'Altibase' 카테고리의 다른 글

[Altibase] FailOver CTF & STF (0)	2019.08.23

현재글[Altibase] 모니터링 쿼리 가이드

장기기억장치

ORA-00600, pdb의 table nologging 후에 계속되는 archivelog 생성, monitoring, pdb's table nologging not working, 파티션 exchange, diskgroup rename, 이중화, mysql 이중화, Oracle, kgfz_getDiskAccessMode:ntyp, ORA-27037, MySQL Replication, altibase, lima 0.22.0 upgrade 에러, internal error, /opt/socket_vmnet/bin/socket_vmnet, cluster_by_rowid, make -f ins_rdbms.mk rac_off, replication, pdb's table nologging 안됨,

Today :
Yesterday :

장기기억장치