Skip to content

Disk tablespace usage#

Overview#

This document summarizes disk tablespace usage query by version.

Altibase 4.3.9#

Disk tablespace usage query

set linesize 1024;
set colsize 30;
SELECT TBS.NAME TBS_NAME                                                                                                    -- Disk tablespace name
     , TO_CHAR(ROUND(DAT.MAX * TBS.PAGE_SIZE / 1024 /1024, 2)) 'MAX(M)'                                                     -- Maximum size that can be allocated
     , ROUND(TBS.TOTAL_PAGE_COUNT * TBS.PAGE_SIZE / 1024 / 1024, 2) 'TOTAL(M)'                                              -- Total number of pages allocated so far
     , DECODE(TBS.TYPE, 5, ROUND( UNDO.ALLOC  TBS.PAGE_SIZE/1024/1024, 2) / UNDO TABLESPACE*/
                         , ROUND( TBS.ALLOCATED_PAGE_COUNT * TBS.PAGE_SIZE / 1024 / 1024, 2) ) 'ALLOC(M)'                   -- Total of only 'used pages' excluding 'blank pages' among the allocated pages so far
     , DECODE(TBS.TYPE, 3, '-' / TEMP TABLESPACE /
                      , 5, ROUND( UNDO.USED  TBS.PAGE_SIZE /1024/1024, 2) / UNDO TABLESPACE*/                             -- Size of the pages in use at which data is loaded. TEMP TABLESPACE cannot be obtained.
                         , DECODE(SEG.USED, '', 0, ROUND((SEG.USED  TBS.PAGE_SIZE  TBS.A_EXTENT_PAGE_COUNT)/1024/1024, 2)) / USER TABLESPACE & SYS_TBS_DATA /) 'USED(M)'   
     , DECODE(TBS.TYPE, 5, ROUND( UNDO.ALLOC / DAT.MAX * 100, 2) --UNDO
                         , ROUND( TBS.ALLOCATED_PAGE_COUNT / DAT.MAX * 100, 2) ) 'USAGE(%)'
     , DECODE(TBS.STATE, 1, 'ONLINE', 2, 'BEGIN BACKUP', 3, 'END BACKUP', 'NOT DEFINED') STATE
     , DAT.AUTOEXTEND
  FROM V$TABLESPACES TBS LEFT OUTER JOIN (SELECT SPACE_ID , SUM(EXTENT_TOTAL_COUNT) ALLOC , SUM(EXTENT_FULL_COUNT ) USED
                                            FROM X$SEGMENT
                                            GROUP BY SPACE_ID
                                         ) SEG ON TBS.ID = SEG.SPACE_ID
     ,(SELECT SPACEID
             , SUM(DECODE(MAXSIZE, 0, CURRSIZE, MAXSIZE)) AS MAX
             , DECODE(MAX(AUTOEXTEND), 1, 'ON', 'OFF') 'AUTOEXTEND'
          FROM V$DATAFILES
         GROUP BY SPACEID ) DAT
     , (SELECT SUM(ALLOCATED_PAGE_COUNT) ALLOC
             , SUM(USED_PAGE_COUNT) USED
          FROM V$UNDO_TBS ) UNDO
 WHERE TBS.ID = DAT.SPACEID;

Example of output

TBS_NAME                        MAX(M)                          TOTAL(M)    ALLOC(M)    USED(M)                         USAGE(%)    STATE         AUTOEXTEND  
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYS_TBS_DATA                    2049                            249         249         0                               12.15       ONLINE        ON   
SYS_TBS_UNDO                    2048                            246         245.72      0.02                            12          ONLINE        ON   
SYS_TBS_TEMP                    2048                            100         1           -                               0.05        ONLINE        ON   
USER_DATA                       2048                            2048        832         831                             40.63       ONLINE        OFF  
USER_IDX                        2048                            2048        157         155.75                          7.67        ONLINE        OFF  
USER_IDX_TBS                    512                             512         1           0                               0.2         ONLINE        OFF  
6 rows selected.

