| 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 |
|
| 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 |
|
| 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 |
| |
| 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 |
| |
| SET_TIMEOUT |
|
Sets the timeout of resumable space allocations for the current session in seconds |
dbms_resumable.set_timeout(timeout IN NUMBER); |
| See demo |
| |
| 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;
/
|
| |
| SPACE_ERROR_INFO
(stand-alone function) |
Stand-alone function for determining
the cause of a space error.
This function has a public synonym: ora_space_error_info
|
Function source code. See note above about
SPACE_ERROR_INFO. |
CREATE OR REPLACE FUNCTION 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 IS
BEGIN
RETURN dbms_resumable.space_error_info(error_type, object_type,
object_owner, table_space_name, object_name, sub_object_name);
END;
/ |
| |
| 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 DISTINCT sid
FROM v$mystat; |
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..1000
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%'; |
