Skip to content
Contents

Useful SQL#

Miscellaneous queries#

State of a service thread#

This query shows the state of a service thread.

Column Name Description
RUN_MODE the type of service thread. (dedicated or shared)
STATE currently working state (executing / pool)
CNT the number of threads in the run_mode and state

Altibase V4#

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

Altibase V5#

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

Lock & Transaction Information#

This query shows the lock & transaction information including replication transactions.

Column Name Description
TX_ID current transaction id
BLOCKED_TX_ID the transaction id which current transaction is waiting for
STATUS current transaction's status
USER_NAME the user name who has issued the current transaction
SESSION_ID the session id which has issued the current transaction
CLIENT_IP client IP address
AUTOCOMMIT autocommit
LOCK_DESC Lock description
FIRST_UPDATE_TIME the first update time of current transaction
TABLE_NAME table_name
CURRENT_QUERY current query in the current transaction
DDL DDL or not
LOGFILE# logfile number

Altibase V4#

Icon

Altibase V4 does not support this features.

Altibase V5#

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 / (606024), '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 -- 6:END
ORDER BY TX.ID, ST.ID, TX.FIRST_UPDATE_TIME DESC ;

Redo log files#

This query shows the information of transaction log files

Column Name Description
OLDEST_LOG_FILE the oldest log file in LOG_DIR
CURRENT_LOG_FILE the current log file number which service thread is writing to.
LOG_FILE_GAP the interval between CURRENT_LOG_FILE and OLDEST_LOG_FILE
SELECT  OLDEST_ACTIVE_LOGFILE OLDEST_LOGFILE,
        CURRENT_LOGFILE CURRENT_LOGFILE,
        CURRENT_LOGFILE-OLDEST_ACTIVE_LOGFILE LOGFILE_GAP
FROM V$ARCHIVE ;

Cumulative count of total transaction waits due to logging#

This query shows the number of total waits due to lack of the log space exhaustion for logging. If the value returns non-zero value, you'd better increase PREPARE_LOG_FILE_COUNT in altibase.properties.

SELECT  LF_PREPARE_WAIT_COUNT
FROM V$LFG;

Memory Ager GAP#

This query shows the memory ager thread's information.

Column Name Description
GC_NAME the Ager name. (LOGICAL_AGER for index aging, DELTHRD for data aging)
SCNOFTAIL The commit SCN of the tail in garbage collection OID list
MINMEMSCNINTXS The lowest of the view SCNs for memory-related transactions
GC_GAP the OID count which ager has to proceed
SELECT  GC_NAME,
        SCNOFTAIL,
        MINMEMSCNINTXS,
        ADD_OID_CNT-GC_OID_CNT GC_GAP
FROM V$MEMGC ;

Finding a query which blocks ager from aging.#

Aging process will be blocked if there's a transaction which refers the aging target. This query shows the query which blocks from aging.

Column Name Description
SESSION_ID the session identifier
TOTAL_TIME total elapsed time
EXECUTE_TIME the elapsed time for query execution
TX_ID transaction identifier
QUERY query string
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 status#

This query shows the memory state arranged by ALTIBASE module.

Column Name Description
NAME the name of ALTIBASE module
ALLOC_MAX(M) max alloc size of memory
ALLOC(M) current size of memory
SELECT  NAME,
          ROUND(MAX_TOTAL_SIZE/1024/1024) 'ALLOC_MAX(M)',
          ROUND(ALLOC_SIZE/1024/1024) 'ALLOC(M)'
FROM      V$MEMSTAT
ORDER BY 3 DESC ;

Total memory usage of ALTIBASE#

This query shows the total memory state.

Column Name Description
ALLOC_MAX(M) max alloc size of memory
ALLOC(M) current size of memory
SELECT  ROUND(SUM(MAX_TOTAL_SIZE)/1024/1024) 'ALLOC_MAX(M)',
        ROUND(SUM(ALLOC_SIZE)/1024/1024) 'ALLOC(M)'
FROM V$MEMSTAT ;

SQL about Objects#

Memory Table#

This query returns the object information about a memory table.

Column Name Description
USER_NAME the object owner
TABLE_NAME the object name
TABLESPACE_NAME the tablespace name which this object belongs to
TABLE_OID the table object identifier
ALLOC(M) allocated size of the memory table (Megabyte)
USED(M) actually used size of the memory table (Megabyte)
EFFICIENCY(%) store efficiency of the object

Altibase V4#

SELECT  A.USER_NAME,
        B.TABLE_NAME,
        'SYS_TBS_MEMORY' TABLESPACE_NAME,
        C.TABLE_OID,
        ROUND((C.FIXED_ALLOC_MEM + C.VAR_ALLOC_MEM)/(1024*1024),2) 'ALLOC(M)',
        ROUND((C.FIXED_USED_MEM + C.VAR_USED_MEM)/(1024*1024),2) 'USED(M)',
        ROUND((C.FIXED_USED_MEM + C.VAR_USED_MEM)/(C.FIXED_ALLOC_MEM + C.VAR_ALLOC_MEM)*100,2) 'EFFICIENCY(%)'
