Skip to content

Disk table and index usage#

Overview#

This document summarizes disk table and index usage retrieve queries by version.

The user can use the following two methods for disk table usage. This page provides queries using v$segment(x$segment).

  • v$segment: A method of calculating physical pages while full-scanning a table
  • v$usage: A sampling method using statistical information

Altibase 4.3.9.x#

This is an Altibase version 4.3.9 of the disk table and index usage query.

This query can also be used in Altibase version 5.1.1.

Disk table usage query#

Altibase 4.3.9 Disk Table Usage Query

set linesize 1024;
set colsize 20;
SELECT U.USER_NAME 'USER_NAME'                                                                                      -- Database user
     , TBL.TABLE_NAME 'TABLE_NAME'                                                                                  -- Table name
     , TBS.NAME 'TBS_NAME'                                                                                          -- Name of the tablespace to which the table belongs
     , TO_CHAR((TBS.TOTAL_PAGE_COUNT * TBS.PAGE_SIZE)/1024 , '999,999,999') 'TBS_MAX(KB)'                           -- Maximum size of tablespace
     , TO_CHAR((TBS.A_EXTENT_PAGE_COUNT  TBS.PAGE_SIZE  SEG.EXTENT_TOTAL_COUNT)/1024, '999,999,999') 'ALLOC(KB)'  -- The total size allocated from the table
     , TO_CHAR((TBS.A_EXTENT_PAGE_COUNT  TBS.PAGE_SIZE  SEG.EXTENT_FULL_COUNT)/1024, '999,999,999') 'USED(KB)'    -- Actual usage of the table (data usage)
     , TO_CHAR((((TBS.A_EXTENT_PAGE_COUNT  TBS.PAGE_SIZE  SEG.EXTENT_TOTAL_COUNT)/(TBS.TOTAL_PAGE_COUNT  TBS.PAGE_SIZE))100), '99.9') 'USAGE(%)' -- Utilization based on the maximum size of the tablespace
  FROM X$SEGMENT SEG
     , SYSTEM_.SYS_TABLES_ TBL
     , V$TABLESPACES TBS
     , SYSTEM_.SYS_USERS_ U
 WHERE SEG.TABLE_OID = TBL.TABLE_OID
   AND SEG.SPACE_ID = TBL.TBS_ID
   AND SEG.SPACE_ID = TBS.ID
   AND TBL.USER_ID = U.USER_ID
   AND TBL.TABLE_TYPE = 'T'
   AND SEG.SEGMENT_TYPE = 6
 ORDER BY USER_NAME, TABLE_NAME
 ;

Example of output

USER_NAME             TABLE_NAME            TBS_NAME              TBS_MAX(KB)      ALLOC(KB)        USED(KB)         USAGE(%)
----------------------------------------------------------------------------------------------------------------------------------------------
SYS                   CUSTOMER              USER_DATA                2,097,152              256                0       0.0
SYS                   DEPARTMENT            SYS_TBS_DATA               254,976              256                0        .1
SYS                   DISK_T                USER_DATA                2,097,152          851,200          850,944      40.6
SYS                   EMPLOYEE              SYS_TBS_DATA               254,976              256                0        .1
SYS                   ORDERS                USER_DATA                2,097,152              256                0       0.0
5 rows selected.

Disk index usage query#

Altibase 4.3.9 Disk Index Usage Query

