Resumable space allocation gives certain operations time to recover from a supported allocation error. Oracle suspends the statement, retains its transaction resources, and continues it automatically when the cause is corrected before the timeout. The client waits during suspension. Use it when someone can monitor the operation and correct that specific condition within the response window. A long timeout prolongs blocking and resource retention, so choose the interval from the recovery action and its effect on other work.
Oracle DBA Lesson 62 Part 2 — Resolve a Suspended Space Request
Enable resumable mode before the statement
The loading session needs the RESUMABLE system privilege. Enable resumable mode before the intended statement:
ALTER SESSION ENABLE RESUMABLE TIMEOUT 120
NAME 'LOAD_WINDOW';120 is the suspension timeout in seconds for this example, measured from suspension. It is a limit on how long the statement may stay suspended, separate from how long the statement has already run. NAME helps locate the work. Names are not unique identifiers. These settings belong to the session. An explicit nonzero timeout avoids relying on a system default.
Eligible failures include out-of-space, maximum-extents, and space-quota conditions for supported operations. Permission, connectivity, and integrity failures stay errors, as do SQL failures outside those allocation conditions. Check the operation-specific documentation, especially for utilities and for parallel or distributed work.
See managing space for schema objects for resumable operations, correctable errors, retained resources, suspension, continuation, and timeout, and ALTER SESSION and RESUMABLE_TIMEOUT for enable, disable, name, and timeout behavior.
Separate the quota from the data file
An insert that needs another extent can suspend when its owner reaches a tablespace quota. In this example the owner has a 1 MB allocation allowance on RESUME_SPACE. The data file is 16 MB, autoextend is off, and the file has room for the bounded load. A quota limits that owner's allocation in the named tablespace. It reserves no file space.
The DATAFILE SIZE clause without a file name uses Oracle Managed Files when DB_CREATE_FILE_DEST is set. The example is a dedicated, fixed-size tablespace:
CREATE TABLESPACE resume_space
DATAFILE SIZE 16M AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
ALTER USER app_user QUOTA 1M ON resume_space;
SELECT username, tablespace_name, bytes, max_bytes
FROM dba_ts_quotas
WHERE username = 'APP_USER'
AND tablespace_name = 'RESUME_SPACE';
SELECT file_id, file_name, bytes, autoextensible, maxbytes
FROM dba_data_files
WHERE tablespace_name = 'RESUME_SPACE';Record the file identity. Read usable free space in the tablespace together with the file size and the underlying capacity. DBA_TS_QUOTAS returns the owner's BYTES and MAX_BYTES. DBA_DATA_FILES returns the file size and whether the file can autoextend. The M suffix uses Oracle's binary size units. The later 5 MB quota ceiling and the 16 MB file are bounds. They do not predict exact segment overhead.
UNLIMITED TABLESPACE overrides individual quotas.
See CREATE TABLESPACE and DB_CREATE_FILE_DEST for the fixed-size Oracle Managed Files clause, DBA_TS_QUOTAS and DBA_DATA_FILES for the allowance and the file, and creating user accounts and GRANT for quota scope and the overriding privilege.
Run the insert after enabling the session
In the owner session, create the table in resume_space, capture the session identity, enable resumable mode, and then run the insert:
CREATE TABLE resume_load (
id NUMBER,
payload VARCHAR2(1000 BYTE)
)
SEGMENT CREATION IMMEDIATE
TABLESPACE resume_space
NOCOMPRESS;
SELECT SYS_CONTEXT('USERENV', 'SID') AS load_sid,
SYS_CONTEXT('USERENV', 'INSTANCE') AS load_instance,
SYS_CONTEXT('USERENV', 'CON_NAME') AS container_name
FROM dual;
ALTER SESSION ENABLE RESUMABLE TIMEOUT 120
NAME 'LOAD_WINDOW';
INSERT INTO resume_load (id, payload)
SELECT LEVEL, RPAD('x', 1000, 'x')
FROM dual
CONNECT BY LEVEL <= 3000;This is a 3,000-row load. Each payload is 1,000 single-byte x characters, plus row and segment overhead. The exact suspension row count and the allocation depend on storage. CREATE TABLE is DDL and can commit outstanding work in the session that issues it, so create the table before the insert transaction begins.
Read the suspension before changing the file
Use a separate observer session in the same database and container. Capture the loading session's identity before the insert starts. The observer can query DBA_RESUMABLE. The owner can query USER_RESUMABLE for its own work.
SELECT r.instance_id, r.session_id, u.username,
r.name, r.status, r.timeout, r.start_time,
r.suspend_time, r.resume_time,
r.error_number, r.error_msg, r.sql_text
FROM dba_resumable r
JOIN dba_users u ON u.user_id = r.user_id
WHERE r.name = 'LOAD_WINDOW'
AND r.session_id = &load_sid
AND r.instance_id = &load_instance;Replace the substitution values with the captured identity. Also match the owner, the name, the statement text, and the start time, particularly when sessions or names can be reused. Read the full error. A quota error, an inability to extend a segment, and a maximum-extents error call for different corrections.
A schematic row used in the video is:
| SESSION_ID | STATUS | ERROR_NUMBER |
|---|---|---|
| 142 | SUSPENDED | 1536 |
ORA-01536 reports a tablespace quota exceeded. The row identifies the statement owner. The full error identifies the affected tablespace.
| Reported condition | Inspect | Possible targeted action |
|---|---|---|
| Owner quota reached | Owner and tablespace BYTES and MAX_BYTES, and actual capacity | Increase that finite quota within the planned limit |
| Unable to extend a table or index | Affected segment and tablespace, usable free extents, file limits, and underlying capacity | Make appropriate capacity available after determining the actual constraint |
| Maximum extents reached | Exact object and storage condition, and the supported options | Correct that limit for the specific object and deployment |
See DBA_RESUMABLE for the monitoring fields.
Raise the quota the error names
For this quota example, within the 120-second suspension window, raise only this owner's allowance on the dedicated tablespace from 1 MB to the 5 MB ceiling:
ALTER USER app_user QUOTA 5M ON resume_space;The physical file remains 16 MB. Raising a quota cannot create storage. When physical capacity is insufficient, resolve that condition before expecting the load to continue. The correction in this example raises that one quota to 5 MB and leaves autoextend off.
DDL can implicitly commit outstanding work in the session that issues it. Run the quota change in the administrator session. The original client keeps waiting on the suspended insert.
The same statement continues, or it times out
When the correctable condition is removed before timeout, Oracle automatically resumes the same statement. Leave the original client waiting. A second insert would be a different statement. The suspended statement can suspend again if it meets another correctable allocation failure.
DBA_RESUMABLE.STATUS includes RUNNING, SUSPENDED, TIMEOUT, ERROR, and ABORTED. The view has no COMPLETED status. For a running row, ERROR_NUMBER is zero and ERROR_MSG is null. Monitoring samples can miss a short continuation or the end of the work. Verify the client outcome and the intended data as well.
Successful execution of this insert does not commit it. After checking the result, choose an explicit COMMIT or ROLLBACK. For a successful load the checks are 3,000 rows, 3,000 distinct IDs, IDs 1 through 3,000, an ID total of 4,501,500, 1,000-byte payloads, and zero unexpected rows. This example then rolls the transaction back:
SELECT COUNT(*) AS rows_loaded,
COUNT(DISTINCT id) AS distinct_ids,
MIN(id) AS min_id, MAX(id) AS max_id,
SUM(id) AS id_total,
MIN(LENGTHB(payload)) AS min_payload_bytes,
MAX(LENGTHB(payload)) AS max_payload_bytes
FROM resume_load;
SELECT COUNT(*) AS unexpected_rows
FROM resume_load
WHERE id IS NULL OR id <> TRUNC(id)
OR id < 1 OR id > 3000
OR payload IS NULL OR payload <> RPAD('x', 1000, 'x');
ROLLBACK;
SELECT COUNT(*) AS rows_after_rollback FROM resume_load;
ALTER SESSION DISABLE RESUMABLE;After the explicit rollback, the new table is empty. ROLLBACK applies to the whole transaction, so keep other uncommitted work out of that session.
If the cause remains at timeout, the statement returns an error. A suitably privileged administrator, or the owner for its own statement, can call DBMS_RESUMABLE.ABORT for that exact session and instance. Confirm the session and instance, and the privileges the package requires in that release. The ordinary outcomes are continuation after a timely correction, or an error when the timeout expires.
Statement failure rolls back that statement's changes. Earlier successful work in the same transaction can remain, so the transaction still needs an explicit end. Suspension itself retains transactional resources, including locks already held. A conflicting writer can wait behind the suspended transaction.
When the session no longer needs resumable mode, ALTER SESSION DISABLE RESUMABLE turns the mode off. Disabling the mode leaves the commit or rollback decision unchanged.
See transactions for statement-level atomicity, transaction control, and DDL commits, and DBMS_RESUMABLE for the controlled abort.
Recap
Enable resumable mode in the executing session before the statement, with an explicit timeout measured from suspension. A supported allocation error, such as a space quota, an out-of-space condition, or maximum extents, can suspend that statement. The client waits, and the transaction keeps its locks. DBA_RESUMABLE shows SUSPENDED and the error number. ORA-01536 is the owner's quota. In this example that quota is 1 MB on RESUME_SPACE, while the data file is 16 MB with autoextend off. Raising the quota to 5 MB changes the allowance and leaves the file size unchanged. If the reported condition is corrected in time, Oracle continues the same statement. Check the client and the rows, then COMMIT or ROLLBACK. The view has no COMPLETED status. At timeout the statement returns an error and its own changes roll back. Earlier work in the transaction can remain. DISABLE RESUMABLE only turns the mode off.
No comments:
Post a Comment