Skip to content

Undo Tablespace#

Monitoring method when undo tablespace usage increases#

1. Overview#

When undo tablespace usage continues to increase or does not decrease, it is necessary to accurately identify the root cause and take appropriate actions accordingly.

In general, undo usage increases or is not released in the following situations:

  • Long-running update transactions: When DML transactions on disk tables do not terminate and continue for a long time
  • Uncommitted transactions: When SQL execution is completed but the transaction remains open because a COMMIT or ROLLBACK has not been performed
  • Undo image reference: When a query session references pre-change data, preventing the undo area from being reused and causing it to be retained for a long time
  • LOB resources not released: When LOB data is queried in non-autocommit mode and the LOB cursor or transaction is not properly closed in a timely manner
  • Ongoing rollback: When a large transaction is canceled and the recovery process takes a long time

This document describes key monitoring items to diagnose the causes of increasing undo usage and provides guidance on corrective and preventive actions for each scenario.

2. Monitoring Query#

If the undo tablespace usage does not decrease or continues to increase, it is necessary to identify the transactions that are using undo or accessing undo images, along with their status.

The following query can be used to identify such transactions.

Altibase 5.3.3 or later#
SELECT DECODE(TX.LOG_TYPE, 1, REP.REP_NAME, TX.SESSION_ID) AS SESSION_ID                                    -- Session ID that performed the transaction. Or the replication object name
     , TX.ID AS TX_ID                                                                                       -- Transaction ID
     , ST.ID AS STATEMENT_ID                                                                                -- STATEMENT ID
     , DECODE(TX.TSS_RID,  0, 'SELECT', 'UPDATE') AS TX_TYPE                                                -- Transaction type
     , DECODE(TX.STATUS,   0, 'BEGIN',  1, 'PRECOMMIT', 2, 'COMMIT_IN_MEMORY', 
                           3, 'COMMIT', 4, 'ABORT',     5, 'BLOCKED', 6, 'END') AS TX_STATUS                -- Transaction status
     , DECODE(ST.EXECUTE_FLAG, 1, 'SQL ING', 0, 'SQL END')                      AS SQL_STATUS               -- SQL status    
     , DECODE(TX.LOG_TYPE,     1, 'REP '||REP.PEER_IP||':'||REP.PEER_PORT, S.COMM_NAME) AS CLIENT_IP        -- Client IP
     , S.CLIENT_PID AS CLIENT_PID                                                                           -- Client process ID
     , DECODE(S.AUTOCOMMIT_FLAG, 1, 'ON', 0, 'OFF') AS AUTOCOMMIT                                           -- AUTOCOMMIT MODE
     , S.UTRANS_TIME_LIMIT AS UTRANS_TIMEOUT                                                                -- UTRANS_TIMEOUT setting value of the session
     , DECODE(ST.LAST_QUERY_START_TIME, NULL, '', ST.LAST_QUERY_START_TIME) AS LAST_QUERY_START_TIME        -- SQL statement start time
     , CASE
           WHEN TX.DISK_VIEW_SCN LIKE 'INFINITE%' THEN ''
           ELSE LTRIM(TX.DISK_VIEW_SCN)
       END AS DISK_VIEW_SCN                                                                                 -- Minimum SCN visible to the query transaction
     , CASE
           WHEN TX.MIN_DISK_LOB_VIEW_SCN LIKE 'INFINITE%' THEN ''
           ELSE LTRIM(TX.MIN_DISK_LOB_VIEW_SCN)
       END AS MIN_DISK_LOB_VIEW_SCN                                                                         -- Minimum SCN visible to the transaction querying LOB data
     , ROUND(((UD_S.TOTAL_EXTENT_COUNT  UD_S.PAGE_COUNT_IN_EXTENT  8192) / 1024), 2) AS 'UNDO_USED_KB'    -- Undo usage of the update transaction
     , ST.UNDO_READ_PAGE + ST.UNDO_GET_PAGE AS UNDO_PAGE_COUNT_COUNT                                              -- Undo pages
     , DECODE(TX.LOG_TYPE, 1, 'REMOTE_TX_ID : '||REP_TX.REMOTE_TID, LTRIM(ST.QUERY)) AS QUERY               -- Last query executed by the transaction
  FROM V$TRANSACTION TX 
  LEFT JOIN (SELECT SESSION_ID
                  , TX_ID
                  , ID
                  , TO_CHAR(TO_DATE('1970010109','YYYYMMDDHH') + (LAST_QUERY_START_TIME) / (606024), 'YYYY-MM-DD HH:MI:SS') LAST_QUERY_START_TIME
                  , EXECUTE_FLAG
                  , UNDO_READ_PAGE
                  , UNDO_GET_PAGE
                  , QUERY
               FROM V$STATEMENT
              WHERE (SESSION_ID, TX_ID, LAST_QUERY_START_TIME) 
                 IN (SELECT SESSION_ID, TX_ID, MAX(LAST_QUERY_START_TIME) LAST_QUERY_START_TIME 
                      FROM V$STATEMENT 
                     GROUP BY SESSION_ID, TX_ID)) ST ON TX.ID = ST.TX_ID 
  LEFT JOIN V$SESSION S         ON TX.SESSION_ID = S.ID 
  LEFT JOIN V$REPRECEIVER_TRANSTBL REP_TX ON TX.ID = REP_TX.LOCAL_TID 
  LEFT JOIN V$REPRECEIVER REP   ON REP_TX.REP_NAME = REP.REP_NAME
  LEFT JOIN V$TXSEGS TX_S       ON TX.ID = TX_S.TRANS_ID 
  LEFT JOIN V$UDSEGS UD_S       ON TX_S.ID = UD_S.TXSEG_ENTRY_ID              
 WHERE TX.TSS_RID <> 0                                      / modified transaction /
    OR TX.MIN_DISK_LOB_VIEW_SCN NOT LIKE 'INFINITE%'        / LOB access transaction /
    OR ((ST.UNDO_READ_PAGE <> 0 OR ST.UNDO_GET_PAGE <> 0)   / transaction accessing undo pages /
         AND TX.DISK_VIEW_SCN NOT LIKE 'INFINITE%')
 ORDER BY SESSION_ID, TX_ID;