set linesize 1024;
set colsize 20;
SELECT U.USER_NAME AS 'USER_NAME'           -- Database user
     , TBL.TABLE_NAME AS 'TABLE_NAME'       -- Table name
     , IDX.INDEX_NAME AS 'INDEX_NAME'       -- Index name
     , TBS.NAME AS 'TBS_NAME'               -- Tablespace name to which the index belongs
     , TO_CHAR((TBS.TOTAL_PAGE_COUNT * TBS.PAGE_SIZE)/1024 , '999,999,999') AS 'TBS_MAX(KB)'                            -- Maximum size of table space
     , TO_CHAR((TBS.A_EXTENT_PAGE_COUNT  TBS.PAGE_SIZE  SEG.EXTENT_TOTAL_COUNT)/1024, '999,999,999') AS 'ALLOC(KB)'   -- Total size allocated from the index
     , TO_CHAR((TBS.A_EXTENT_PAGE_COUNT  TBS.PAGE_SIZE  SEG.EXTENT_FULL_COUNT )/1024, '999,999,999') AS 'USED(KB)'    -- Actual usage of the index (data usage)
    , TO_CHAR((((TBS.A_EXTENT_PAGE_COUNT  SEG.EXTENT_TOTAL_COUNT)/TBS.TOTAL_PAGE_COUNT)100), '99.9') AS 'USAGE(%)'    -- Utilization based on the maximum size of the tablespace
 FROM  X$SEGMENT SEG
     , V$INDEX I
     , SYSTEM_.SYS_INDICES_ IDX
     , SYSTEM_.SYS_TABLES_ TBL
     , V$TABLESPACES TBS
     , SYSTEM_.SYS_USERS_ U
 WHERE SEG.TABLE_OID = I.TABLE_OID
   AND SEG.SEGMENT_DESC = I.INDEX_SEG_DESC
   AND I.INDEX_ID = IDX.INDEX_ID
   AND IDX.TABLE_ID = TBL.TABLE_ID
   AND SEG.SPACE_ID = IDX.TBS_ID
   AND SEG.SPACE_ID = TBS.ID
   AND IDX.USER_ID = U.USER_ID
   AND TBL.TABLE_TYPE = 'T'
   AND SEG.SEGMENT_TYPE = 5
 ORDER BY U.USER_NAME, TBL.TABLE_NAME, IDX.INDEX_NAME
   ;

Example of output

USER_NAME             TABLE_NAME            INDEX_NAME            TBS_NAME              TBS_MAX(KB)      ALLOC(KB)        USED(KB)         USAGE(%)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYS                   CUSTOMER              __SYS_IDX_ID_136      USER_DATA                2,097,152              256                0       0.0
SYS                   DEPARTMENT            DEP_IDX1              USER_IDX                 2,097,152              256                0       0.0
SYS                   DEPARTMENT            __SYS_IDX_ID_134      SYS_TBS_DATA               254,976              256                0        .1
SYS                   DISK_T                DISK_T_IDX_01         USER_IDX                 2,097,152          159,744          159,488       7.6
SYS                   EMPLOYEE              EMP_IDX1              USER_IDX                 2,097,152              256                0       0.0
SYS                   EMPLOYEE              __SYS_IDX_ID_135      SYS_TBS_DATA               254,976              256                0        .1
SYS                   ORDERS                ODR_IDX1              USER_IDX                 2,097,152              256                0       0.0
SYS                   ORDERS                __SYS_IDX_ID_137      USER_DATA                2,097,152              256                0       0.0
8 rows selected.

Reference - How to check the number of disk tables and indexes#

Disk Table count query

set linesize 1024;
set colsize 30;
SELECT 'TABLE CNT : '||COUNT(*) TABLE_COUNT
  FROM V$DISKTBL_INFO D
     , SYSTEM_.SYS_TABLES_ T
 WHERE D.TABLE_OID = T.TABLE_OID ;

Disk Index count query

set linesize 1024;
set colsize 30;
SELECT 'INDEX CNT : '||COUNT(*) INDEX_COUNT
  FROM SYSTEM_.SYS_INDICES_
 WHERE TABLE_ID IN (SELECT TABLE_ID
                      FROM SYSTEM_.SYS_TABLES_ T
                         , V$DISKTBL_INFO D
                     WHERE T.TABLE_OID = D.TABLE_OID);

Altibase 5.1.5.x#

  • This is a disk table and index usage query for Altibase version 5.1.5.
  • In Altibase 5, due to a change in the structure of the disk table, only the size allocated to the table can be known and the actual usage cannot be checked.
  • This means that data cannot be calculated except for the free space that occurred after DELETE.
  • From Altibase 5.3.3.33, 5.3.5.15, 5.5.1.0.3, it has been improved so that the usage can be checked.

Disk table#

Altibase 5.1.5 Disk Table Usage Query