FROM    SYSTEM_.SYS_USERS_ A,
        SYSTEM_.SYS_TABLES_ B,
        V$MEMTBL_INFO C
WHERE   A.USER_NAME <> 'SYSTEM_'
        AND B.TABLE_TYPE = 'T'
        AND A.USER_ID = B.USER_ID
        AND B.TABLE_OID = C.TABLE_OID;

Altibase V5#

SELECT  A.USER_NAME,
        B.TABLE_NAME,
        D.NAME TABLESPACE_NAME,
        C.TABLE_OID,
        ROUND((C.FIXED_ALLOC_MEM + C.VAR_ALLOC_MEM)/(1024*1024),2) 'ALLOC(M)',
        ROUND((C.FIXED_USED_MEM + C.VAR_USED_MEM)/(1024*1024),2) 'USED(M)',
        ROUND((C.FIXED_USED_MEM + C.VAR_USED_MEM)/(C.FIXED_ALLOC_MEM + C.VAR_ALLOC_MEM)*100,2) 'EFFICIENCY(%)'
FROM    SYSTEM_.SYS_USERS_ A,
        SYSTEM_.SYS_TABLES_ B,
        V$MEMTBL_INFO C,
        V$TABLESPACES D
WHERE   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 ;

Queue Table#

This query returns the object information about a queue table.

Column Name Description
USER_NAME the object owner
TABLE_NAME the object name
TABLESPACE_NAME the tablespace name which this object belongs to
TABLE_OID the table object identifier
ALLOC(M) allocated size of the memory table (Megabyte)
USED(M) actually used size of the memory table (Megabyte)
EFFICIENCY(%) store efficiency of the object

Icon

Queue table structure is the same as a memory table except its functionality.

Altibase V4#

SELECT  A.USER_NAME,
        B.TABLE_NAME,
        'SYS_TBS_MEMORY' TABLESPACE_NAME,
        C.TABLE_OID,
        ROUND((C.FIXED_ALLOC_MEM + C.VAR_ALLOC_MEM)/(1024*1024),2) 'ALLOC(M)',
        ROUND((C.FIXED_USED_MEM + C.VAR_USED_MEM)/(1024*1024),2) 'USED(M)',
        ROUND((C.FIXED_USED_MEM + C.VAR_USED_MEM)/(C.FIXED_ALLOC_MEM + C.VAR_ALLOC_MEM)*100,2) 'EFFICIENCY(%)'
FROM    SYSTEM_.SYS_USERS_ A,
        SYSTEM_.SYS_TABLES_ B,
        V$MEMTBL_INFO C
WHERE   A.USER_NAME <> 'SYSTEM_'
        AND B.TABLE_TYPE = 'Q'
        AND A.USER_ID = B.USER_ID
        AND B.TABLE_OID = C.TABLE_OID;

Altibase V5#

SELECT  A.USER_NAME,
        B.TABLE_NAME,
        D.NAME TABLESPACE_NAME,
        C.TABLE_OID,
        ROUND((C.FIXED_ALLOC_MEM + C.VAR_ALLOC_MEM)/(1024*1024),2) 'ALLOC(M)',
        ROUND((C.FIXED_USED_MEM + C.VAR_USED_MEM)/(1024*1024),2) 'USED(M)',
        ROUND((C.FIXED_USED_MEM + C.VAR_USED_MEM)/(C.FIXED_ALLOC_MEM + C.VAR_ALLOC_MEM)*100,2) 'EFFICIENCY(%)'
FROM    SYSTEM_.SYS_USERS_ A,
        SYSTEM_.SYS_TABLES_ B,
        V$MEMTBL_INFO C,
        V$TABLESPACES D
WHERE   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 ;

Memory table / Queue table Index information#

This query returns the index information of a memory table and a queue object.

Column Name Description
USER_NAME the object owner
OBJECT_TYPE the type of object
OBJECT_NAME the object name
TABLESPACE_NAME the tablespace name which this object belongs to
INDEX_NAME the table object identifier
INDEX_TYPE the table object identifier

Icon

Estimating of memory index is very simple in most cases. Altibase index just consists of pointers, thus you just multiply 16 bytes by the number of table rows to get the size of each index.

Altibase V4#

SELECT  C.USER_NAME,
        DECODE(F.TABLE_TYPE, 'Q', 'QUEUE', 'T', 'TABLE') OBJECT_TYPE,
        TABLE_NAME OBJECT_NAME,
        'SYS_TBS_MEMORY' TABLESPACE_NAME,
        E.INDEX_NAME,
        RPAD(CASE2(E.INDEX_TYPE=1, 'B-TREE', 'R-TREE'),10,' ') INDEX_TYPE
FROM    V$INDEX B,
        SYSTEM_.SYS_USERS_ C,
        SYSTEM_.SYS_INDICES_ E,
        SYSTEM_.SYS_TABLES_ F
WHERE   B.INDEX_ID = E.INDEX_ID
        AND E.USER_ID = C.USER_ID
        AND F.USER_ID = E.USER_ID
        AND F.TABLE_OID = B.TABLE_OID
        AND C.USER_NAME <> 'SYSTEM_';

Altibase V5#