3. Key Items to Check During Monitoring#

When interpreting the monitoring results, analyze the following items with a focus to identify the root cause.

Evaluation Criteria#
Item Description Remarks
SESSION_ID Identifier of the session executing the transaction. For replication transactions, the replication object name is displayed.
STATEMENT_ID Identifier of the SQL statement within the transaction. NULL indicates that the statement has been released.
TX_TYPE Transaction type. UPDATE: Transaction that has performed at least one DML operation.
SELECT: Read-only transaction
TX_STATUS Transaction status. BEGIN: Transaction is active.
ABORT: Transaction is undergoing rollback.
SQL_STATUS Status of the SQL statement. SQL_ING: Statement is in execution.
SQL_END: Execution has completed.
NULL indicates that the statement has been released.
CLIENT_IP Information about client connection type, IP, PORT For replication, displays the remote server IP and replication port.
NULL indicates that the session has ended.
CLIENT_PID Client process ID Client process ID.
NULL indicates that the session has ended.
AUTOCOMMIT AutoCommit mode of the session. NULL indicates that the session has ended.
DISK_VIEW_SCN SCN of the undo image visible to the transaction. Indicates that a previous version of the data is being queried.
MIN_DISK_LOB_VIEW_SCN SCN of the undo image used by the LOB cursor. Indicates that the LOB cursor is open.
UNDO_USED_KB Size of undo currently used by the query Size of the undo tablespace used by the update transaction.
UNDO_PAGE_COUNT Number of undo pages accessed by the query Indicates that undo pages are being accessed.

4. Diagnostic Criteria by Scenario#

4.1 Long-Running Update Transactions#

Update transactions generate undo records for pre-change data, resulting in increased undo usage.