Altibase 5.1.5#

  • In Altibase version 5, the disk tablespace usage cannot be queried due to the change in the disk table structure, and only the allocated size is known.
  • Starting from Altibase version 5.3.3, it has been improved so that the usage can be queried. (Excluding undo tablespaces and temporary tablespaces)

Disk tablespace usage query

set linesize 1024;
set colsize 30;
SELECT  NAME TBS_NAME                                         -- Disk tablespace name
      , TO_CHAR(ROUND(D.MAX * PAGE_SIZE / 1024 /1024, 2)) 'MAX(M)'                             -- Max size of tablespace
      , ROUND(TOTAL_PAGE_COUNT * PAGE_SIZE / 1024 / 1024, 2) 'TOTAL(M)'                        -- Total number of pages allocated so far
      , DECODE(TYPE, 7, ROUND((SELECT (SUM(total_page_count) * PAGE_SIZE)/1024/1024
                                 FROM V$undo_seg)+
                              (SELECT (SUM(ALLOC_PAGE_COUNT) * PAGE_SIZE)/1024/1024
                                 FROM v$tss_seg), 2)
                      , ROUND(ALLOCATED_PAGE_COUNT * PAGE_SIZE / 1024 / 1024, 2)) 'ALLOC(M)'   -- Total of only 'used pages' excluding 'blank pages' among the allocated pages so far
      , DECODE(TYPE, 7, ROUND( ( (SELECT SUM(total_page_count) FROM V$undo_seg) +
                                 (SELECT SUM(ALLOC_PAGE_COUNT) FROM v$tss_seg ) ) / D.MAX  * 100, 2)
                      , ROUND(ALLOCATED_PAGE_COUNT / D.MAX * 100, 2))             'USAGE(%)'   -- ALLOC utilization rate compared to MAX
       , 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
;

Example of output

TBS_NAME                        MAX(M)                          TOTAL(M)    ALLOC(M)    USAGE(%)    STATE           AUTOEXTEND  
----------------------------------------------------------------------------------------------------------------------------------------
SYS_TBS_DISK_DATA               2048                            100         0.25        0.01        ONLINE          ON   
SYS_TBS_DISK_UNDO               2048                            100         2           0.1         ONLINE          ON   
SYS_TBS_DISK_TEMP               2048                            100         0.25        0.01        ONLINE          ON   
3 rows selected.

Altibase 5.3.3, 5.3.5#

  • Starting from Altibase version 5.3.3, the actual usage of the tablespace can be checked by using the TOTAL_USED_SIZE column information of X$SEGMENT.
  • The TOTAL_USED_SIZE column of X$SEGMENT was added in BUG-31372.
  • The version reflecting BUG-31372 is as follows.
    • Altibase 5.3.3.33
    • Altibase 5.3.5.15
    • Altibase 5.5.1.0.3
  • Therefore, in Altibase 5.3.3, 5.3.5, 5.5.1 versions without BUG-31372 being modified, an error may occur when using the following query.
  • The actual usage of undo tablespaces and temporary tablespaces is not available in this version.

Disk tablespace usage query