SELECT  C.USER_NAME,
        DECODE(F.TABLE_TYPE, 'Q', 'QUEUE', 'T', 'TABLE') OBJECT_TYPE,
        TABLE_NAME OBJECT_NAME,
        D.SPACE_NAME TABLESPACE_NAME,
        E.INDEX_NAME,
        RPAD(CASE2(E.INDEX_TYPE=1, 'B-TREE', 'R-TREE'),10,' ') INDEX_TYPE
FROM    V$INDEX B,
        SYSTEM_.SYS_USERS_ C,
        V$MEM_TABLESPACES D,
        SYSTEM_.SYS_INDICES_ E,
        SYSTEM_.SYS_TABLES_ F
WHERE   B.INDEX_ID = E.INDEX_ID
        AND E.USER_ID = C.USER_ID
        AND F.USER_ID = E.USER_ID
        AND F.TBS_ID = D.SPACE_ID
        AND F.TABLE_OID = B.TABLE_OID
        AND C.USER_NAME <> 'SYSTEM_';

Disk Table#

This query returns the object information about a disk table.

Column Name Description
USER_NAME the object owner
TABLE_NAME the object name
TABLESPACE_NAME the tablespace name which this object belongs to
TABLE_OID the table object identifier
ALLOC(M) allocated size of the memory table (Megabyte)
SELECT  USER_NAME,
        A.TABLE_NAME,
        D.NAME TBS_NAME,
        A.TABLE_OID,
        ROUND((B.DISK_PAGE_CNT*8)/1024) 'ALLOC(M)'
FROM    SYSTEM_.SYS_TABLES_ A,
        V$DISKTBL_INFO B,
        SYSTEM_.SYS_USERS_ C,
        V$TABLESPACES D
WHERE   A.TABLE_OID = B.TABLE_OID
        AND A.USER_ID = C.USER_ID
        AND A.TBS_ID=D.ID
        AND C.USER_NAME <> 'SYSTEM_';

Disk Index#

This query returns the object information about a disk index.

Column Name Description
USER_NAME the object owner
TABLE_NAME the object name
TBS_NAME the tablespace name which this object belongs to
INDEX_NAME the name of index
INDEX_TYPE the type of index
ALLOC(M) allocated size of the memory table (Megabyte)

Altibase V4#

SELECT  USER_NAME,
        TABLE_NAME,
        D.NAME TBS_NAME,
        E.INDEX_NAME,
        RPAD(CASE2(E.INDEX_TYPE=1,'B-TREE', 'R-TREE'),10,' ') INDEX_TYPE,
        ROUND(D.A_EXTENT_PAGE_COUNTD.PAGE_SIZEA.EXTENT_TOTAL_COUNT/1024/1024) 'ALLOC(M)'
FROM    V$SEGMENT A,
        V$INDEX B,
        V$TABLESPACES D,
        SYSTEM_.SYS_USERS_ C,
        SYSTEM_.SYS_INDICES_ E,
        SYSTEM_.SYS_TABLES_ F
WHERE   A.SEGMENT_DESC = B.INDEX_SEG_DESC
        AND B.INDEX_ID = E.INDEX_ID
        AND E.USER_ID = C.USER_ID
        AND A.SPACE_ID = D.ID
        AND F.TBS_ID = D.ID
        AND A.SEGMENT_TYPE='INDEX'
        AND F.USER_ID = E.USER_ID
        AND F.TABLE_OID = B.TABLE_OID ;

Altibase V5#

SELECT  USER_NAME,
        TABLE_NAME,
        D.NAME TBS_NAME,
        E.INDEX_NAME,
        RPAD(CASE2(E.INDEX_TYPE=1,'B-TREE', 'R-TREE'),10,' ') INDEX_TYPE,
        ROUND(D.EXTENT_PAGE_COUNTD.PAGE_SIZEA.EXTENT_TOTAL_COUNT/1024/1024) 'ALLOC(M)'
FROM    V$SEGMENT A,
        V$INDEX B,
        V$TABLESPACES D,
        SYSTEM_.SYS_USERS_ C,
        SYSTEM_.SYS_INDICES_ E,
        SYSTEM_.SYS_TABLES_ F
WHERE   A.SEGMENT_PID = B.INDEX_SEG_PID
        AND B.INDEX_ID = E.INDEX_ID
        AND E.USER_ID = C.USER_ID
        AND A.SPACE_ID = D.ID
        AND F.TBS_ID = D.ID
        AND A.SEGMENT_TYPE='INDEX'
        AND F.USER_ID = E.USER_ID
        AND F.TABLE_OID = B.TABLE_OID ;

Sequence#

This query returns the object information about a sequence.

Column Name Description
USER_NAME the object owner
SEQ_NAME the object name
MIN the minimum value of the sequence
CURRENT_SEQ the current value of the sequence
MAX the maximum value of the sequence
INCREMENT increment property of the sequence
IS_CYCLE can be cycled
CACHE the cache size of the sequence
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   A.SEQ_OID = C.TABLE_OID
        AND B.USER_ID = C.USER_ID
        AND B.USER_NAME <> 'SYSTEM_' ;

Synonym#

This query returns the object information about a synonym.