Evaluation Criteria#
Item Value Description
TX_TYPE UPDATE Indicates a transaction that performs data modification
TX_STATUS BEGIN Transaction is active (not yet committed or rolled back)
SQL_STATUS SQL_ING SQL statement is currently executing
UNDO_USED_KB Increasing Indicates growth in undo usage

※ Undo is also generated when inserting data into tables with foreign key constraints or triggers.

Example Output#

The following example shows a SQL statement that modifies data and is currently in progress:

SESSION_ID            : 2           
TX_ID                 : 8766080               
STATEMENT_ID          : 131072                
TX_TYPE               : UPDATE                       -- Transaction with data modification   
TX_STATUS             : BEGIN                        
SQL_STATUS            : SQL ING                      -- SQL statement is in progress
CLIENT_IP             : TCP 127.0.0.1:32102
CLIENT_PID            : 123203            
AUTOCOMMIT            : OFF         
UTRANS_TIMEOUT        : 3600             
LAST_QUERY_START_TIME : 2025-12-02 16:30:47  
DISK_VIEW_SCN         : 962767         
MIN_DISK_LOB_VIEW_SCN : 962767         
UNDO_USED_KB          : 41728                 -- Undo usage increases while the update statement is executing  
UNDO_PAGE_COUNT       : 1589737   
QUERY                 : UPDATE dt SET c10 = SYSDATE

4.2 Long-Running Open Update Transactions#

If a transaction is not terminated after the SQL statement has completed, the undo segments used by the transaction are not released.

Evaluation Criteria#
Item Value Description
TX_TYPE UPDATE Indicates a transaction that performs data modification
TX_STATUS BEGIN Transaction remains active (not yet committed or rolled back)
SQL_STATUS SQL_END / NULL SQL_END: Statement execution has completed
NULL: Statement has been closed (released)
UNDO_USED_KB Stable No further increase after the modifying statement has completed

Example Output

Case 1: Statement completed, transaction still open

SESSION_ID            : 2           
TX_ID                 : 8766080               
STATEMENT_ID          : 131072                
TX_TYPE               : UPDATE                       -- Transaction with data modification   
TX_STATUS             : BEGIN                        -- Transaction remains open
SQL_STATUS            : SQL END                      -- Statement execution has completed
CLIENT_IP             : TCP 127.0.0.1:32102
CLIENT_PID            : 123203            
AUTOCOMMIT            : OFF         
UTRANS_TIMEOUT        : 3600             
LAST_QUERY_START_TIME : 2025-12-02 16:30:47  
DISK_VIEW_SCN         :                              -- SCN is cleared after statement completion   
MIN_DISK_LOB_VIEW_SCN :                              -- SCN is cleared after statement completion   
UNDO_USED_KB          : 71168                        -- Undo usage no longer increases after completion
UNDO_PAGE_COUNT       : 2003444
QUERY                 : UPDATE dt SET c10 = SYSDATE

Case 2: Statement released, transaction still open

SESSION_ID            : 3           
TX_ID                 : 1862209               
STATEMENT_ID          :   
TX_TYPE               : UPDATE                       -- Transaction with data modification      
TX_STATUS             : BEGIN                        -- Transaction remains open             
SQL_STATUS            :                              -- Statement has been released   
CLIENT_IP             : TCP 127.0.0.1:11046
CLIENT_PID            : 206764            
AUTOCOMMIT            : OFF         
UTRANS_TIMEOUT        : 3600             
LAST_QUERY_START_TIME : 2026-01-15 10:51:50  
DISK_VIEW_SCN         :   
MIN_DISK_LOB_VIEW_SCN :   
UNDO_USED_KB          : 256  
UNDO_PAGE_COUNT       :  
QUERY                 :

4.3 Statements Accessing Pre-Change Data for an Extended Period#

Undo segments are not released while a query continues to access undo images generated by update transactions.