set linesize 1024;
set colsize 30;
SELECT NAME TBS_NAME                                                                                                            -- TBS_NAME : Disk tablespace name
     , TO_CHAR(D.MAX * PAGE_SIZE / 1024 /1024, '999,999,999') 'MAX(M)'                                                          -- MAX(M)   : Max size of tablespace
     , TO_CHAR(TOTAL_PAGE_COUNT*PAGE_SIZE/1024/1024, '999,999,999') 'TOTAL(M)'                                                  -- TOTAL(M) : Total page size allocated to date
     , DECODE(TYPE, 7, TO_CHAR((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), '999,999,999') , / UNDO /
                       TO_CHAR((ALLOCATED_PAGE_COUNT*PAGE_SIZE)/1024/1024, '999,999,999')) 'ALLOC(M)'                           -- ALLOC(M) : Total of only 'used pages' excluding 'blank pages' among the allocated pages so far
     , DECODE(TYPE, 3, TO_CHAR(NVL(DS.USED, 0)/1024/1024, '999,999,999'),
                    4, TO_CHAR(NVL(DS.USED, 0)/1024/1024, '999,999,999') / SYS_TEMP /
                     , LPAD('-', 12)) 'USED(M)'                                                                                 -- USED(M)  : Size of the pages in use at which data is loaded. TEMP and UNDO cannot obtain USED.
     , DECODE(TYPE, 7, TO_CHAR(((SELECT SUM(TOTAL_EXTENT_COUNT*PAGE_COUNT_IN_EXTENT)
                                  FROM V$UDSEGS)+ (SELECT SUM(TOTAL_EXTENT_COUNT*PAGE_COUNT_IN_EXTENT)
                                  FROM V$TSSEGS)) / D.MAX 100,  '99.99') ,          / UNDO */
                    3, TO_CHAR(NVL(DS.USED, 0)/(D.MAXPAGE_SIZE) 100,  '99.99') ,
                    4, TO_CHAR(NVL(DS.USED, 0)/(D.MAXPAGE_SIZE) 100,  '99.99') ,   / TEMP /
                       TO_CHAR(ALLOCATED_PAGE_COUNT / D.MAX * 100,  '99.99') ) 'USAGE(%)'                                       -- USAGE(%) : MAX대비 USED. TEMP, UNDO 의 경우 MAX대비 ALLOC
     , DECODE(STATE, 1, 'OFFLINE', 2, 'ONLINE', 5, 'OFFLINE BACKUP', 6, 'ONLINE BACKUP', 128, 'DROPPED', 'DISCARDED') STATE     -- STATE    : Tablespace state
     , D.AUTOEXTEND
  FROM V$TABLESPACES T LEFT OUTER JOIN (SELECT SPACE_ID , SUM(TOTAL_USED_SIZE) USED
                                          FROM X$SEGMENT
                                         GROUP BY SPACE_ID ) DS ON DS.SPACE_ID = T.ID
     ,(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
;

Example of output

TBS_NAME                        MAX(M)           TOTAL(M)         ALLOC(M)         USED(M)                   USAGE(%)         STATE           AUTOEXTEND  
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYS_TBS_DISK_DATA                      2,048              217              217              211               10.32           ONLINE          ON   
SYS_TBS_DISK_UNDO                      2,048              323              322                -               15.73           ONLINE          ON   
SYS_TBS_DISK_TEMP                      2,048              100                0                -                 .01           ONLINE          ON   
USER_DATA                              2,048            2,048              310              309               15.07           ONLINE          OFF  
USER_IDX                               2,048            2,048               47               46                2.24           ONLINE          OFF  
PART_DATA                              1,024            1,024              173              171               16.72           ONLINE          OFF  
PART_DATA_DEF                          1,024            1,024               10                9                 .92           ONLINE          OFF  
PART_IDX                               3,072            3,072               70               69                2.23           ONLINE          OFF  
8 rows selected.

Altibase 5.5.1, 6.1.1, 6.3.1#

  • V$DISK_UNDO_USAGE was added in Altibase 5.5.1.
  • This performance view allows users to check the usage of the undo tablespace.

Disk tablespace usage query

set linesize 1024;
set colsize 30;
SELECT DECODE(TYPE, 3, 'SYSTEM TABLESPACE', 4, 'USER DATA TABLESPACE', 5, 'SYSTEM TABLESPACE', 6, 'USER TEMP TABLESPACE', 7, 'SYSTEM TABLESPACE') TBS_TYPE
     , NAME TBS_NAME                                                                                                                                    -- TBS_NAME : Tablespace name
     , TO_CHAR((D.MAX * PAGE_SIZE / 1024 /1024), '999,999,999') 'MAX(M)'                                                                                -- MAX(M)   : Maximum size of tablespace
     , TO_CHAR((TOTAL_PAGE_COUNT * PAGE_SIZE)/1024/1024, '999,999,999') 'TOTAL(M)'                                                                      -- TOTAL(M) : Total number of pages allocated so far
     , DECODE(TYPE, 7, TO_CHAR((U.TOTAL_EXT_CNT*PROP.EXTENT_SIZE)/1024/1024, '999,999,999')
                     , TO_CHAR((ALLOCATED_PAGE_COUNT * PAGE_SIZE)/1024/1024, '999,999,999')) 'ALLOC(M)'                                                 -- ALLOC(M) : Total of only 'used pages' excluding 'blank pages' among the allocated pages so far.
     , DECODE(TYPE, 3, TO_CHAR(NVL(DS.USED, 0)/1024/1024, '999,999,999'), 
                    4, TO_CHAR(NVL(DS.USED, 0)/1024/1024, '999,999,999'),
                    7, TO_CHAR(((U.TX_EXT_CNT+U.USED_EXT_CNT+U.UNSTEALABLE_EXT_CNT) * PROP.EXTENT_SIZE)/1024/1024, '999,999,999')
                     , LPAD('-', 12))'USED(M)'                                                                                                          -- USED(M)  : Size of the pages in which the data is loaded
     , DECODE(TYPE, 7, TO_CHAR((((U.TX_EXT_CNT+U.USED_EXT_CNT+U.UNSTEALABLE_EXT_CNT)  PROP.EXTENT_SIZE)/(D.MAXPAGE_SIZE))*100, '99.99'),
                    3, TO_CHAR(NVL(DS.USED, 0)/(D.MAXPAGE_SIZE) 100, '99.99'),
                    4, TO_CHAR(NVL(DS.USED, 0)/(D.MAXPAGE_SIZE) 100, '99.99')
                     , TO_CHAR((ALLOCATED_PAGE_COUNT/D.MAX) * 100, '99.99')) 'USAGE(%)'                                                                 -- USAGE(%) : Usage (USED versus MAX)
     , DECODE(STATE, 1, 'OFFLINE', 2, 'ONLINE', 5, 'OFFLINE BACKUP', 6, 'ONLINE BACKUP', 128, 'DROPPED', 'DISCARDED') STATE                             -- STATE    : Tablespace state
     , D.AUTOEXTEND
  FROM V$TABLESPACES T LEFT OUTER JOIN(SELECT SPACE_ID , SUM(TOTAL_USED_SIZE) USED
                                         FROM X$SEGMENT
                                        GROUP BY SPACE_ID) DS ON DS.SPACE_ID = T.ID
     , (SELECT SPACEID
             , SUM(DECODE(MAXSIZE, 0, CURRSIZE, MAXSIZE)) AS MAX
             , DECODE(MAX(AUTOEXTEND), 1, 'ON', 'OFF') 'AUTOEXTEND'
          FROM V$DATAFILES
         GROUP BY SPACEID ) D
     , V$DISK_UNDO_USAGE U
     , (SELECT VALUE1 EXTENT_SIZE
          FROM V$PROPERTY
         WHERE NAME = 'SYS_UNDO_TBS_EXTENT_SIZE') PROP
 WHERE T.ID = D.SPACEID ;

Example of output

TBS_TYPE              TBS_NAME                        MAX(M)           TOTAL(M)         ALLOC(M)         USED(M)                   USAGE(%)         STATE           AUTOEXTEND  
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYSTEM TABLESPACE     SYS_TBS_DISK_DATA                      2,048              952              952              952               46.46           ONLINE          ON   
SYSTEM TABLESPACE     SYS_TBS_DISK_UNDO                      2,048              704                8                8                 .39           ONLINE          ON   
SYSTEM TABLESPACE     SYS_TBS_DISK_TEMP                      2,048              100                1                -                 .02           ONLINE          ON   
USER DATA TABLESPACE  PART_DATA                              1,024            1,024              177              176               17.19           ONLINE          OFF  
USER DATA TABLESPACE  PART_DATA_DEF                          1,024            1,024               12               11                1.07           ONLINE          OFF  
USER DATA TABLESPACE  PART_IDX                               3,072            3,072              416              415               13.49           ONLINE          OFF  
6 rows selected.
BUG-39985 V$DISK_UNDO_USAGE calculation error correction#

The REUSABLE_EXT_CNT column of V$DISK_UNDO_USAGE defines the reusable size within the undo tablespace.

An undo tablespace full phenomenon occurred, but the REUSABLE_EXT_CNT column has an error indicating that there is reusable space, so it has been improved.

This bug is reflected in the version below.

  • Altibase version 6.1.1.4.9
  • Altibase version 6.3.1.3.3