...
Column | Description |
---|---|
USER_NAME | Table owner |
TABLE_NAME | Table name |
PARTITION_NAME | The name of the partitioned table if you created the partitioned table on the table. Memory partition tables are supported starting from 6.5.1. |
TBS_NAME | Tablespace name specified when creating the table |
ALLOC(M) | The total number of pages allocated from to the memory table |
USED(M) | The total of 'pages loaded with actual data' among the pages allocated by the memory table. For example, if the user executes a full DELETE on a memory table with an ALLOC size of 100M, ALLOC will not change to 100M, but USED will be close to zero. The user can reduce ALLOC by executing TRUNCATE. |
USAGE(%) | This is expressed as a percentage of the 'page loaded with actual data' among pages allocated to the memory table. (Ie USED / ALLOC) |
...
Description | |
---|---|
ALLOC(M) | The total number of pages allocated from to the memory table |
USED(M) | The total of 'pages loaded with actual data' among the pages allocated by the memory table. For example, if the user executes a full DELETE on a memory table with an ALLOC size of 100M, ALLOC will not change to 100M, but USED will be close to zero. The user can reduce ALLOC by executing TRUNCATE. |
USAGE(%) |
...
Column | Description |
---|---|
INDEX_PARTITIONED | Whether the index is a partition index. F: Non-partition index T: Partition index memory(Memory Partition partition index is supported in Altibase 6.5.1 or later.) |
SIZE(MB) | The user can find the index size by the number of nodes attached to the index. One page is used for every 15 index nodes, and the page size is 32 KB. There is a difference in the number of slots available for each node according to the Altibase server version. For this reason, even if the data is the same, the index size may differ depending on the version. |
...
Column | Description |
---|---|
TABLE_NAME | Table name |
PARTITIONED_TABLE | When a partition table is created in a table, the name of the partition table is displayed. '-' For non-partitioned tables |
TBS_NAME | Shows the tablespace specified when creating the table. If a tablespace is specified for a partitioned table, the tablespace name specified in the partitioned table is displayed. |
MAX(MB) | The maximum size of the tablespace to which the table belongs |
ALLOC(MB) | The total size of the table allocated |
USED(MB) | Refers to the size of the table allocated data that contains data. When DELETE the data, the space DELETE can be reused, but in the case of a disk table, unlike a memory table, USED does not decrease even if DELETE is performed. To check the actual USED except for the space that can be reused as DELETE, AGING must be performed. Ex) ALTER TABLE table_name AGING; ALTER TABLE ~ AGING; Because it holds the X lock on the table during execution, other requests for the table will be in a waiting state, so be careful when performing it. ALLOC and USED always have the same value unless AGING is performed. |
...
Column | Description |
---|---|
TABLE_NAME | Table name |
PARTITIONED_TABLE | When a partitioned table is created on a table, the name of the partition table is displayed. '-' For non-partitioned tables |
INDEX_NAME | Index name |
PARTITIONED_INDEX | When a partitioned index is created on an index, the partitioned index name is displayed. '-' For non-partitioned indexes |
TBS_NAME | The tablespace specified when creating the index. If a tablespace is specified in a partitioned index, the tablespace name specified in the partitioned index is displayed. |
MAX(MB) | The maximum size of the tablespace to which the index belongs |
ALLOC(MB) | The total size allocated from to the index |
USED(MB) | The size of the index allocated space that contains data. If DELETE is performed on the data, the space DELETE can be reused, but the USED of the index does not decrease even when DELETE is performed. To check the actual USED except for the space that can be reused as DELETE, AGING must be performed. Ex) ALTER INDEX index_name AGING; ALTER INDEX ~ AGING; During execution, because the X lock is held on the table, other requests for the table will be in a waiting state, so be careful when performing it. ALLOC and USED always have the same value unless AGING is performed. |
...
Column | Description |
---|---|
PARTITIONED_METHOD | Shows how to partition the partition table. |
PARTITION_ORDER | For hash partitions, this shows the order of the partitions. |
ROW_MOVEMENT | This shows whether the record of a partitioned table is updated, and if the value of the column Shows whether it is allowed to automatically move the record to another partition when the partitioned table record is updated and the column value corresponding to the partition key is changed to a value belonging to another partition, the record is automatically moved to another partition. T is allowed to move and F is not allowed. This option is set when creating a partition table. |
PARTITION_MIN_VALUE | For range partitions, the minimum reference value of the partition table is displayed as a string. For a list partition, PARTITION_MIN_VALUE and PARTITION_MAX_VALUE are the same. In the case of a hash partition, both PARTITION_MIN_VALUE and PARTITION_MAX_VALUE are null. |
PARTITION_MAX_VALUE | In the case of range partition, the maximum reference value of the partition table is displayed as a string. For a list partition, PARTITION_MIN_VALUE and PARTITION_MAX_VALUE are the same. In the case of a hash partition, both PARTITION_MIN_VALUE and PARTITION_MAX_VALUE are null. |
ACCESS | Shows the access mode for data in the partition table. This column is available in Altibase 6.5.1 and later. R: Data read-only mode W: Data read/write mode (basic mode) A: Data read/add mode |
CREATED | This is the time when the partition table was created. This column is available in Altibase 6.5.1 and later. |
LAST_DDL_TIME | This is the last time DDL was executed on the partition table. This column is available in Altibase 6.5.1 and later. |
...
Code Block |
---|
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 -- If the user wants to excluseexclude PUBLIC synonym, remove comments and use them. ; |
...
Column | Description |
---|---|
SYNONYM_OWNER | The database user who created the synonym. When this value is 'PUBLIC', it means synonym created by default so that anyone can use it when creating a database. |
OBJECT_OWNER | The owner of the synonym target object |
OBJECT_NAME | The name of the synonym targe target object |
LAST_DDL_TIME | The last name DDL was executed on synonym |
...
Description | |
---|---|
STATUS |
If When the PSM name is entered in the PROC_NAME condition, the PSM creation statement is displayed.
Code Block |
---|
SELECT PARSE FROM SYSTEM_.SYS_PROC_PARSE_ WHERE PROC_OID = (SELECT PROC_OID FROM SYSTEM_.SYS_PROCEDURES_ WHERE PROC_NAME = 'psm_name') -- Enter the pasmPSM name that want to be searched ORDER BY SEQ_NO ; |
...
This query shows information about the VIEW object.
Code Block |
---|
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
, 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 1=1
AND A.USER_ID = B.USER_ID
AND B.TABLE_ID = C.VIEW_ID
AND B.TABLE_TYPE = 'V' ; |
When a view name is entered in the TABLE_NAME condition, the VIEW creation statement is displayed.
Code Block |
---|
SELECT PARSE
FROM SYSTEM_.SYS_VIEW_PARSE_
WHERE VIEW_ID = (SELECT TABLE_ID
FROM SYSTEM_.SYS_TABLES_
WHERE TABLE_NAME = 'view_name') -- Enter the name of the view you want to view.
ORDER BY SEQ_NO ; |
Package
This shows information about the package.
...
Description | |
---|---|
PACKAGE_TYPE | Package type. Whether it is a specification or a bodyPACKAGE Specification or PACKAGE Body. |
STATUS |
This shows information about subprograms (stored procedures and store functions) included in the package.
Code Block |
---|
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_' ; |
Description | |
---|---|
SUB_TYPE | Compile status. If 1, compile is required. |
PARA_NAME | Subprogram parameter name |
PARA_ORDER | Subprogram parameter sequence. Has 1 in the first case. |
INOUT_TYPE | Parameter input/output |
DATA_TYPE | Parameter data type |
SIZE | The size of a parameter type |
DEFAULT_VAL |
If When the package name is entered in the PACKAGE_NAME condition, the PACKAGE creation statement of the package is displayed. This is available in Altibase 6.3.1 and later.
Code Block |
---|
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') -- Enter the name of the package that wanted to be viewed. ORDER BY SEQ_NO ; |
This shows trigger creation information.
Code Block |
---|
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; |
Description | |
---|---|
TABLE_NAME | Table name where the trigger is defined |
IS_ENABLE | Whether to triggerTRIGGER works. |
EVENT_TIME | Time to trigger |
EVENT_TYPE | Type of event that triggers |
GRANULARITY |
This shows information about the job object.
Code Block |
---|
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 -- Used in Altibase 6.3.1 after deletion. FROM SYSTEM_.SYS_JOBS_; |
Column | Description |
---|---|
JOB_NAME | The name of the JOB object |
PROC_NAME | The name of the procedure registered in JOB |
NTERVAL, INTERVAL_TYPE | Performance cycle |
STATE | JOB progress status. If ING, it means that the procedure is currently being executed by the task scheduler. |
EXEC_COUNT | The number of JOB executions |
ERROR_CODE | The result of the last JOB execution |
START_TIME | The time when JOB first started executing |
LAST_EXEC_TIME | The time at which the JOB was last executed means the time that was most recently executed. |
END_TIME | The time at which the last JOB was executed. |
IN_ENABLE | Whether is the JOB execution is possible by the job scheduler |
This is a statement to search database user information.
Code Block |
---|
SELECT USER_ID, USER_NAME, TBS.NAME 'DEFAULT_TBS', CREATED FROM SYSTEM_.SYS_USERS_ U, V$TABLESPACES TBS WHERE U.DEFAULT_TBS_ID = TBS.ID; |
This is a statement to search the tablespace list.
Code Block |
---|
SELECT ID 'TBS_ID' , NAME 'TBS_NAME' , DECODE(TYPE, 0, 'SYSTEM_TBS', 1, 'SYSTEM_TBS', 2, 'USER_MEM_TBS', 3, 'SYSTEM_TBS', 4, 'USER_DISK_TBS', 5, 'SYSTEM_TBS', 6, 'USER_DISK_TEMP', 7, 'SYSTEM_UNDO', 8, 'USER_VOL_TBS') 'TBS_TYPE' FROM V$TABLESPACES; |
...