Evaluation Criteria#
Item Value Description
TX_TYPE UPDATE / SELECT UPDATE: Transaction that has performed at least one modification in the past
SELECT: Read-only transaction
SQL_STATUS SQL_ING Statement is currently executing
DISK_VIEW_SCN SCN value Indicates that a pre-update version of the data is being accessed
UNDO_USED_KB NULL or stable No increase in undo usage for read-only transactions or queries
UNDO_PAGE_COUNT Increasing Indicates active access to undo data If unchanged or 0, undo is not being accessed
Example Output#

Case 1: Read-only transaction in progress

SESSION_ID            : 9           
TX_ID                 : 53804352              
STATEMENT_ID          : 589824                
TX_TYPE               : SELECT                       -- Read-only transaction
TX_STATUS             : BEGIN                          
SQL_STATUS            : SQL ING                      -- Statement is in execution
CLIENT_IP             : TCP 127.0.0.1:17731
CLIENT_PID            : 190617            
AUTOCOMMIT            : ON          
UTRANS_TIMEOUT        : 0                
LAST_QUERY_START_TIME : 2025-12-05 14:12:24      
DISK_VIEW_SCN         : 322663  
MIN_DISK_LOB_VIEW_SCN : 322663  
UNDO_USED_KB          :                              -- No undo usage for read-only transactions
UNDO_PAGE_COUNT       : 35207                        -- Increasing value indicates active undo access
QUERY                 : SELECT * FROM TEST_DISK_TBL;

Case 2: Query within an update transaction accessing undo

SESSION_ID            : 1           
TX_ID                 : 3393                  
STATEMENT_ID          : 65537                 
TX_TYPE               : UPDATE                       -- Transaction that has performed modifications  
TX_STATUS             : BEGIN             
SQL_STATUS            : SQL ING                      -- Statement is in execution     
CLIENT_IP             : TCP 127.0.0.1:46339
CLIENT_PID            : 102737            
AUTOCOMMIT            : OFF         
UTRANS_TIMEOUT        : 3600              
LAST_QUERY_START_TIME : 2026-01-15 11:13:24  
DISK_VIEW_SCN         : 801111  
MIN_DISK_LOB_VIEW_SCN : 801111  
UNDO_USED_KB          : 71168                        -- Undo usage remains stable during query execution  
UNDO_PAGE_COUNT       : 3024517                      -- Increasing value indicates active undo access 
QUERY                 : SELECT * FROM dt

4.4 Open LOB Access in Non-Autocommit Mode#

In non-autocommit mode, if a transaction is not completed after querying LOB data, or if only the statement is released without closing the LOB cursor, the associated undo segments are not released.

This can result in continuously increasing undo usage.

Evaluation Criteria#
Item Value Description
TX_STATUS BEGIN Transaction remains active (not yet committed or rolled back)
MIN_DISK_LOB_VIEW_SCN SCN value Indicates that a LOB cursor is still open
Example Output#

Case 1: Transaction remains open after querying LOB data

SESSION_ID            : 4           
TX_ID                 : 900929                
STATEMENT_ID          : 589824                
TX_TYPE               : SELECT
TX_STATUS             : BEGIN                        -- Transaction remains open  
SQL_STATUS            : SQL END                      -- Statement execution has completed
CLIENT_IP             : TCP 127.0.0.1:49199
CLIENT_PID            : 170256             
AUTOCOMMIT            : OFF         
UTRANS_TIMEOUT        : 0                
LAST_QUERY_START_TIME : 2025-12-05 14:12:24  
DISK_VIEW_SCN         :
MIN_DISK_LOB_VIEW_SCN : 324055                       -- Presence of SCN indicates LOB data access
UNDO_USED_KB          :                               
UNDO_PAGE_COUNT       :
QUERY                 : SELECT * FROM demo_clob;

Case 2: Statement released, but transaction and LOB cursor remain open