Column Name Description
SYNONYM_OWNER the object owner
SYNONYM_NAME the object name
OBJECT_OWNER the minimum value of the sequence
OBJECT_NAME the current value of the sequence
LAST_DDL_TIME the last DDL occured time

Altibase V4#

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

Altibase V5#

SELECT  NVL(USER_NAME, 'PUBLIC') SYNONYM_OWNER,
        SYNONYM_NAME,
        OBJECT_OWNER_NAME OBJECT_OWNER,
        OBJECT_NAME,
        TO_CHAR(A.LAST_DDL_TIME, 'YYYY-MM-DD HH:MI:SS') LAST_DDL_TIME
FROM    SYSTEM_.SYS_SYNONYMS_ A LEFT OUTER JOIN
        SYSTEM_.SYS_USERS_ B ON A.SYNONYM_OWNER_ID = B.USER_ID
WHERE   OBJECT_OWNER_NAME <> 'SYSTEM_' ;

PSM (Stored Procedures / Function / Typeset)#

This query returns the object information about a PSM.

Column Name Description
USER_NAME the object owner
PSM_TYPE the type of PSM
PSM_NAME the name of PSM
STATUS the status of PSM
LAST_DDL_TIME the last DDL occured time

Altibase V4#

SELECT  A.USER_NAME,
        DECODE(OBJECT_TYPE, 0, 'PROCEDURE', 1, 'FUNCTION', 3, 'TYPESET') PSM_TYPE,
        PROC_NAME PSM_NAME,
        DECODE(STATUS, 0, 'VALID', 'INVALID') STATUS
FROM    SYSTEM_.SYS_USERS_ A,
        SYSTEM_.SYS_PROCEDURES_ B
WHERE A.USER_ID = B.USER_ID AND A.USER_NAME <> 'SYSTEM_' ;

Altibase V5#

SELECT  A.USER_NAME,
        DECODE(OBJECT_TYPE, 0, 'PROCEDURE', 1, 'FUNCTION', 3, 'TYPESET') PSM_TYPE,
        PROC_NAME PSM_NAME,
        DECODE(STATUS, 0, 'VALID', 'INVALID') STATUS,
        TO_CHAR(B.LAST_DDL_TIME, 'YYYY-MM-DD HH:MI:SS') LAST_DDL_TIME
FROM    SYSTEM_.SYS_USERS_ A,
        SYSTEM_.SYS_PROCEDURES_ B
WHERE A.USER_ID = B.USER_ID AND A.USER_NAME <> 'SYSTEM_' ;

View#

This query returns the object information about a view.

Column Name Description
USER_NAME the object owner
VIEW_NAME the type of PSM
STATUS the status of PSM
LAST_DDL_TIME the last DDL occured time

Altibase V4#

SELECT  A.USER_NAME,
        B.TABLE_NAME VIEW_NAME,
        DECODE(STATUS, 0, 'VALID', 'INVALID') STATUS
FROM    SYSTEM_.SYS_USERS_ A,
        SYSTEM_.SYS_TABLES_ B,
        SYSTEM_.SYS_VIEWS_ C
WHERE   A.USER_ID = B.USER_ID
        AND B.TABLE_ID = C.VIEW_ID
        AND B.TABLE_TYPE = 'V' ;

Altibase V5#

SELECT  A.USER_NAME,
        B.TABLE_NAME VIEW_NAME,
        DECODE(STATUS, 0, 'VALID', 'INVALID') STATUS,
        TO_CHAR(B.LAST_DDL_TIME, 'YYYY-MM-DD HH:MI:SS') LAST_DDL_TIME
FROM    SYSTEM_.SYS_USERS_ A,
        SYSTEM_.SYS_TABLES_ B,
        SYSTEM_.SYS_VIEWS_ C
WHERE   A.USER_ID = B.USER_ID
        AND B.TABLE_ID = C.VIEW_ID
        AND B.TABLE_TYPE = 'V' ;

System Privileges#

This query returns the object information about system privileges.

Column Name Description
GRANTEE the grantee
GRANTOR the granter
PRIV_NAME the name of 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 B.GRANTEE_ID = A.USER_ID
        AND B.GRANTOR_ID = C.USER_ID
        AND B.PRIV_ID = D.PRIV_ID ;

Object Privileges#

This query returns the information about object privileges.

Column Name Description
GRANTEE the grantee
GRANTOR the granter
OBJECT_OWNER the object owner
OBJECT_NAME the object name
OBJECT_TYPE the object type
PRIV_NAME the name of privilege
GRANTABLE is grant-able
SELECT  A.USER_NAME GRANTEE,
        C.USER_NAME GRANTOR,
        F.USER_NAME OBJECT_OWNER,
        E.TABLE_NAME OBJECT_NAME,
        E.TABLE_TYPE OBJECT_TYPE,
        REPLACE(D.PRIV_NAME, '_', ' ') PRIV_NAME,
        DECODE(B.WITH_GRANT_OPTION, 0, 'NO', 'YES') GRANTABLE
FROM    SYSTEM_.SYS_USERS_ A,
        SYSTEM_.SYS_GRANT_OBJECT_ B,
        SYSTEM_.SYS_USERS_ C,
        SYSTEM_.SYS_PRIVILEGES_ D,
        SYSTEM_.SYS_TABLES_ E,
        SYSTEM_.SYS_USERS_ F
