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
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:
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
Case 2: Statement released, transaction still open
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
Case 2: Query within an update transaction accessing undo
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
Case 2: Statement released, but transaction and LOB cursor remain open
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
Case 2: Session terminated during rollback
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
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:
Method B: Resize an Existing Data File
Capacity can also be increased by extending the size or maximum size of an existing data file.
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.
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.
The process_id can 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.
Method 2: Using Row Limiting (LIMIT)
Repeatedly delete a fixed number of rows that match a condition.
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
Connection string examples
Java
APRE
System-Level Configuration
Executing the following statement applies the setting to all new sessions:
For persistent configuration, update the properties file:
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
TheSQLFreeLob()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.