SESSION_ID            : 4           
TX_ID                 : 900929                
STATEMENT_ID          :                              -- NULL indicates statement has been released 
TX_TYPE               : SELECT
TX_STATUS             : BEGIN                        -- Transaction remains open            
SQL_STATUS            :                              -- Statement has been released
CLIENT_IP             : TCP 127.0.0.1:49199
CLIENT_PID            : 170256            
AUTOCOMMIT            : OFF         
UTRANS_TIMEOUT        : 0                
LAST_QUERY_START_TIME :    
DISK_VIEW_SCN         :
MIN_DISK_LOB_VIEW_SCN : 324055                       -- Presence of SCN indicates LOB data access
UNDO_USED_KB          :   
UNDO_PAGE_COUNT       :
QUERY                 :

4.5 Transactions Undergoing Rollback#

During rollback, undo segments are used to restore data to its previous state. Undo usage may be retained until the rollback operation completes.

Evaluation Criteria#
Item Value Description
STATEMENT_ID Numeric / NULL Numeric: Session is active
NULL: Rollback continues after session termination
TX_TYPE UPDATE Transaction that performs data modifications
TX_STATUS ABORT Transaction is in rollback
Example Output#

Case 1: Explicit rollback execution

SESSION_ID            : 3           
TX_ID                 : 155968                
STATEMENT_ID          : 196609                
TX_TYPE               : UPDATE                 -- Update transaction  
TX_STATUS             : ABORT                  -- Rollback in progress  
SQL_STATUS            : SQL ING     
CLIENT_IP             : TCP 127.0.0.1:56757
CLIENT_PID            : 185015            
AUTOCOMMIT            : OFF         
UTRANS_TIMEOUT        : 0                
LAST_QUERY_START_TIME : 2025-12-05 12:03:18      
DISK_VIEW_SCN         :   
MIN_DISK_LOB_VIEW_SCN :   
UNDO_USED_KB          : 285696  
UNDO_PAGE_COUNT       : 4045648  
QUERY                 : rollback

Case 2: Session terminated during rollback

SESSION_ID            : 3           
TX_ID                 : 155968                
STATEMENT_ID          :                 
TX_TYPE               : UPDATE                 -- Update transaction  
TX_STATUS             : ABORT                  -- Rollback in progress              
SQL_STATUS            : 
CLIENT_IP             :                        -- Session has been terminated  
CLIENT_PID            :
AUTOCOMMIT            :          
UTRANS_TIMEOUT        :                 
LAST_QUERY_START_TIME : 2025-12-05 12:03:18  
DISK_VIEW_SCN         :   
MIN_DISK_LOB_VIEW_SCN :   
UNDO_USED_KB          : 285696  
UNDO_PAGE_COUNT       : 4045648 
QUERY                 :

4.6 Replication Transactions#

Undo may also be consumed by replication transactions.

Evaluation Criteria#
Item Value Description
SESSION_ID Replication object name Indicates a replication transaction
CLIENT_IP Remote server IP Displays the IP address and port of the remote server
TX_STATUS BEGIN Transaction is in progress
QUERY REMOTE_TX_ID Identifier of the source transaction
Example Output#
SESSION_ID            : REP                      -- Replication object name is displayed
TX_ID                 : 12354                 
STATEMENT_ID          :   
TX_TYPE               : UPDATE   
TX_STATUS             : BEGIN             
SQL_STATUS            :   
CLIENT_IP             : REP 192.168.1.145:53950
CLIENT_PID            :                   
AUTOCOMMIT            :   
UTRANS_TIMEOUT        :           
LAST_QUERY_START_TIME : 2025-12-05 17:22:31       
DISK_VIEW_SCN         : 161183  
MIN_DISK_LOB_VIEW_SCN : 161183  
UNDO_USED_KB          : 19456  
UNDO_PAGE_COUNT       : 18793
QUERY                 : REMOTE_TX_ID : 53835072

5. Corrective Actions#

When undo tablespace usage reaches a critical level, immediate actions can be categorized into two approaches: expanding physical storage and removing the root cause (session/transaction).

5.1 Expanding the Undo Tablespace#

If insufficient undo space may impact service availability, increase the undo tablespace capacity.
Before proceeding, ensure that sufficient free space is available in the underlying file system.