WHERE   C.USER_NAME <> 'SYSTEM_'
        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 = E.TABLE_ID
        AND E.USER_ID = F.USER_ID
ORDER BY GRANTEE, GRANTOR, OBJECT_OWNER, OBJECT_TYPE, OBJECT_NAME, PRIV_NAME ;

Constraint list#

This query returns the information about object constraints.

Column Name Description
USER_NAME the object owner
OBJECT_NAME the object name
OBJECT_TYPE the object type
CONST_NAME the constraint name
CONST_type the constraint type
COLUMN_NAME the column name
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, 'FK', 1, 'NOT NULL', 2, 'UNIQUE', 3, 'PK', 4, 'NULL', 5, 'TIMESTAMP', 6, 'LOCAL UNIQUE') CONST_TYPE,
        COLUMN_NAME
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_' ;

Primary Key, Foreign Key, Unique Constraint#

This query returns the information about object constraints.

Column Name Description
USER_NAME the object owner
TABLE_NAME the table name
CONST_TYPE the constraint type
CONST_NAME the constraint name
INDEX_NAME the index name
R_TABLE the referenced table
R_INDEX the referenced index
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 ;

Index column list#

This query returns the information about index columns.

Column Name Description
USER_NAME the object owner
TABLE_NAME the table name
INDEX_NAME the index name
COLUMN_NAME the column name
COL_ORDER column order (when composite index)
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 ;

Index information#

This query returns the information about an index.

Column Name Description
USER_NAME the object owner
INDEX_NAME the index name
INDEX_ID the index name
TABLE_NAME the table name
TBS_NAME the tablespace which this object belongs to
IS_UNIQUE unique index or not
COLUMN_CNT the number of columns
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 ;

SQL about Replication#

Replication Sender#

This query returns the replication sender status.

Column Name Description
REP_NAME the name of replication object
REMOTE_IP the IP address of remote replicated server
REMOTE_REP_PORT the port number of remote replicated server
STATUS the status of replication sender
NETWORK check whether network error or not
XSN XLOG Sequence Number: the final position in a log file from which logs were transmitted by the replication sender
SELECT  REP_NAME,
        PEER_IP REMOTE_IP,
        PEER_PORT REMOTE_REP_PORT,
        DECODE(STATUS, 0, 'STOP', 1, 'RUN', 2, 'RETRY') AS STATUS,
        DECODE(NET_ERROR_FLAG, 0, 'OK', 'ERROR') AS NETWORK,
        XSN
FROM V$REPSENDER ;

Replication Receiver#

Column Name Description
REP_NAME the name of replication object
REMOTE_IP the IP address of remote replicated server
REMOTE_REP_PORT the port number of remote replicated server
APPLY_XSN applied XLOG Sequence Number: the final position in a log file from which logs were applied by the replication receiver
SELECT  REP_NAME,
          PEER_IP REMOTE_IP,
          PEER_PORT REMOTE_REP_PORT,
          APPLY_XSN
FROM V$REPRECEIVER ;

Replication gap#

Column Name Description
REP_NAME the name of replication object
REMOTE_IP the IP address of remote replicated server
REMOTE_REP_PORT the port number of remote replicated server
APPLY_XSN applied XLOG Sequence Number: the final position in a log file from which logs were applied by the replication receiver
SELECT  REP_NAME,
            REP_SN,
            REP_LAST_SN,
            REP_GAP,
            READ_FILE_NO,
            START_FLAG
FROM    V$REPGAP ;

Replication status#

Column Name Description
REP_NAME the name of replication object
REMOTE_IP the IP address of remote replicated server
REP_GAP the interval between the log record that was most recently written due to a transaction on the local server and the log record that is currently being sent by the replication Sender thread.
RESTART_XSN the XSN from which the Sender thread must begin sending logs when replication is started.
SENDER the sender status
RECEIVER the receiver status
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 ;

Log buffer or file status occupied by an unsent XLOG#

This query shows that the logbuffer/logfile status which is occupied by an unsent XLOG.

Altibase V4#

Icon

ALTIBASE V4 does not support this feature.

Altibase V5#

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 ;

Replication table list#

The list of tables that participate in a replication.

Column Name Description
REP_NAME the name of replication object
LOCAL_TABLE the local table name
REMOTE_TABLE the remote table name
SELECT  REPLICATION_NAME REP_NAME,
        LOCAL_USER_NAME||'.'||LOCAL_TABLE_NAME LOCAL_TABLE,
        REMOTE_USER_NAME||'.'||REMOTE_TABLE_NAME REMOTE_TABLE
FROM    SYSTEM_.SYS_REPL_ITEMS_
ORDER BY 1, 2 ;

SQL about Sessions#

Total number of sessions#

This query returns the number of sessions which are currently established.

SELECT  COUNT(*) TOTAL_SESSION_CNT
FROM    V$SESSION ;

Session Information#

This query returns the information about a session.

