iSQL에서 내용을 보기좋게
set linesize 200;
set colsize 30 ;
알티베이스에서 좀더 보기 쉽도록 포퍼먼스 뷰를 만든다.
출처 : 알티베이스 튜닝 교육
/**
--테이블크기 정보 조회
TABLESPACE_NAME : 테이블스페이스명
TABLE_NAME : 테이블명
ALLOC : 테이블 ALLOC 크기(MB)
USED : 테이블 사용량 크기(MB)
**/
CREATE OR REPLACE view ADM_TBL_INFO AS
SELECT A.NAME TABLESPACE_NAME,
B.TABLE_NAME TABLE_NAME,
TRUNC((C.FIXED_ALLOC_MEM+C.VAR_ALLOC_MEM)/1024/1024,2) "ALLOC(MB)",
TRUNC((C.FIXED_USED_MEM+C.VAR_USED_MEM)/1024/1024,2) "USED(MB)"
FROM V$TABLESPACES A,
SYSTEM_.SYS_TABLES_ B,
V$MEMTBL_INFO C
WHERE A.ID = C.TABLESPACE_ID
AND B.TABLE_OID = C.TABLE_OID
AND B.USER_ID != 1 AND B.TABLE_TYPE = 'T'
UNION ALL
SELECT A.NAME TABLESPACE_NAME,
B.TABLE_NAME,
TRUNC(C.DISK_TOTAL_PAGE_CNT*8/1024,2) ALLOC,
TRUNC(C.DISK_PAGE_CNT*8/1024,2) USED
FROM V$TABLESPACES A,
V$DISKTBL_INFO C,
SYSTEM_.SYS_TABLES_ B
WHERE C.TABLE_OID = B.TABLE_OID
AND A.ID = C.TABLESPACE_ID
ORDER BY 1,2;
/**
--테이블스페이스 정보 조회
TBS_NAME : 테이블스페이스명
MAXSIZE(MB) : 테이블스페이스 최대크기
USEDSIZE(MB) : 테이블스페이스 사용량
CURRSIZE(MB) : 테이블스페이스 현재크기
**/
CREATE OR REPLACE view ADM_TBS AS
SELECT RTRIM(NAME) AS TBS_NAME,
MAXSIZE/1024/1024 AS 'MAXSIZE(MB)',
USEDSIZE/1024/1024 AS 'USEDSIZE(MB)',
CURRSIZE/1024/1024 AS 'CURRSIZE(MB)'
FROM (
SELECT B.SPACEID,
B.MAXSIZE,
A.USEDSIZE,
CURRSIZE
FROM (
SELECT A.SPACE_ID,
NVL(sum(B.ALLOC), 0) ALLOCSIZE,
NVL(sum(B.USED), 0) USEDSIZE
FROM V$MEM_TABLESPACES A
LEFT JOIN
(
SELECT A.TABLESPACE_ID,
B.TABLE_NAME,
SUM(A.FIXED_ALLOC_MEM+A.VAR_ALLOC_MEM) ALLOC,
SUM(A.FIXED_USED_MEM+A.VAR_USED_MEM) USED
FROM V$MEMTBL_INFO A,
SYSTEM_.SYS_TABLES_ B
WHERE A.TABLE_OID=B.TABLE_OID
GROUP BY A.TABLESPACE_ID, B.TABLE_NAME
) B
ON A.SPACE_ID=B.TABLESPACE_ID
WHERE A.SPACE_ID != 0
group by a.space_id
) A,
(
SELECT 1 SPACEID,
MEM_MAX_DB_SIZE MAXSIZE,
MEM_ALLOC_PAGE_COUNT * 32 * 1024 ALLOCSIZE,
MEM_ALLOC_PAGE_COUNT * 32 * 1024 CURRSIZE
FROM V$DATABASE
) B
UNION ALL
SELECT DF.SPACEID SPACEID,
df.MAXSIZE,
NVL(UF.USEDSIZE,'N/A') USEDSIZE,
df.CURRSIZE
FROM (
SELECT SPACEID,
SUM(MAXSIZE) * 8 * 1024 MAXSIZE,
SUM(CURRSIZE) * 8 * 1024 CURRSIZE
FROM X$DATAFILES
GROUP BY SPACEID
) DF,
(
SELECT ID SPACEID,
to_char(ALLOCATED_PAGE_COUNT * 8 * 1024) as USEDSIZE
FROM v$TABLESPACES
WHERE ID NOT IN
(
SELECT SPACE_ID
FROM V$MEM_TABLESPACES
)
) UF
WHERE DF.SPACEID = UF.SPACEID
) TBS_SZ
LEFT OUTER JOIN
V$TABLESPACES TBS_INFO
ON TBS_SZ.SPACEID = TBS_INFO.ID
ORDER BY TBS_SZ.SPACEID;
/**
--USER 별 모든 OBJECT
USER_NAME : USER 명
TABLE_NAME :
OBJECT_TYPE : OBJECT 종류
**/
CREATE OR REPLACE view ADM_OBJECT AS
SELECT Y.USER_NAME,
X.TABLE_NAME,
DECODE(X.TABLE_TYPE, 'T', 'TABLE', 'V', 'VIEW', 'S', 'SEQUENCE') OBJECT_TYPE
FROM SYSTEM_.SYS_TABLES_ X INNER JOIN
SYSTEM_.SYS_USERS_ Y ON X.USER_ID = Y.USER_ID
WHERE Y.USER_ID > 1
UNION ALL
SELECT Y.USER_NAME,
X.PROC_NAME ,
'PSM'
FROM SYSTEM_.SYS_PROCEDURES_ X INNER JOIN
SYSTEM_.SYS_USERS_ Y ON X.USER_ID = Y.USER_ID
WHERE Y.USER_ID > 1
UNION ALL
SELECT NVL(B.USER_NAME,'PUBLIC') USER_NAME,
A.OBJECT_OWNER_NAME||'.'||OBJECT_NAME,'SYNONYMS'
FROM SYSTEM_.SYS_SYNONYMS_ A
LEFT OUTER JOIN SYSTEM_.SYS_USERS_ B
ON A.SYNONYM_OWNER_ID = B.USER_ID
WHERE OBJECT_OWNER_NAME NOT IN ('SYSTEM_')
ORDER BY 1;
/**
--시노님 정보 조회
USER_NAME : 유저이름(PUBLIC, 유저)
SYSNONYM_NAME : 시노님이름
OBJ_NAME : OBJECT 이름
**/
CREATE OR REPLACE view ADM_SYNONYM AS
SELECT NVL(B.USER_NAME,'PUBLIC') AS USER_NAME,
SYNONYM_NAME,
A.OBJECT_OWNER_NAME||'.'||OBJECT_NAME AS OBJ_NAME
FROM SYSTEM_.SYS_SYNONYMS_ A
LEFT OUTER JOIN SYSTEM_.SYS_USERS_ B
ON A.SYNONYM_OWNER_ID = B.USER_ID
WHERE OBJECT_OWNER_NAME NOT IN ('SYSTEM_')
ORDER BY 1;
/**
--리플리케이션 정보 조회
REPLICATION_NAME : 리플리케이션 명
XSN : Replication Sequence No
REPGAP : Replication Gap
REMOTE_REPL : Replication 대상 정보
RECEIVER_STAT : Reciever 상태
**/
CREATE OR REPLACE view ADM_REPLICATION AS
SELECT
A.REPLICATION_NAME,
A.XSN,
NVL(TO_CHAR(E.REP_GAP), 'N/A') AS REPGAP,
D.HOST_IP || ' (' || D.PORT_NO || '):' || CASE2(TO_CHAR(B.PEER_PORT) = '', 'OFF', 'ON') AS REMOTE_REPL,
CASE2(TO_CHAR(C.PEER_PORT) = '', 'OFF', 'ON') AS RECEIVER_STAT
FROM
( SELECT REP_NAME,
MAX(REP_GAP) AS REP_GAP
FROM
V$REPGAP
GROUP BY REP_NAME
) E,
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
WHERE D.REPLICATION_NAME = A.REPLICATION_NAME
AND A.REPLICATION_NAME = E.REP_NAME
ORDER BY A.REPLICATION_NAME;
/**
--시퀀스의 정보를 나타냄
SEQ_NAME : SEQUENCE 명
CURRENT_SEQ : 현재 SEQUENCE 번호
START_SEQ : SEQUENCE 시작 번호
INCREMENT_SEQ : 증가 번호
CACHE_SIZE : 캐시
MAX_SEQ : 최대 값
MIN_SEQ : 최소 값
IS_CYCLE : 순환 여부
**/
CREATE OR REPLACE view ADM_SEQUENCE AS
SELECT C.USER_NAME||'.'||B.TABLE_NAME SEQ_NAME,
A.CURRENT_SEQ,
A.START_SEQ,
A.INCREMENT_SEQ,
A.CACHE_SIZE,
A.MAX_SEQ,
A.MIN_SEQ,
A.IS_CYCLE
FROM V$SEQ A, SYSTEM_.SYS_TABLES_ B, SYSTEM_.SYS_USERS_ C
WHERE A.SEQ_OID = B.TABLE_OID
AND B.USER_ID = C.USER_ID
AND B.USER_ID != 1
ORDER BY SEQ_NAME;
/**
--전체 뷰에 대한 정보를 조회
VIEW_NAME : 뷰 명
VIEW_ID : View Object ID
STATUS : View 의 상태
**/
CREATE OR REPLACE view ADM_VIEW AS
SELECT C.USER_NAME||'.'||B.TABLE_NAME VIEW_NAME,
A.VIEW_ID,
DECODE(A.STATUS,0,'VALID','INVALID') STATUS
FROM SYSTEM_.SYS_VIEWS_ A, SYSTEM_.SYS_TABLES_ B,
SYSTEM_.SYS_USERS_ C
WHERE A.VIEW_ID = B.TABLE_ID AND A.USER_ID=C.USER_ID
ORDER BY VIEW_NAME;
/**
--인덱스에 속한 전체 컬럼명을 출력
TABLE_NAME : 테이블명
INDEX_NAME : 인덱스명
COLUMN_NAME : 컬럼명
COLUMN_ORDER : 해당 컬럼 Ordering 된 순서
**/
CREATE OR REPLACE view ADM_COL_INDEX AS
SELECT D.USER_NAME||'.'||C.TABLE_NAME TABLE_NAME,
B.INDEX_NAME,
E.COLUMN_NAME,
DECODE(SORT_ORDER,'A','ASC','D','DESC') COLUMN_ORDER
FROM SYSTEM_.SYS_INDEX_COLUMNS_ A, SYSTEM_.SYS_INDICES_ B,
SYSTEM_.SYS_TABLES_ C, SYSTEM_.SYS_USERS_ D,
SYSTEM_.SYS_COLUMNS_ E
WHERE 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
AND D.USER_ID != 1
ORDER BY TABLE_NAME, INDEX_NAME, INDEX_COL_ORDER;
/**
-- 모든 인덱스들의 대한 정보
INDEX_ID : Index ID
TABLE_NAME : 해당 인덱스가 있는 테이블 명
TBS : Index의 Tablespace 명
UNIQUENESS : Unique Index 여부
COLUMN_COUNT : Column 갯수
**/
CREATE OR REPLACE view ADM_INDEX AS
SELECT A.INDEX_ID,
C.USER_NAME||'.'||B.TABLE_NAME TABLE_NAME,
A.INDEX_NAME INDEX_NAME,
NVL(D.NAME,'MEMORY') TBS,
DECODE(IS_UNIQUE,'T','UNIQUE','NONUIQUE') UNIQUENESS,
A.COLUMN_CNT COLUMN_COUNT
FROM SYSTEM_.SYS_INDICES_ A LEFT OUTER JOIN V$TABLESPACES D
ON A.TBS_ID = D.ID,
SYSTEM_.SYS_TABLES_ B, SYSTEM_.SYS_USERS_ C
WHERE A.TABLE_ID = B.TABLE_ID
AND A.USER_ID = C.USER_ID
AND C.USER_ID != 1
ORDER BY TABLE_NAME, INDEX_NAME ;
/**
-- Constraint 에 대한 조회
TABLE_NAME : Contraint 가 설정된 테이블 명
CONSTRAINT_NAME : Constraint 명
CON_TYPE : Contraint 종류
INDEX_NAME : 해당 Constraint 가 index 존재를 필요로 할 경우 해당 Index 명
REFERENCE_TABLE : Constraint 가 FK 일 경우 참조하는 테이블 명
REFERENCE_INDEX : Constraint 가 FK 일 경우 참조하는 인덱스 명
**/
CREATE OR REPLACE view ADM_CONSTRAINT AS
SELECT C.USER_NAME||'.'||B.TABLE_NAME TABLE_NAME,
A.CONSTRAINT_NAME,
DECODE(A.CONSTRAINT_TYPE,0,'FOREIGNKEY',
1,'NOTNULL',
2,'UNIQUE',
3,'PRIMARYKEY',
4,'NULL',
5,'TIMESTAMP','UNKNOWN') CON_TYPE,
D.INDEX_NAME,
(SELECT TABLE_NAME FROM SYSTEM_.SYS_TABLES_ IT WHERE IT.TABLE_ID = A.REFERENCED_TABLE_ID) REFERENCE_TABLE,
(SELECT INDEX_NAME FROM SYSTEM_.SYS_INDICES_ II WHERE II.INDEX_ID = A.REFERENCED_INDEX_ID) REFERENCE_INDEX
FROM SYSTEM_.SYS_CONSTRAINTS_ A LEFT OUTER JOIN SYSTEM_.SYS_INDICES_ D
ON A.INDEX_ID = D.INDEX_ID ,
SYSTEM_.SYS_TABLES_ B, SYSTEM_.SYS_USERS_ C
WHERE A.TABLE_ID = B.TABLE_ID
AND A.USER_ID = C.USER_ID
ORDER BY TABLE_NAME ,CON_TYPE;
/**
-- 현재 Altibase 에 접속된 세션의 정보를 출력합니다.
ID : Session ID
CONN_INFO : 접속된 세션의 정보
ACTIVE : Action 여부를 판단합니다.
OPEN_STMT : 해당 세션이 연 모든 Statment 갯수
CURRENT_STMT : 현재 수행 중인 Statement ID
COMMIT_MODE : Commit 모드
**/
CREATE OR REPLACE view ADM_SESSION AS
SELECT ID,
REPLACE2(REPLACE2(COMM_NAME,'SOCKET-',NULL),'-SERVER',NULL) CONN_INFO,
DECODE(ACTIVE_FLAG,1,'ACTIVE','INACTIVE') ACTIVE,
OPENED_STMT_COUNT OPEN_STMT,
CURRENT_STMT_ID CURRENT_STMT,
CLIENT_PID,
DECODE(AUTOCOMMIT_FLAG,0,'Non AutoCommit','AutoCommit') COMMIT_MODE
FROM V$SESSION;
/**
-- LOCK 정보 조회
SESSION_ID : 세션 ID
TX_ID : 트렌젝션 ID
TBL_INFO : 테이블 정보
LOCK_ITEM_TYPE : LOCK 상태
STATE : 쿼리 상태
QUERY : 쿼리 내역
**/
CREATE OR REPLACE view ADM_LOCK AS
SELECT A.SESSION_ID AS SESSION_ID,
A.TX_ID AS TX_ID,
DECODE(LOCK_ITEM_TYPE,'TBS',B.NAME,'TBL',C.TABLE_NAME,DBF_ID) TBL_INFO ,
LOCK_DESC AS LOCK_ITEM_TYPE,
DECODE(A.STATE,0,'ALLOC',1,'PREPARED',2,'FETCH-READY',4,'FETCHING','UNKNOWN') AS STATE,
RPAD(QUERY,120) AS QUERY
FROM V$LOCK_STATEMENT A LEFT OUTER JOIN V$TABLESPACES B
ON A.TBS_ID=B.ID
LEFT OUTER JOIN SYSTEM_.SYS_TABLES_ C
ON A.TABLE_OID=C.TABLE_OID
ORDER BY SESSION_ID;
/**
-- PROCEDURE 정보 조회
USER_NAME : 유저이름
PROC_NAME : procedure 이름
OBJECT_TYPE : 타입(PROCEDURE,FUNCTION,TYPE_SET)
STATUS : 상태
**/
CREATE OR REPLACE view ADM_PROC AS
SELECT B.USER_NAME, A.PROC_NAME,
DECODE(A.OBJECT_TYPE,0,'PROCEDURE',1,'FUNCTION','TYPE_SET') AS TYPE,
DECODE(A.STATUS,0,'VALID','INVALID') AS STATUS
FROM SYSTEM_.SYS_PROCEDURES_ A, SYSTEM_.SYS_USERS_ B
WHERE A.USER_ID=B.USER_ID
AND B.USER_NAME <> 'SYSTEM_';
/**
-- TRIGGER 정보 조회
USER_NAME : 유저이름
TRIGGER_NAME : 트리거 이름
TABLE_NAME : 테이블 이름
ENABLE : 상태
EVENT_TIME : 이벤트 시간
EVENT_TYPE : 이벤트 타입
GRANULARITY : 이벤트 조건
**/
CREATE OR REPLACE view ADM_TRIGGER AS
SELECT B.USER_NAME, A.TRIGGER_NAME, C.TABLE_NAME,
DECODE(A.IS_ENABLE,0,'DISABLE','ENABLE') AS ENABLE,
DECODE(A.EVENT_TIME,1,'BEFORE','AFTER') AS EVENT_TIME,
DECODE(A.EVENT_TYPE,1,'INSERT',2,'DELETE',3,'UPDATE') AS EVENT_TYPE,
DECODE(A.GRANULARITY,1,'FOR EACH ROW',2,'FOR EACH STATEMENT') AS GRANULARITY
FROM SYSTEM_.SYS_TRIGGERS_ A, SYSTEM_.SYS_USERS_ B,
SYSTEM_.SYS_TABLES_ C
WHERE A.USER_ID=B.USER_ID
AND C.TABLE_ID=A.TABLE_ID ;
http://newhyuki.egloos.com/1957158
알티베이스 메모리 사이즈 부분별로 보기 (v$memstat)
->
SELECT * FROM V$MEMSTAT ORDER By 4 DESC;
------------------------------------------------------------------------------------------------------------------
메모리 테이블 별 사이즈 보기.
->
SELECT
A.TABLE_OID TABLEOID,
C.USER_NAME||'.'||B.TABLE_NAME TABLENAME,
A.MEM_SLOT_SIZE SLOTSIZE,
FIXED_ALLOC_MEM+VAR_ALLOC_MEM ALLOCBYTE,
FIXED_USED_MEM+VAR_USED_MEM USEDBYTE,
ROUND ((FIXED_USED_MEM+VAR_USED_MEM)/(FIXED_ALLOC_MEM+VAR_ALLOC_MEM)*100,2) EFFICIENCY
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
C.USER_ID != 1 AND
B.TABLE_TYPE != 'V' AND
B.TABLE_TYPE = 'T'
ORDER BY TABLENAME;
출처 : http://www.mins01.com/20090118/board/board.php?type=read&b_id=tech&sh=&sw=&cat=DataBase&page=1&b_idx=585