| General Information |
| Purpose |
Suspend large operations that run out of space or reach space limits after executing for a long time,
fix the problem, and make the statement resume execution. In this way you can write applications without worrying about running into space-related errors.
When you suspend a statement, you should log the suspension in the alert log. You should also register a procedure to be executed when the statement is suspended.
Using a view, you can monitor the progress of the statement and indicate whether the statement is currently executing or suspended.
Suspending a statement automatically results in suspending the transaction. Thus all transactional resources are held during a statement suspend and resume.
When the error condition disappears, the suspended statement automatically resumes execution. A resumable space allocation can be suspended and resumed
multiple times during execution.
A suspension timeout interval is associated with resumable space allocations. A resumable space allocation that is suspended for the timeout interval
(the default is two hours) wakes up and returns an exception to the user. A suspended statement may be forced to throw an exception using the
DMBS_RESUMABLE.ABORT() procedure.
* storage settings inappropriate for the table being inserted
* storage settings inappropriate for the rollback segment being used for the transaction
* lack of space on temporary tablespace
* user quotas restricted
* users privileges missing |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmsres.sql |
| First Available |
9.0.1 |
| Dependencies |
| DBA_RESUMABLE |
ORA_SERVER_ERROR_DEPTH |
| DBMS_RESUMABLE_LIB |
ORA_SERVER_ERROR_PARAM |
| GV_$SESSION_WAIT |
SPACE_ERROR_INFO |
| ORA_SERVER_ERROR |
USER_RESUMABLE |
|
| Correctable or related exceptions |
| Exception |
Description |
| ORA-01536 |
space quota exceeded on tablespace |
| ORA-01562 |
failed to extend rollback segment |
| ORA-01628 |
Max extents reached on rollback segment |
| ORA-01631 |
max extents reached on table |
| ORA-01650 |
unable to extend rollback segment |
| ORA-01652 |
unable to extend temp segment |
| ORA-01653 |
unable to extend table |
| ORA-01654 |
unable to extend index |
| ORA-01658 |
unable to create initial extent |
| ORA-01659 |
unable to allocate extents |
| ORA-30032 |
suspended session has timed out |
|
| Object Privileges |
GRANT execute ON dbms_resumable TO <schema_name>;
GRANT alter system TO <schema_name>; -- for abort procedure
GRANT resumable TO <schema_name>; |
GRANT execute ON dbms_resumable TO uwclass;
GRANT alter system TO uwclass;
GRANT resumable TO uwclass; |
| Resumable Operations |
select statements that require temporary segments for sorting
DML statements that generate undo
DDL statements such as the following:
a. create table as select
b. create index
c. alter index .. rebuild
d. alter <table/index> move partition
e. alter <table/index> rebuild partition
f. create materialized view or materialized view log |
| Security Model |
EXECUTE is granted to the DBA role.
Runs as AUTHID CURRENT_USER |
| Subprograms |
|
| |
| ABORT |
| Aborts a suspended resumable space allocation |
dbms_resumable.abort(sessionID IN NUMBER); |
| exec dbms_resumable.abort(140); |
| |
| GET_SESSION_TIMEOUT |
| Gets the current timeout value of the resumable space allocations for a session in seconds |
dbms_resumable.get_session_timeout(sessionID IN NUMBER) RETURN NUMBER;
If session_id does not exist returns -1 |
| See Demo Below |
| |
| GET_TIMEOUT |
| Returns the current timeout value of resumable space allocations for the current session in seconds |
dbms_resumable.get_timeout RETURN NUMBER; |
SELECT dbms_resumable.get_timeout
FROM dual; |
| |
| SET_SESSION_TIMEOUT |
| Sets the timeout of resumable space allocations for a session in seconds |
dbms_resumable.set_session_timeout(
sessionID IN NUMBER,
timeout IN NUMBER); |
| See Demo Below |
| |
| SET_TIMEOUT |
| Sets the timeout of resumable space allocations for the current session in seconds |
dbms_resumable.set_timeout(timeout IN NUMBER); |
| See Demo Below |
| |
| SPACE_ERROR_INFO |
| Looks for space-related errors in the error stack, otherwise returning FALSE |
space_error_info(
error_type OUT VARCHAR2,
object_type OUT VARCHAR2,
object_owner OUT VARCHAR2,
table_space_name OUT VARCHAR2,
object_name OUT VARCHAR2,
sub_object_name OUT VARCHAR2) RETURN BOOLEAN;
| Error Types |
|---|
| MAX EXTENTS REACHED |
| NO MORE SPACE |
| SPACE QUOTA EXCEEDED |
| Object Types |
|---|
| CLUSTER |
| INDEX |
| INDEX PARTITION |
| INDEX SUBPARTITION |
| LOB PARTITION |
| LOB SEGMENT |
| LOB SUBPARTITION |
| ROLLBACK SEGMENT |
| TABLE |
| TABLE PARTITION |
| TABLESPACE |
| TABLE SUBPARTITION |
| TEMP SEGMENT |
| UNDO SEGMENT |
|
Rather than using this procedure, or the following function, something similar to the following is recommended.
CREATE OR REPLACE TRIGGER suspension_handler
AFTER SUSPEND
ON DATABASE
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
-- code to notify the DBA
END suspension_handler;
/ |
| |
| Demo |
| SYS |
RESUME_DEMO |
SELECT x.ksppinm NAME, y.ksppstvl VALUE, ksppdesc DESCRIPTION
FROM x$ksppi x, x$ksppcv y
WHERE x.inst_id = userenv('Instance')
AND y.inst_id = userenv('Instance')
AND x.indx = y.indx
AND x.ksppinm = '_system_trig_enabled';
-- if necessary enable system event triggers
ALTER SYSTEM SET "_system_trig_enabled" = TRUE
SCOPE=BOTH;
CREATE USER resume_demo
IDENTIFIED BY resume_demo
DEFAULT TABLESPACE uwdata
TEMPORARY TABLESPACE temp
QUOTA 1M ON uwdata;
GRANT create session, create table, resumable
TO resume_demo;
GRANT select ON v_$mystat TO resume_demo;
CREATE OR REPLACE TRIGGER logon_trigger
AFTER logon
ON DATABASE
BEGIN
execute immediate 'alter session enable resumable';
dbms_resumable.set_timeout(1800);
END logon_trigger;
/ |
|
| |
conn resume_demo/resume_demo
SELECT sid
FROM v$mystat
WHERE rownum = 1; |
set linesize 151
col user_id format 999
col session_id format 999
col timeout format 99999
col name format a25
col sql_text format a35
-- session_id equals gv$session sid
SELECT user_id, session_id, status, timeout, name
FROM dba_resumable;
desc gv$session
SELECT sid, username
FROM gv$session;
SELECT dbms_resumable.get_session_timeout(142)
FROM dual;
exec dbms_resumable.set_session_timeout(142, 2200);
SELECT dbms_resumable.get_session_timeout(142)
FROM dual; |
|
| |
CREATE TABLE fill_space (
fidcol NUMBER(10),
strcol VARCHAR2(4000))
TABLESPACE uwdata;
BEGIN
FOR i IN 1..100000
LOOP
INSERT INTO fill_space
(fidcol, strcol)
VALUES
(i, RPAD('X', 999, 'X'));
END LOOP;
END;
/ |
SELECT user_id, session_id, status, timeout, start_time, suspend_time, resume_time, error_number
FROM dba_resumable;
SELECT user_id, session_id, status, suspend_time, sql_text
FROM dba_resumable;
ALTER USER resume_demo QUOTA unlimited ON uwdata; |
|
|
| |
| Related Queries |
| Current Errors |
SELECT user_id, session_id, error_number, error_msg, suspend_time
FROM dba_resumable; |
| Related Event Information |
SELECT sid, event, seconds_in_wait
FROM gv$session_wait
WHERE sid = 140; |
| Other related information |
SELECT event, total_waits, time_waited
FROM gv$system_event
WHERE event like '%suspend%'; |
| Other related information |
SELECT sid, event, total_waits, time_waited
FROM gv$session_event
WHERE event LIKE '%suspend%'; |
