ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
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.
Note
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 DBMS_RESUMABLE.ABORT() procedure.
storage settings inappropriate for the table being inserted
storage settings inappropriate for the rollback segment being used for the transaction
Looks for space-related errors in the error stack, otherwise returns 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;
/
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@pdbdev
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;