set linesize 1024;
set colsize 30;
SELECT U.USER_NAME USER_NAME                                                                                                    -- Database user
     , DECODE(TBL.IS_PARTITIONED, 'T', 'PARTITIONED', 'F', 'NON-PARTITIONED') PARTITIONED                                       -- If a partitioned table, PARTITIONED a non-partitioned, then NON-PARTITIONED
     , TBL.TABLE_NAME TABLE_NAME                                                                                                -- Table name
     , DECODE(TBL.IS_PARTITIONED, 'T', TBL.PARTITION_NAME, 'F', '-') PARTITIONED_TABLE                                          -- Partitioned table name
     , TBS.NAME TABLESPACE_NAME                                                                                                 -- Tablespace
     , TO_CHAR((D.MAX * TBS.PAGE_SIZE)/1024, '999,999,999') 'MAX(KB)'                                                           -- Maximum size of the tablespace to which the table belongs
     , TO_CHAR((TBS.EXTENT_PAGE_COUNT  TBS.PAGE_SIZE  SEG.EXTENT_TOTAL_COUNT)/1024, '999,999,999') 'ALLOC(KB)'                -- Total size allocated to date
     , TO_CHAR((((TBS.EXTENT_PAGE_COUNT  TBS.PAGE_SIZE  SEG.EXTENT_TOTAL_COUNT)/(D.MAXTBS.PAGE_SIZE))100), '99.99') 'USAGE(%)' -- Percentage of utilization compared to the maximum size of the tablespace
  FROM (SELECT TBL.USER_ID
             , DECODE(TBL.IS_PARTITIONED, 'F', TBL.TABLE_OID, 'T', PT.PARTITION_OID) TABLE_OID
             , TBL.TABLE_NAME
             , PT.PARTITION_NAME
             , DECODE(TBL.IS_PARTITIONED, 'F', TBL.TBS_ID, 'T', PT.TBS_ID) TBS_ID
             , TBL.IS_PARTITIONED
          FROM SYSTEM_.SYS_TABLES_ TBL LEFT OUTER JOIN SYSTEM_.SYS_TABLE_PARTITIONS_ PT ON TBL.TABLE_ID = PT.TABLE_ID
       ) TBL
     , V$SEGMENT SEG
     , SYSTEM_.SYS_USERS_ U
     , V$TABLESPACES TBS
     , (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 1=1
   AND SEG.SEGMENT_TYPE = 'TABLE'  / 'TABLE' :, 'INDEX' : /
   AND SEG.TABLE_OID = TBL.TABLE_OID
   AND U.USER_ID = TBL.USER_ID
   AND D.SPACEID = TBL.TBS_ID
   AND TBS.ID = TBL.TBS_ID
 ORDER BY USER_NAME, PARTITIONED, TABLE_NAME, PARTITIONED_TABLE
;

Example of output

USER_NAME             PARTITIONED      TABLESPACE_NAME       TABLE_NAME            PARTITIONED_TABLE     MAX(KB)          ALLOC(KB)        USAGE(%)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYS                   NON-PARTITIONED  SYS_TBS_DISK_DATA     CUSTOMER              -                        2,097,152              256        .01
SYS                   NON-PARTITIONED  SYS_TBS_DISK_DATA     DEPARTMENT            -                        2,097,152              256        .01
SYS                   NON-PARTITIONED  SYS_TBS_DISK_DATA     EMPLOYEE              -                        2,097,152              256        .01
SYS                   NON-PARTITIONED  SYS_TBS_DISK_DATA     GOODS                 -                        2,097,152              256        .01
SYS                   NON-PARTITIONED  USER_DATA             ORDERS                -                        2,097,152              256        .01
SYS                   PARTITIONED      PART_DATA             PART_T1               P1                         262,144              512        .20
SYS                   PARTITIONED      PART_DATA             PART_T1               P2                         262,144              512        .20
SYS                   PARTITIONED      PART_DATA             PART_T1               P3                         262,144           18,176       6.93
SYS                   PARTITIONED      PART_DATA             PART_T2               P201406                    262,144            9,472       3.61
SYS                   PARTITIONED      PART_DATA             PART_T2               P201407                    262,144           18,944       7.23
SYS                   PARTITIONED      PART_DATA             PART_T2               P201408                    262,144            4,864       1.86
SYS                   PARTITIONED      PART_DATA             PART_T2               P201512                    262,144            7,168       2.73
SYS                   PARTITIONED      PART_DATA_DEF         PART_T2               PMAX                       262,144            8,192       3.13
SYS                   PARTITIONED      SYS_TBS_DISK_DATA     RANGE2                DEF                      2,097,152              256        .01
SYS                   PARTITIONED      SYS_TBS_DISK_DATA     RANGE2                Q1_2014                  2,097,152            1,536        .07
SYS                   PARTITIONED      SYS_TBS_DISK_DATA     RANGE2                Q2_2014                  2,097,152              768        .04
SYS                   PARTITIONED      SYS_TBS_DISK_DATA     RANGE2                Q3_2014                  2,097,152            2,304        .11
SYS                   PARTITIONED      SYS_TBS_DISK_DATA     RANGE2                Q4_2014                  2,097,152              256        .01
SYS                   PARTITIONED      SYS_TBS_DISK_DATA     RANGE_SALES           DEF                      2,097,152              256        .01
SYS                   PARTITIONED      SYS_TBS_DISK_DATA     RANGE_SALES           Q1_2014                  2,097,152              768        .04
SYS                   PARTITIONED      SYS_TBS_DISK_DATA     RANGE_SALES           Q2_2014                  2,097,152            1,536        .07
SYS                   PARTITIONED      SYS_TBS_DISK_DATA     RANGE_SALES           Q3_2014                  2,097,152            1,280        .06
SYS                   PARTITIONED      SYS_TBS_DISK_DATA     RANGE_SALES           Q4_2014                  2,097,152              768        .04
23 rows selected.

Disk Index#

set linesize 1024;
set colsize 20;
SELECT U.USER_NAME USER_NAME                                                                                                   -- Database user  
     , I_LIST.TABLE_NAME                                                                                                       -- Table name
     , DECODE(I_LIST.PARTITION_NAME, NULL, 'NON-PARTITIONED', I_LIST.PARTITION_NAME) PARTITIONED_NAME                          -- Partitioned table name. If a non-partitioned, then NON-PARTITIONED
     , I_LIST.INDEX_NAME INDEX_NAME                                                                                            -- Index name
     , DECODE(I_LIST.INDEX_PARTITION_NAME, NULL, 'NON-PARTITIONED', I_LIST.INDEX_PARTITION_NAME) PARTITIONED_INDEX             -- Partitioned index name
     , TBS.NAME TBS_NAME                                                                                                       -- Tablespace to which the index belongs  
     , TO_CHAR((D.MAX * TBS.PAGE_SIZE)/1024, '999,999,999') 'MAX(KB)'                                                          -- Maximum size of table space
     , TO_CHAR((TBS.EXTENT_PAGE_COUNT  TBS.PAGE_SIZE  SEG.EXTENT_TOTAL_COUNT)/1024, '999,999,999') 'ALLOC(KB)'               -- Total size allocated from the index 
     , TO_CHAR((((TBS.EXTENT_PAGE_COUNT  TBS.PAGE_SIZE  SEG.EXTENT_TOTAL_COUNT)/(D.MAXTBS.PAGE_SIZE))100), '99.99') 'USAGE(%)'   -- Percentage of utilization compared to the maximum size of the tablespace
  FROM (SELECT T.TABLE_NAME
             , PT.PARTITION_NAME
             , I.INDEX_NAME
             , PI.INDEX_PARTITION_NAME
             , DECODE(T.IS_PARTITIONED, 'F', I.TABLE_ID, 'T', PT.TABLE_ID) TABLE_ID
             , DECODE(T.IS_PARTITIONED, 'F', T.TABLE_OID, 'T', PT.PARTITION_OID) TABLE_OID
             , DECODE(I.IS_PARTITIONED, 'F', I.TBS_ID, 'T', PI.TBS_ID) TBS_ID
             , I.INDEX_ID
             , T.USER_ID
          FROM SYSTEM_.SYS_INDICES_ I LEFT OUTER JOIN SYSTEM_.SYS_INDEX_PARTITIONS_ PI ON PI.INDEX_ID = I.INDEX_ID LEFT OUTER JOIN SYSTEM_.SYS_TABLE_PARTITIONS_ PT ON PT.PARTITION_ID = PI.TABLE_PARTITION_ID LEFT OUTER JOIN SYSTEM_.SYS_TABLES_ T ON T.TABLE_ID = I.TABLE_ID ) I_LIST
     , V$SEGMENT SEG
     , V$INDEX I
     , V$TABLESPACES TBS
     , SYSTEM_.SYS_USERS_ U
     , (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 1=1
   AND SEG.TABLE_OID = I.TABLE_OID
   AND SEG.SEGMENT_PID = I.INDEX_SEG_PID
   AND SEG.SPACE_ID = I_LIST.TBS_ID
   AND I_LIST.INDEX_ID = I.INDEX_ID
   AND I_LIST.TABLE_OID = I.TABLE_OID
   AND I_LIST.TBS_ID = TBS.ID
   AND D.SPACEID = I_LIST.TBS_ID
   AND U.USER_ID = I_LIST.USER_ID
 ORDER BY I_LIST.TABLE_NAME, I_LIST.INDEX_NAME, I_LIST.PARTITION_NAME, I_LIST.INDEX_PARTITION_NAME
;

Example of output

USER_NAME             TABLE_NAME            PARTITIONED_NAME      INDEX_NAME            PARTITIONED_INDEX     TBS_NAME              MAX(KB)          ALLOC(KB)        USAGE(%)        
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYS                   CUSTOMER              NON-PARTITIONED       __SYS_IDX_ID_113      NON-PARTITIONED       SYS_TBS_DISK_DATA        2,097,152              256        .01          
SYS                   DEPARTMENT            NON-PARTITIONED       DEP_IDX1              NON-PARTITIONED       SYS_TBS_DISK_DATA        2,097,152              256        .01          
SYS                   DEPARTMENT            NON-PARTITIONED       __SYS_IDX_ID_111      NON-PARTITIONED       SYS_TBS_DISK_DATA        2,097,152              256        .01          
SYS                   EMPLOYEE              NON-PARTITIONED       EMP_IDX1              NON-PARTITIONED       SYS_TBS_DISK_DATA        2,097,152              256        .01          
SYS                   EMPLOYEE              NON-PARTITIONED       __SYS_IDX_ID_112      NON-PARTITIONED       SYS_TBS_DISK_DATA        2,097,152              256        .01          
SYS                   GOODS                 NON-PARTITIONED       __SYS_IDX_ID_114      NON-PARTITIONED       SYS_TBS_DISK_DATA        2,097,152              256        .01          
SYS                   GOODS                 NON-PARTITIONED       __SYS_IDX_ID_115      NON-PARTITIONED       SYS_TBS_DISK_DATA        2,097,152              256        .01          
SYS                   ORDERS                NON-PARTITIONED       ODR_IDX1              NON-PARTITIONED       USER_IDX                 2,097,152              256        .01          
SYS                   ORDERS                NON-PARTITIONED       ODR_IDX2              NON-PARTITIONED       USER_IDX                 2,097,152              256        .01          
SYS                   ORDERS                NON-PARTITIONED       ODR_IDX3              NON-PARTITIONED       USER_IDX                 2,097,152              256        .01          
SYS                   ORDERS                NON-PARTITIONED       __SYS_IDX_ID_116      NON-PARTITIONED       USER_DATA                2,097,152              256        .01          
SYS                   PART_T1               P1                    PART_T1_IDX           P_IDX1                PART_IDX                   786,432              256        .03          
SYS                   PART_T1               P2                    PART_T1_IDX           P_IDX2                PART_IDX                   786,432              256        .03          
SYS                   PART_T1               P3                    PART_T1_IDX           P_IDX3                PART_IDX                   786,432            7,424        .94          
14 rows selected.

Reference - How to check the number of disk tables and indexes#

Disk table count query

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 ;

Disk index count query

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 ;

Altibase 5.3.x, 5.5.1, 6.1.1, 6.3.1#

Version#

The monitoring query introduced on this page can be used from the version that reflects BUG-31372.

  • Altibase version 5.3.3.33
  • Altibase version 5.3.5.15
  • Altibase version 5.5.1.0.3
  • Altibase version 6.1.1
  • Altibase version 6.3.1

Note

In BUG-31372, the TOTAL_USED_SIZE column of X$SEGMENT has been added so that the actual usage of the disk table can be queried.

Note

In Altibase version 5.3.3, 5.3.5, 5.5.1 without BUG-31372 being modified, an error may occur when using the following query.

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.

Disk table#

  • 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 ~ AGING;, the table is locked, so other requests for the table are put in waiting for state.

    Altibase 5.3.x, 5.5.1, 6.1.1, 6.3.1 Disk table usage query

    SELECT U.USER_NAME USER_NAME                                                                                            -- Database user
         , TBL.TABLE_NAME TABLE_NAME                                                                                        -- Table name
         , DECODE(TBL.IS_PARTITIONED, 'T', TBL.PARTITION_NAME, 'F', '-') PARTITIONED_TABLE                                  -- Partitioned table name
         , TBS.NAME TABLESPACE_NAME                                                                                         -- Tablespace
         , TO_CHAR((D.MAX * TBS.PAGE_SIZE)/1024, '999,999,999,999') 'MAX(KB)'                                               -- Maximum size of the tablespace to which the table belongs
         , TO_CHAR((TBS.EXTENT_PAGE_COUNT  TBS.PAGE_SIZE  SEG.TOTAL_EXTENT_COUNT)/1024, '999,999,999,999') 'ALLOC(KB)'    -- Current allocated size
         , TO_CHAR(SEG.TOTAL_USED_SIZE/1024, '999,999,999,999') 'USED(KB)'                                                  -- Size of the allocated space that contains data
      FROM (SELECT TBL.USER_ID
                 , DECODE(TBL.IS_PARTITIONED, 'F', TBL.TABLE_OID, 'T', PT.PARTITION_OID) TABLE_OID
                 , TBL.TABLE_NAME
                 , PT.PARTITION_NAME
                 , DECODE(TBL.IS_PARTITIONED, 'F', TBL.TBS_ID, 'T', PT.TBS_ID) TBS_ID
                 , TBL.IS_PARTITIONED
              FROM SYSTEM_.SYS_TABLES_ TBL LEFT OUTER JOIN SYSTEM_.SYS_TABLE_PARTITIONS_ PT ON TBL.TABLE_ID = PT.TABLE_ID
             WHERE TBL.TABLE_TYPE = 'T'
           ) TBL
         , (SELECT S.TABLE_OID, SUM(S.TOTAL_EXTENT_COUNT) TOTAL_EXTENT_COUNT, SUM(S.TOTAL_USED_SIZE) TOTAL_USED_SIZE 
              FROM X$SEGMENT S 
             WHERE S.SEGMENT_TYPE IN (6, 7) / 6 : Table, 7 : LOB data(6.1.1 or earlier), 5 : Index /
             GROUP BY S.TABLE_OID) SEG
         , SYSTEM_.SYS_USERS_ U
         , V$TABLESPACES TBS
         , (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 1=1
       AND SEG.TABLE_OID = TBL.TABLE_OID
       AND U.USER_ID = TBL.USER_ID
       AND D.SPACEID = TBL.TBS_ID
       AND TBS.ID = TBL.TBS_ID
     ORDER BY USER_NAME, TABLE_NAME, PARTITIONED_TABLE
    ;
    

    Example of output

    USER_NAME             TABLE_NAME            PARTITIONED_TABLE     TABLESPACE_NAME       MAX(KB)          ALLOC(KB)        USED(KB)          
    -------------------------------------------------------------------------------------------------------------------------------------------------------
    SYS                   DISK_T                -                     USER_DATA                2,097,152          316,160              316,032  
    SYS                   DISK_T2               -                     USER_DATA                2,097,152              256                    8  
    SYS                   EMP                   -                     SYS_TBS_DISK_DATA        2,097,152              256                   16  
    SYS                   PART_T1               P1                    PART_DATA                1,048,576              768                  520  
    SYS                   PART_T1               P2                    PART_DATA                1,048,576              768                  520  
    SYS                   PART_T1               P3                    PART_DATA                1,048,576          126,464              126,384  
    SYS                   PART_T2               P201406               PART_DATA                1,048,576           11,520               11,280  
    SYS                   PART_T2               P201407               PART_DATA                1,048,576           22,784               22,544  
    SYS                   PART_T2               P201408               PART_DATA                1,048,576            5,888                5,648  
    SYS                   PART_T2               P201512               PART_DATA                1,048,576            8,704                8,464  
    SYS                   PART_T2               PMAX                  PART_DATA_DEF            1,048,576            9,728                9,592  
    SYS                   RANGE2                DEF                   SYS_TBS_DISK_DATA        2,097,152              256                    8  
    SYS                   RANGE2                Q1_2014               SYS_TBS_DISK_DATA        2,097,152          112,896              112,688  
    SYS                   RANGE2                Q2_2014               SYS_TBS_DISK_DATA        2,097,152           56,576               56,352  
    SYS                   RANGE2                Q3_2014               SYS_TBS_DISK_DATA        2,097,152            1,792                1,704  
    SYS                   RANGE2                Q4_2014               SYS_TBS_DISK_DATA        2,097,152              768                  576  
    SYS                   RANGE_SALES           DEF                   SYS_TBS_DISK_DATA        2,097,152              256                    8  
    SYS                   RANGE_SALES           Q1_2014               SYS_TBS_DISK_DATA        2,097,152            5,888                5,648  
    SYS                   RANGE_SALES           Q2_2014               SYS_TBS_DISK_DATA        2,097,152           11,520               11,280  
    SYS                   RANGE_SALES           Q3_2014               SYS_TBS_DISK_DATA        2,097,152            8,704                8,464  
    SYS                   RANGE_SALES           Q4_2014               SYS_TBS_DISK_DATA        2,097,152            4,096                3,952  
    21 rows selected.
    

Disk Index#

  • 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 ~ AGING, the table is locked. Therefore, other requests for the table are put in a waiting state, so be cautious when executing the ALTER INDEX ~ AGING.

    Altibase 5.3.x, 5.5.1, 6.1.1, 6.3.1 Disk index usage query

    -- Disk index usage column description 
    -- USER_NAME    : Database user 
    -- TABLE_NAME   : Table name
    -- PARTITIONED_NAME : Partitioned table name. If a non-partitioned, NON-PARTITIONED
    -- INDEX_NAME   : Index name
    -- PARTITIONED_INDEX    : Partitioned index name
    -- TBS_NAME : Tablespace to which the index belongs 
    -- MAX(KB)  : Max size of tablespace
    -- ALLOC(KB)    : Total size allocated
    -- USED(KB) : Size of the allocated space that includes data
    -- USAGE(%) : Percentage of utilization compared to the maximum size of the tablespace
    set linesize 1024;
    set colsize 20;
    SELECT U.USER_NAME USER_NAME                                                                                         
         , I_LIST.TABLE_NAME                                                                                             
         , DECODE(I_LIST.PARTITION_NAME, NULL, 'NON-PARTITIONED', I_LIST.PARTITION_NAME) PARTITIONED_NAME                
         , I_LIST.INDEX_NAME INDEX_NAME                                                                                  
         , DECODE(I_LIST.INDEX_PARTITION_NAME, NULL, 'NON-PARTITIONED', I_LIST.INDEX_PARTITION_NAME) PARTITIONED_INDEX   
         , TBS.NAME TBS_NAME                                                                                             
         , TO_CHAR((D.MAX * TBS.PAGE_SIZE)/1024, '999,999,999') 'MAX(KB)'                                                
         , TO_CHAR((TBS.EXTENT_PAGE_COUNT  TBS.PAGE_SIZE  SEG.TOTAL_EXTENT_COUNT)/1024, '999,999,999') 'ALLOC(KB)'     
         , TO_CHAR(SEG.TOTAL_USED_SIZE/1024, '999,999,999,999') 'USED(KB)'                                               
         , TO_CHAR(((SEG.TOTAL_USED_SIZE/(D.MAXTBS.PAGE_SIZE))100), '99.99') 'USAGE(%)'                                
      FROM (SELECT T.TABLE_NAME
                 , PT.PARTITION_NAME
                 , I.INDEX_NAME
                 , PI.INDEX_PARTITION_NAME
                 , DECODE(T.IS_PARTITIONED, 'F', I.TABLE_ID, 'T', PT.TABLE_ID) TABLE_ID
                 , DECODE(T.IS_PARTITIONED, 'F', T.TABLE_OID, 'T', PT.PARTITION_OID) TABLE_OID
                 , DECODE(I.IS_PARTITIONED, 'F', I.TBS_ID, 'T', PI.TBS_ID) TBS_ID
                 , I.INDEX_ID
                 , T.USER_ID
              FROM SYSTEM_.SYS_INDICES_ I LEFT OUTER JOIN SYSTEM_.SYS_INDEX_PARTITIONS_ PI ON PI.INDEX_ID = I.INDEX_ID 
                                          LEFT OUTER JOIN SYSTEM_.SYS_TABLE_PARTITIONS_ PT ON PT.PARTITION_ID = PI.TABLE_PARTITION_ID 
                                          LEFT OUTER JOIN SYSTEM_.SYS_TABLES_ T ON T.TABLE_ID = I.TABLE_ID AND T.TABLE_TYPE = 'T') I_LIST
         , X$SEGMENT SEG
         , V$INDEX I
         , V$TABLESPACES TBS
         , SYSTEM_.SYS_USERS_ U
         , (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 1=1
       AND SEG.TABLE_OID = I.TABLE_OID
       AND SEG.SEGMENT_PID = I.INDEX_SEG_PID
       AND SEG.SPACE_ID = I_LIST.TBS_ID
       AND I_LIST.INDEX_ID = I.INDEX_ID
       AND I_LIST.TABLE_OID = I.TABLE_OID
       AND I_LIST.TBS_ID = TBS.ID
       AND D.SPACEID = I_LIST.TBS_ID
       AND U.USER_ID = I_LIST.USER_ID
     ORDER BY I_LIST.TABLE_NAME, I_LIST.INDEX_NAME, I_LIST.PARTITION_NAME, I_LIST.INDEX_PARTITION_NAME
    ;
    

    Example of output

    USER_NAME             TABLE_NAME            PARTITIONED_NAME      INDEX_NAME            PARTITIONED_INDEX     TBS_NAME              MAX(KB)          ALLOC(KB)        USED(KB)            USAGE(%)
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SYS                   DISK_T                NON-PARTITIONED       DISK_T_IDX_01         NON-PARTITIONED       USER_IDX                 2,097,152           47,104               46,984      2.24
    SYS                   DISK_T2               NON-PARTITIONED       DISK_T2_IDX_01        NON-PARTITIONED       USER_IDX                 2,097,152              256                   16      0.00
    SYS                   EMP                   NON-PARTITIONED       EMP_IDX_01            NON-PARTITIONED       SYS_TBS_DISK_DATA        2,097,152              256                   32      0.00
    SYS                   PART_T1               P1                    PART_T1_IDX           P_IDX1                PART_IDX                 3,145,728              256                  216       .01
    SYS                   PART_T1               P2                    PART_T1_IDX           P_IDX2                PART_IDX                 3,145,728              256                  216       .01
    SYS                   PART_T1               P3                    PART_T1_IDX           P_IDX3                PART_IDX                 3,145,728           46,848               46,608      1.48
    SYS                   PART_T2               P201406               PART_T2_IDX_01        P201406               PART_IDX                 3,145,728            3,840                3,808       .12
    SYS                   PART_T2               P201407               PART_T2_IDX_01        P201407               PART_IDX                 3,145,728            7,680                7,528       .24
    SYS                   PART_T2               P201408               PART_T2_IDX_01        P201408               PART_IDX                 3,145,728            2,048                1,936       .06
    SYS                   PART_T2               P201512               PART_T2_IDX_01        P201512               PART_IDX                 3,145,728            3,072                2,856       .09
    SYS                   PART_T2               PMAX                  PART_T2_IDX_01        PMAX                  PART_IDX                 3,145,728            3,328                3,232       .10
    SYS                   PART_T2               P201406               PART_T2_IDX_02        P201406               PART_IDX                 3,145,728            3,840                3,808       .12
    SYS                   PART_T2               P201407               PART_T2_IDX_02        P201407               SYS_TBS_DISK_DATA        2,097,152            7,680                7,528       .36
    SYS                   PART_T2               P201408               PART_T2_IDX_02        P201408               SYS_TBS_DISK_DATA        2,097,152            2,304                1,936       .09
    SYS                   PART_T2               P201512               PART_T2_IDX_02        P201512               SYS_TBS_DISK_DATA        2,097,152            3,328                2,856       .14
    SYS                   PART_T2               PMAX                  PART_T2_IDX_02        PMAX                  SYS_TBS_DISK_DATA        2,097,152            3,584                3,232       .15
    16 rows selected.
    

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 ;

Disk index count query

set linesize 1024;
set colsize 50;
SELECT DECODE(T.IS_PARTITIONED, 'T', 'PARTITIONED     INDEX CNT : '||PART_T.CNT, 'F', 'NON-PARTITIONED INDEX CNT : '||T.CNT) INDEX_COUNT
  FROM (SELECT IS_PARTITIONED
             , COUNT(*) CNT
          FROM SYSTEM_.SYS_INDICES_
         WHERE TABLE_ID IN (SELECT TABLE_ID
                              FROM SYSTEM_.SYS_TABLES_ T
                                 , V$DISKTBL_INFO D
                             WHERE T.TABLE_OID = D.TABLE_OID)
         GROUP BY IS_PARTITIONED) T
     , (SELECT COUNT(*) CNT FROM SYSTEM_.SYS_INDEX_PARTITIONS_) PART_T ;