- 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 |
|---|