JDBC로 연결되는 Tool 사용시 유용한 Altibase Query

--테이블크기 정보 조회 
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;

--테이블스페이스 정보 조회 
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
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;

--시노님 정보 조회
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;
/**

--리플리케이션 정보 조회 
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;

--시퀀스의 정보를 나타냄  
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;    

--전체 뷰에 대한 정보를 조회 
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;

--인덱스에 속한 전체 컬럼명을 출력 
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;

-- 모든 인덱스들의 대한 정보
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 에 대한 조회 
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 에 접속된 세션의 정보를 출력합니다. 
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 정보 조회

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 정보 조회 
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 정보 조회 

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 ;

--알티베이스 메모리 사이즈 부분별로 보기 (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;