Resumable System Privilege

Contents:- Resumable Prviliges

Sql> conn sys as sysdba

Sql> create tablespace inventory datafile ‘+data(datafile)’ size 1m;

Sql> conn hr/hr

Sql> create table demo(id number,name varchar2(100)) tablespace inventory;

Sql> create or replace procedure insert_record

as

          For I in 1..100000

          Loop

                   Insert into demo values(I,’aaa’);

          End loop;

End;

/

n  You will get error.

Now when you grant resumable privilege to user, so at that time because of lack of disk space user will not get error and wait for transaction, but alert will occur in alert log file. In between dba will increase datafile size and user will complete that transaction.

Sql> conn sys as sysdba

Sql> grant resumable to hr;

Sql> conn hr/hr

Sql> alter session enable resumable;

Sql> exec insert_record;