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
email@example.com. Request a Workshop for
your organization today.
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 DBMS_RESUMABLE.ABORT() procedure.
storage settings inappropriate for the table being inserted
storage settings inappropriate for the rollback segment being used for the transaction
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
GRANT select ON v_$mystat TO resume_demo;
CREATE OR REPLACE TRIGGER logon_trigger
execute immediate 'alter session enable resumable';
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
SELECT sid, username