Column Name Description
SESSSION_ID the unique identifier of the session
USER_NAME the database user name
CLIENT_IP the client IP address
CLIENT_APP_INFO the type of client application not supported in Altibase 4
CLIENT_PID the client process identifier
SESSION_STATE the status of the session
AUTOCOMMIT the autocommit mode
LOGIN_TIME the time when the session is established
IDLE_TIME the time when the session has been idle
CURRENT_QUERY the query that the session has last executed not supported in Altibase 4

Altibase 4#

SELECT  A.ID SESSION_ID,
        A.DB_USERNAME USER_NAME,
        REPLACE2(REPLACE2(A.COMM_NAME, 'SOCKET-', NULL), '-SERVER', NULL) CLIENT_IP,
        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 / (246060), 'MM/DD HH:MI:SS')) LOGIN_TIME,
        DECODE(A.IDLE_START_TIME, 0, '-', TO_CHAR(TO_DATE('1970010109', 'YYYYMMDDHH') + A.IDLE_START_TIME / (246060), 'MM/DD HH:MI:SS')) IDLE_TIME
FROM    V$SESSION A ;

Altibase 5#

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,
        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 / (246060), 'MM/DD HH:MI:SS')) LOGIN_TIME,
        DECODE(A.IDLE_START_TIME, 0, '-', TO_CHAR(TO_DATE('1970010109', 'YYYYMMDDHH') + A.IDLE_START_TIME / (246060), '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 ;

Session information connected as SYSDBA#

This query returns the information about a session which connects as SYSDBA.

Column Name Description
SESSSION_ID the unique identifier of the session
USER_NAME the database user name
CLIENT_IP the client IP address
CLIENT_APP_INFO the type of client application not supported in Altibase 4
CLIENT_PID the client process identifier
SESSION_STATE the status of the session
AUTOCOMMIT the autocommit mode
LOGIN_TIME the time when the session is established
IDLE_TIME the time when the session has been idle
CURRENT_QUERY the query that the session last executed not supported in Altibase 4

Altibase 4#

SELECT  A.ID SESSION_ID,
        A.DB_USERNAME USER_NAME,
        REPLACE2(REPLACE2(A.COMM_NAME, 'SOCKET-', NULL), '-SERVER', NULL) CLIENT_IP,
        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 / (246060), 'MM/DD HH:MI:SS')) LOGIN_TIME,
        DECODE(A.IDLE_START_TIME, 0, '-', TO_CHAR(TO_DATE('1970010109', 'YYYYMMDDHH') + A.IDLE_START_TIME / (246060), 'MM/DD HH:MI:SS')) IDLE_TIME
FROM    V$SESSION A
WHERE   A.SYSDBA_FLAG = 1;