The following methods can be used:

Method A: Add a Data File#

Additional space can be secured by adding a new data file to the existing tablespace.
First, check the current data file configuration:

-- 1. Check current data file configuration
SELECT RPAD(T.NAME, 20) SPACE_NAME
     , ROUND((D.INITSIZE*T.PAGE_SIZE)/1024/1024) AS 'INITSIZE(MB)'
     , ROUND((D.CURRSIZE*T.PAGE_SIZE)/1024/1024) AS 'CURRSIZE(MB)'
     , DECODE(D.AUTOEXTEND, 0, 'OFF', 1, 'ON') 'AUTOEXTEND'
     , ROUND((D.NEXTSIZE*T.PAGE_SIZE)/1024/1024) AS 'NEXTSIZE(MB)'
     , DECODE(D.MAXSIZE, 0, ROUND((D.CURRSIZET.PAGE_SIZE)/1024/1024), ROUND((D.MAXSIZET.PAGE_SIZE)/1024/1024)) AS 'MAXSIZE(MB)'
     , D.NAME DATAFILE
  FROM V$TABLESPACES T, V$DATAFILES D
 WHERE T.ID = D.SPACEID AND T.TYPE = 7
 ORDER BY 7;

-- 2. Example: add a data file
ALTER TABLESPACE SYS_TBS_DISK_UNDO ADD DATAFILE 'undo002.dbf' AUTOEXTEND ON NEXT 1M MAXSIZE 2G;
Method B: Resize an Existing Data File#

Capacity can also be increased by extending the size or maximum size of an existing data file.

-- 1. Example: When AUTOEXTEND is ON
ALTER TABLESPACE SYS_TBS_DISK_UNDO ALTER DATAFILE 'undo001.dbf' AUTOEXTEND ON NEXT 1M MAXSIZE 4G;

-- 2. Example: When AUTOEXTEND is OFF
ALTER TABLESPACE SYS_TBS_DISK_UNDO ALTER DATAFILE 'undo001.dbf' SIZE 4G;

5.2 Terminating Sessions#

If storage expansion is not feasible, or if a specific session is identified as the cause of excessive undo usage, the session can be terminated.

Warning
Terminating a DML session processing a large volume of data may trigger rollback.
In such cases, undo space may not be released immediately and will only be reclaimed after the rollback completes.

Impact by Transaction Type

Transaction Type Impact on Termination Resource Release Timing
SELECT Can be terminated immediately Immediately upon session termination
DML Rollback is performed After rollback completes
Terminating a Session#

After identifying the database name from V$DATABASE, terminate the target session using the SESSION_ID obtained from the monitoring query.

ALTER DATABASE database_name SESSION CLOSE session_id ;
Terminating a Client Process#

If the database session cannot be terminated normally or the connection must be closed at the application level, terminate the client process at the OS level.

$ kill -9 

The process_idcan be obtained from the CLIENT_IP field in the monitoring query.

6. Preventing Undo Tablespace Growth#

Long-running transactions (bulk transactions) are a primary cause of increased undo tablespace usage, as changes remain uncommitted for an extended period.

When large data modifications are processed within a single transaction, the following issues may occur:

  • Rapid growth of undo space: Undo segments for before images can increase significantly in a short period
  • Delayed space reuse: Undo cannot be reused until the transaction is completed (COMMIT or ROLLBACK)
  • Increased rollback overhead: In case of failure or cancellation, a larger volume of undo must be processed, potentially resulting in rollback time exceeding the original execution time

To mitigate these issues, review the following recommendations and optimize transaction handling accordingly.

6.1 Splitting Large Transactions#

Instead of processing a large volume of data in a single transaction, divide the workload into smaller batches.

This approach improves undo reuse and reduces overall undo pressure.

In addition, it minimizes the scope of recovery required in case of rollback.

Example#

Method 1: Using Range Conditions (BETWEEN ~ AND)

Divide the workload into ranges based on a primary key or indexed column, and commit after each batch.

