Using resumable session to avoid space errors
                                                 Last update (2008-04-16 23:03:10)
                                                                                                                   Date added (2008-02-14 11:11:43)

Summary
Sometimes a session rollbacks a transaction because of ORA-0165* unable to extend errors. This feature allows the DBA, once having applied the appropriate solution to the space allocation issue, to resume the suspended transaction which does not loose all the work done previously.

How to
First grant the privilege to the user
GRANT RESUMABLE TO helpdesk;
Create a trigger every time the user logins to enable the feature
CREATE OR REPLACE TRIGGER at_logon_set_resumable_session
AFTER LOGON ON helpdesk.SCHEMA
BEGIN
EXECUTE IMMEDIATE 'alter session enable resumable timeout 1200';
END;
/

Every time the user helpdesk logins and some transactions fail due to the lack of space a record is created at dba_resumable view.
SELECT USER_ID, SESSION_ID, STATUS, START_TIME, SUSPEND_TIME, SQL_TEXT, ERROR_NUMBER, ERROR_MSG 
FROM dba_resumable;

Note
The DBA can cancel the resumable transaction by aborting the session by the procedure DBMS_RESUMABLE.ABORT(sid#). An ORA-1013 "user requested cancel of current operation" is returned to the user. Distributed transactions (with dblinks) does't work with:
alter session enable resumable;
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search