Altibase 5#

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,
        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 / (246060), 'MM/DD HH:MI:SS')) LOGIN_TIME,
        DECODE(A.IDLE_START_TIME, 0, '-', TO_CHAR(TO_DATE('1970010109', 'YYYYMMDDHH') + A.IDLE_START_TIME / (246060), '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;

SQL about Statements#

Total number of statements#

This query returns the number of statements that currently are established.

SELECT  COUNT(*) STATEMENT_CNT
FROM    V$STATEMENT;

Statement information#

This query returns the information about statements, sorted in descending order by the execution time.

Column Name Description
SESSSION_ID the unique identifier of the session
STMT_ID the unique identifier of the statement in the session
TX_ID the transaction identifier if the statement participates in a transaction
PREPARE_TIME the time taken to perform preparing the statement. (microsecond)
FETCH_TIME the time taken to perform a fetch operation (microsecond)
EXECUTE_TIME the time taken to execute the statement (microsecond)
TOTAL_TIME the total elapsed time
EXECUTE_FLAG set 1 if the statement is currently executed
LAST_START_TIME the time when the statement last started
QUERY the query string
SELECT  SESSION_ID,
        ID STMT_ID,
        TX_ID,
        (PARSE_TIME+VALIDATE_TIME+OPTIMIZE_TIME) PREPARE_TIME,
        FETCH_TIME,
        EXECUTE_TIME,
        TOTAL_TIME,
        EXECUTE_FLAG,
        DECODE(LAST_QUERY_START_TIME, 0, '-', TO_CHAR(TO_DATE('1970010109', 'YYYYMMDDHH') + LAST_QUERY_START_TIME / (246060), 'MM/DD HH:MI:SS')) LAST_START_TIME,
        NVL(LTRIM(QUERY), 'NONE') QUERY
FROM    V$STATEMENT
ORDER BY EXECUTE_TIME DESC ;

The number of currently running statements#

This query returns the number of statements that are currently executing.

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

Information about currently running statements#

This query returns the statement information about those that are currently executing.

Column Name Description
SESSSION_ID the unique identifier of the session
STMT_ID the unique identifier of statement in the session
TX_ID the transaction identifier if the statement participate in a transaction
PREPARE_TIME the time taken to perform preparing the statement. (microsecond)
FETCH_TIME the time taken to perform a fetch operation (microsecond)
EXECUTE_TIME the time taken to execute the statement (microsecond)
TOTAL_TIME the total elapsed time
EXECUTE_FLAG set 1 if the statement is currently executed
LAST_START_TIME the time when the statement last started
QUERY the query string
SELECT  SESSION_ID,
        ID STMT_ID,
        TX_ID,
        (PARSE_TIME+VALIDATE_TIME+OPTIMIZE_TIME) PREPARE_TIME,
        FETCH_TIME,
        EXECUTE_TIME,
        TOTAL_TIME,
        DECODE(LAST_QUERY_START_TIME, 0, '-', TO_CHAR(TO_DATE('1970010109', 'YYYYMMDDHH') + LAST_QUERY_START_TIME / (246060), 'MM/DD HH:MI:SS')) LAST_START_TIME,
        NVL(LTRIM(QUERY), 'NONE') QUERY
FROM    V$STATEMENT
WHERE   EXECUTE_FLAG = 1
ORDER BY EXECUTE_TIME DESC ;

Long running query ( over 10 seconds)#

This query returns the statement information about queries that are considered long running. (the queries with elapsed time over 10 seconds)

Column Name Description
SESSSION_ID the unique identifier of the session
STMT_ID the unique identifier of statement in the session
TX_ID the transaction identifier if the statement participate in a transaction
PREPARE_TIME the time taken to perform preparing statement. (microsecond)
FETCH_TIME the time taken to perform a fetch operation (microsecond)
EXECUTE_TIME the time taken to execute statement (microsecond)
TOTAL_TIME the total elapsed time
EXECUTE_FLAG set 1 if the statement is currently executed
LAST_START_TIME the time when the statement last started
QUERY the query string
SELECT  SESSION_ID,
        ID STMT_ID,
        TX_ID,
        (PARSE_TIME+VALIDATE_TIME+OPTIMIZE_TIME) PREPARE_TIME,
        FETCH_TIME,
        EXECUTE_TIME,
        TOTAL_TIME,
        DECODE(LAST_QUERY_START_TIME, 0, '-', TO_CHAR(TO_DATE('1970010109', 'YYYYMMDDHH') + LAST_QUERY_START_TIME / (246060), 'MM/DD HH:MI:SS')) LAST_START_TIME,
        NVL(LTRIM(QUERY), 'NONE') QUERY
FROM    V$STATEMENT
WHERE   EXECUTE_FLAG = 1 AND EXECUTE_TIME/1000000 > 10
ORDER BY EXECUTE_TIME DESC ;

Long running transaction's last statement information (over 1 minute)#

In the case of long running transactions in Altibase, this query shows the information for the last query of a transaction with an elapsed time over 1 minute.

Column Name Description
SESSSION_ID the unique identifier of the session
CLIENT_IP the client IP address
CLIENT_PID the client process identifier
UTRANS_TIME the time taken during the transaction. (microsecond)
EXECUTE_TIME the time taken to execute statement (microsecond)
TOTAL_TIME the total elapsed time
LAST_START_TIME the time when the statement last started
QUERY the query string
SELECT  ST.SESSION_ID,
        SS.COMM_NAME CLIENT_IP,
        SS.CLIENT_PID,
        (BASE_TIME - TR.FIRST_UPDATE_TIME) AS UTRANS_TIME,
        EXECUTE_TIME,
        TOTAL_TIME,
        DECODE(LAST_QUERY_START_TIME, 0, '-', TO_CHAR(TO_DATE('1970010109', 'YYYYMMDDHH') + LAST_QUERY_START_TIME / (246060), '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) > 60
ORDER BY UTRANS_TIME DESC ;

Information about a query running a FULL SCAN#

This query shows the information about a query that is running a FULL SCAN. You need to be careful and check the SQL if there is a FULL SCAN plan node in the statement.

Column Name Description
SESSSION_ID the unique identifier of the session
CLIENT_IP the client IP address
CLIENT_PID the client process identifier
LAST_START_TIME the time when the statement last started
PREPARE_TIME the time taken to perform preparing statement. (microsecond)
FETCH_TIME the time taken to perform a fetch operation (microsecond)
EXECUTE_TIME the time taken to execute statement (microsecond)
TOTAL_TIME the total elapsed time
QUERY the query string
SELECT  SESSION_ID,
        S.COMM_NAME CLIENT_IP,
        S.CLIENT_PID,
        DECODE(LAST_QUERY_START_TIME, 0, '-', TO_CHAR(TO_DATE('1970010109', 'YYYYMMDDHH') + LAST_QUERY_START_TIME / (246060), 'MM/DD HH:MI:SS')) LAST_START_TIME,
        (PARSE_TIME+VALIDATE_TIME+OPTIMIZE_TIME) PREPARE_TIME,
        FETCH_TIME,
        EXECUTE_TIME,
        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 ;

SQL about Tablespaces#

The usage of a Memory tablespace#

This query returns the information about a memory tablespace.

Columns Description
TBS_ID the unique identifier of the tablespace
TBS_TYPE the type of the tablespace
TBS_NAME the name of the tablespace
MAX(M) the max size of the tablespace (Megabyte)
TOTAL(M) the total size of the tablespace (Megabyte)
ALLOC(M) the allocated size of the tablespace (Megabyte)
USED(M) actually used size of the tablespace (Megabyte)
USAGE(%) the tablespace usages. (USED(M) / MAX(M)) * 100. if MAX(M) is unlimited, it assumes that MAX(M) is MEM_MAX_DB_SIZE in altibase.properties.
STATE the state of the tablespace
AUTOEXTEND whether the tablespace can be extended automatically

Altibase V4#

Icon

Altibase V4 does not support User Memory Tablespaces. There's only one memory tablespace including system dictionary tablespace.

Altibase V5#

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', MAXSIZE) '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_SIZE100,2),
        ROUND((M.ALLOC_PAGE_COUNT-M.FREE_PAGE_COUNT)PAGE_SIZE/MAXSIZE100,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 ;

Total memory tablespace usage#

This query returns the total memory tablespace usage.

Columns Description
MAX(M) the max size of the tablespace (Megabyte)
TOTAL(M) the total size of the tablespace (Megabyte)
ALLOC(M) allocated size of the tablespace (Megabyte)
USED(M) actually used size of the tablespace (Megabyte)
USAGE(%) the tablespace usages. (USED(M) / MAX(M)) * 100. if MAX(M) is unlimited, it assumes that MAX(M) is MEM_MAX_DB_SIZE in altibase.properties.
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)321024)/MEM_MAX_DB_SIZE, 4)*100 'USAGE(%)'
FROM V$DATABASE ;

Disk Tablespace Usage#

This query returns the disk tablespace usage information.

Columns Description
TBS_ID the unique identifier of the tablespace
TBS_TYPE the type of the tablespace
TBS_NAME the name of the tablespace
MAX(M) the max size of the tablespace (Megabyte)
TOTAL(M) the total size of the tablespace (Megabyte)
ALLOC(M) allocated size of the tablespace (Megabyte)
AUTOEXTEND whether the tablespace can be extended automatically

Altibase V4#

SELECT  ID TBS_ID,
        DECODE(TYPE, 1, 'DISK_SYS_DATA', 2, 'DISK_USER_DATA', 3, 'DISK_SYS_TEMP', 4, 'DISK_USER_TEMP', 5, 'DISK_UNDO') TBS_TYPE,
        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(ALLOCATED_PAGE_COUNT * PAGE_SIZE / 1024 / 1024,2) 'ALLOC(M)',
        ROUND(ALLOCATED_PAGE_COUNT / D.MAX * 100, 2) 'USAGE(%)',
        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 ;

Altibase V5#

SELECT  ID TBS_ID,
        DECODE(TYPE, 3, 'DISK_SYS_DATA', 4, 'DISK_USER_DATA', 5, 'DISK_SYS_TEMP', 6, 'DISK_USER_TEMP', 7, 'DISK_UNDO') TBS_TYPE,
        NAME TBS_NAME,
        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_EXTENT_COUNTPAGE_COUNT_IN_EXTENT)  PAGE_SIZE)/1024/1024 FROM V$UDSEGS)+(SELECT (SUM(TOTAL_EXTENT_COUNTPAGE_COUNT_IN_EXTENT)  PAGE_SIZE)/1024/1024 FROM V$TSSEGS),2), ROUND(ALLOCATED_PAGE_COUNT * PAGE_SIZE / 1024 / 1024,2)) 'ALLOC(M)',
        DECODE(TYPE, 7, ROUND(((SELECT SUM(TOTAL_EXTENT_COUNTPAGE_COUNT_IN_EXTENT) FROM V$UDSEGS)+(SELECT SUM(TOTAL_EXTENT_COUNTPAGE_COUNT_IN_EXTENT) FROM V$TSSEGS)) / 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 ;

The datafile information#

This query returns the information about data files containing tablespaces.

Columns Description
TBS_NAME the name of the tablespace
FILE# the file number of datafiles containing the tablespace. It starts with 0.
DATAFILE_NAME the name of the datafile
ALLOC(M) allocated size of the datafile (Megabyte)
MAX(M) the max size of the datafile (Megabyte)
AUTOEXTEND can be extended automatically
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 ;

I/O statistics of datafiles#

This query returns I/O statistics for datafiles.

Altibase V4#

Icon

Altibase V4 does not support V$FILESTAT performance view to retrieve the I/O statistics of datafiles.

Altibase V5#

SELECT  NAME TBS_NAME,
        A.PHYRDS PHY_READ,
        A.PHYWRTS PHY_WRITE,
        A.PHYRDS+A.PHYWRTS PHY_TOTAL,
        TRUNC(A.PHYRDS/READ_SUM*100,2) 'READ(%)',
        TRUNC(A.PHYWRTS/WRITE_SUM*100,2) 'WRITE(%)',
        TRUNC( (A.PHYRDS+A.PHYWRTS) / (READ_SUM+WRITE_SUM) * 100 , 2) 'TOTAL(%)',
        A.AVGIOTIM AVG_IO_TIME
FROM      V$FILESTAT A,
          V$DATAFILES B,
          (
                SELECT  SUM(PHYRDS) READ_SUM,
                                SUM(PHYWRTS) WRITE_SUM
                FROM V$FILESTAT
          ) C
WHERE   A.SPACEID = B.SPACEID
          AND A.FILEID = B.ID
          AND READ_SUM > 0
          AND WRITE_SUM > 0
ORDER BY A.PHYRDS+A.PHYWRTS DESC, ROWNUM DESC ;