-- Delete rows 1–1000 and commit
DELETE FROM emp WHERE eno BETWEEN 1 AND 1000;
COMMIT;

-- Delete rows 1001–2000 and commit
DELETE FROM emp WHERE eno BETWEEN 1001 AND 2000;
COMMIT;

-- Delete rows 2001–3000 and commit
DELETE FROM emp WHERE eno BETWEEN 2001 AND 3000;
COMMIT;

Method 2: Using Row Limiting (LIMIT)

Repeatedly delete a fixed number of rows that match a condition.

-- Delete up to 1,000 rows per execution (repeat as needed)
DELETE FROM emp WHERE status = 'D' LIMIT 1000;
COMMIT;

6.2 Configuring UTRANS_TIMEOUT#

If update transactions remain active for an extended period, undo images are retained longer, which can lead to increased undo usage.

To mitigate this, configure UTRANS_TIMEOUT so that transactions exceeding a defined duration are automatically terminated and rolled back.

Session-Level Configuration#

iSQL example

ALTER SESSION SET UTRANS_TIMEOUT = 3600;   -- 단위: 초

Connection string examples

  • Java

    sProps.put("utrans_timeout", "300");
    Connection con = DriverManager.getConnection(url, sProps);
    
  • APRE

    strcpy(conn_opt3, "DSN=localhost;CONNTYPE=1;UTRANS_TIMEOUT=300");
    EXEC SQL CONNECT :usr IDENTIFIED BY :pwd USING :conn_opt3;
    
System-Level Configuration#

Executing the following statement applies the setting to all new sessions:

ALTER SYSTEM SET UTRANS_TIMEOUT = 3600;   -- unit: seconds

For persistent configuration, update the properties file:

$ vi $ALTIBASE_HOME/conf/altibase.properties
UTRANS_TIMEOUT = 3600

6.3 Checking for Uncommitted Transactions#

In applications using non-autocommit mode, ensure that transactions are not left open (idle) due to missing exception handling or incomplete logic.

At the end of business logic, a COMMIT or ROLLBACK must always be explicitly issued.

6.4 Releasing LOB Resources#

LOB cursors created during LOB operations retain undo data until the transaction is completed or the cursor is closed.

After processing LOB data, ensure that one of the following actions is performed:

  • Close the LOB cursor
  • Complete the transaction (COMMIT or ROLLBACK)

(warning) Warning
The SQLFreeLob() function in SQLCLI does not immediately send a request to the server.
Instead, it is transmitted together with subsequent requests.
As a result, there may be a delay between the client-side call and the actual release of LOB resources on the server.

If immediate resource release is required, completing the transaction is the most reliable approach.

Undo tablespace usage#

Overview#

  • Prior to Altibase 5.5.1, only ALLOC of undo tablespace could be checked, but USED could not be checked.
  • This chapter describes how to check the actual usage tablespaces using V$DISK_UNDO_USAGE added from Altibase version 5.5.1.
ALLOC and USED of undo tablespace#
  • The undo tablespace is made up of segments, and segments are made up of smaller units called extents.
  • When a change transaction occurs in a disk table, it is allocated in units of extents. Whenever an extent is newly allocated, the size of the undo tablespace ALLOC increases.
  • When the transaction ends, the extent becomes reusable.
  • However, if any of the extents within a segment are in use, even if there are reusable extents, all of the extents within that segment become unusable. (UNSTEALABLE EXTENT)
  • Either the extent is used because a change transaction is executing, or the UNSTEALABLE extent is counted as USED for the undo tablespace.

Version#

This monitoring query is available in the version below.

  • Altibase 5.5.1
  • Altibase 6.1.1
  • Altibase 6.3.1
  • Altibase 6.5.1
  • Altibase 7.1.1
  • Altibase 7.3.1

Undo tablespace usage#

  • ALLOC and USED may increase while a change transaction is being performed on the disk table.
  • If the transaction is committed, the USED that is increased during the execution of the transaction is reduced.
  • If the transaction is rolled back, the ALLOC and USED that have increased during the execution of the transaction will be reduced.

