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.
Related bug#
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