Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

ColumnDescription
USER_NAMETable owner
TABLE_NAMETable name
PARTITION_NAMEThe 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_NAMETablespace 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)

...

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

...

ColumnDescription
INDEX_PARTITIONEDWhether 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.

...

ColumnDescription
TABLE_NAMETable name
PARTITIONED_TABLEWhen a partition table is created in a table, the name of the partition table is displayed. '-' For non-partitioned tables
TBS_NAMEShows 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.

...

ColumnDescription
TABLE_NAMETable name
PARTITIONED_TABLEWhen a partitioned table is created on a table, the name of the partition table is displayed. '-' For non-partitioned tables
INDEX_NAMEIndex name
PARTITIONED_INDEXWhen a partitioned index is created on an index, the partitioned index name is displayed. '-' For non-partitioned indexes
TBS_NAMEThe 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.

...

ColumnDescription
PARTITIONED_METHODShows how to partition the partition table.
PARTITION_ORDERFor hash partitions, this shows the order of the partitions.
ROW_MOVEMENTThis 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_VALUEFor 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_VALUEIn 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.
ACCESSShows 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
CREATEDThis is the time when the partition table was created. This column is available in Altibase 6.5.1 and later.
LAST_DDL_TIMEThis 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.
 ;

...

ColumnDescription
SYNONYM_OWNERThe 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_OWNERThe owner of the synonym target object
OBJECT_NAMEThe name of the synonym targe target object
LAST_DDL_TIMEThe last name DDL was executed on synonym

...

ColumnDescription
STATUSCompile status. INVALID requires compilation

PSM Creation Statement

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

View Creating Statement

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.

...

 

ColumnDescription
PACKAGE_TYPEPackage type. Whether it is a specification or a bodyPACKAGE Specification or PACKAGE Body.
STATUSCompile status. INVALID requires compilation.

PACKAGE Subprogram

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_' ;
  • Main Column Description

ColumnDescription
SUB_TYPECompile status. If 1, compile is required.
PARA_NAMESubprogram parameter name
PARA_ORDERSubprogram parameter sequence. Has 1 in the first case.
INOUT_TYPEParameter input/output
DATA_TYPEParameter data type
SIZEThe size of a parameter type
DEFAULT_VALThe default value of a parameter
Package

PACKAGE Creating Statement

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 ;

Trigger

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;
  • Main Column Description

ColumnDescription
TABLE_NAMETable name where the trigger is defined
IS_ENABLEWhether to triggerTRIGGER works.
EVENT_TIMETime to trigger
EVENT_TYPEType of event that triggers
GRANULARITYUnit that triggers

Job

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_;
  • Main Column Description

ColumnDescription
JOB_NAMEThe name of the JOB object
PROC_NAMEThe name of the procedure registered in JOB
NTERVAL, INTERVAL_TYPEPerformance cycle
STATEJOB progress status. If ING, it means that the procedure is currently being executed by the task scheduler.
EXEC_COUNTThe number of JOB executions
ERROR_CODEThe result of the last JOB execution
START_TIMEThe time when JOB first started executing
LAST_EXEC_TIMEThe time at which the JOB was last executed means the time that was most recently executed.
END_TIMEThe time at which the last JOB was executed.
IN_ENABLEWhether is the JOB execution is possible by the job scheduler

Database User

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;

Tablespace List

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;

 

 

 

 

...