Undo tablespace usage query

SELECT T.NAME TBS_NAME
     , ROUND(D.MAX * PAGE_SIZE / 1024 /1024, 2) 'MAX(M)'                                                                        -- Max size of undo tablespace
     , ROUND((TOTAL_PAGE_COUNT * PAGE_SIZE) / 1024 / 1024, 2) 'TOTAL(M)'                                                        -- Total size allocated for undo tablespace
     , ROUND((U.TOTAL_EXT_CNT*PROP.EXTENT_SIZE)/1024/1024, 2) 'ALLOC(M)'                                                        -- Total of only 'used pages' excluding 'blank pages' among the allocated pages so far
     , ROUND(((U.TX_EXT_CNT+U.USED_EXT_CNT+U.UNSTEALABLE_EXT_CNT) * PROP.EXTENT_SIZE)/1024/1024, 2) 'USED(M)'                   -- Size of the EXTENT that cannot be reused or in use by the change transaction
     , ROUND((((U.TX_EXT_CNT+U.USED_EXT_CNT+U.UNSTEALABLE_EXT_CNT)  PROP.EXTENT_SIZE)/(D.MAXPAGE_SIZE))*100, 2) 'USAGE(%)'    -- 사용량(MAX 대비 USED)
     , DECODE(STATE,1,'OFFLINE',2,'ONLINE',5,'OFFLINE BACKUP',6,'ONLINE BACKUP',128,'DROPPED', 'DISCARDED') STATE               -- Tablespace 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
     , 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
   AND T.ID = 3 ;

Example of output

TBS_NAME                        MAX(M)      TOTAL(M)    ALLOC(M)    USED(M)     USAGE(%)    STATE           AUTOEXTEND
---------------------------------------------------------------------------------------------------------------------------------
SYS_TBS_DISK_UNDO               2047.99     302         122.25      84.25       4.11        ONLINE          ON
1 row selected.
Column Name Description
TBS_NAME Tablespace Name
MAX(M) Maximum Size of Undo Tablespace
TOTAL(M) Total Allocated Size for Undo Tablespace
ALLOC(M) Total Used Pages (excluding free pages) among Allocated Pages
USED(M) Size of Extents Used or Unusable Due to Active Transactions
USAGE(%) Usage (USED vs. MAX)
STATE Tablespace Status
AUTOEXTEND Auto Extend Enabled up to Maximum Size

V$DISK_UNDO_USAGE performance view#

Column name Data type Description
TX_EXT_CNT BIGINT Number of extents for a segment (Transaction Status Segment, TSS) that stores transaction status information. It is allocated when an update transaction occurs in the disk table.
USED_EXT_CNT BIGINT Number of extents in the undo segment being used in the transaction. When an update transaction occurs in the disk table, it is allocated as needed.
UNSTEALABLE_EXT_CNT BIGINT Number of extents that cannot be taken from other undo segments. The extent that belongs to the online undo segment and the extent that has header information. Contains the extent the transaction is accessing. Even if the undo segment has reusable extents, it cannot be taken from other undo segments.
REUSABLE_EXT_CNT BIGINT Number of reusable extents USED_EXT_CNT used during transaction progress increases as USED_EXT_CNT decreases when the transaction is committed.
TOTAL_EXT_CNT BIGINT The total number of extents allocated from the undo tablespace. When a transaction is rolled back, the extents used in the transaction are returned to the undo tablespace. When a transaction is committed, the extents used in the transaction are held in the corresponding undo segment.

Bug Description

  • Improved an issue where undo areas that cannot be reused were incorrectly counted as available space. (Extents corresponding to the UNSTEALABLE_EXT_CNT column were mistakenly calculated as usable extents.) (BUG-39985)

Applied Versions

  • Altibase 6.1.1.4.9 or later

  • Altibase 6.3.1.3.3 or later

  • Altibase 6.5.1 or later