CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_RESUMABLE
Version 11.1
 
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
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%';
 
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [122 users online]    © 2010 psoug.org