The monitoring query introduced on this page can be used from the version that reflects BUG-31372.
- Altibase HDB version 188.8.131.52
- Altibase HDB version 184.108.40.206
- Altibase HDB version 220.127.116.11.3
- Altibase HDB version 6.1.1
- Altibase HDB version 6.3.1
To check the usage of disk tables and indexes
- ALTER TABLE table_name AGING and ALTER INDEX index_name AGING commands must be executed to get the correct usage.
- Unaging space is calculated as used, so if there are frequent deletes on a table, it may be calculated larger than the actual usage if aging is not performed.
- During the execution of the ALTER TABLE table_name AGING, ALTER INDEX index_name AGING command, the table is full-scanned while holding X LOCK on the table, so other operations on the table and indexes are waiting.
- TOTAL_USED_SIZE of v$segment is volatile temporary data. When the Altibase server is restarted, it is initialized to the total allocation size of the table and index, not the actual usage amount.
- v$segment query itself does not affect the database.
- Even if the table data is deleted with DELETE and USED, it does not decrease.
- To check the actual USED excluding FREE PAGE after DELETE and table_name AGING; must be executed.
While executing ALTER TABLE ~ AGINING;, the table is locked, so other requests for the table are put in waiting for state.
- Even if the table data is deleted with DELETE, the USED of the index does not decrease.
- To check the actual USED except FREE PAGE after DELETE, ALTER INDEX index_name AGING; must be executed.
When executing ALTER INDEX ~ AGINING, the table is locked. Therefore, other requests for the table are put in a waiting state, so be cautious when executing the ALTER INDEX ~ AGAING.
Reference - How to check the count of disk table and index
Error rendering macro 'code': Invalid value specified for parameter 'firstline'
set linesize 1024; set colsize 50; SELECT DECODE(T.IS_PARTITIONED, 'T', 'PARTITIONED TABLE CNT : '||PART_T.CNT, 'F', 'NON-PARTITIONED TABLE CNT : '||T.CNT) TABLE_COUNT FROM (SELECT IS_PARTITIONED , COUNT(*) CNT FROM V$DISKTBL_INFO D , SYSTEM_.SYS_TABLES_ T WHERE D.TABLE_OID = T.TABLE_OID GROUP BY IS_PARTITIONED) T , (SELECT COUNT(*) CNT FROM SYSTEM_.SYS_TABLE_PARTITIONS_ ) PART_T ;