conn / as sysdba

-- demo tablespace, "AND DATAFILES" deletes Operating System Files also
DROP TABLESPACE demo INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE demo
       DATAFILE 'D:\ORACLEXEE\ORADATA\XE\DEMO.DBF' SIZE 1M REUSE
       AUTOEXTEND OFF ;

-- this grant is needed for resumable operations
grant resumable to hr ;

-- no quota usage for hr user
grant unlimited tablespace to hr;

conn hr/hr

-– Resumable space allocation is enabled for 3600 seconds
ALTER SESSION ENABLE RESUMABLE NAME 'Tonguc Demo' TIMEOUT 3600;

-- Demo Table
DROP TABLE tab_resumable PURGE ;
CREATE TABLE tab_resumable TABLESPACE demo NOLOGGING AS 
       SELECT * FROM all_source WHERE ROWNUM < 1000 ;

-– Resumable operation starts
INSERT /*+ append */ INTO tab_resumable NOLOGGING SELECT * FROM all_source;

-– Out of space error occurs and error is written to alert log
Sun Feb 25 18:54:06 2007
statement in resumable session 'Tonguc Demo' was suspended due to
    ORA-01653: unable to extend table HR.TAB_RESUMABLE by 8 in tablespace DEMO

-– DBA may be notified by message generated by AFTER SUSPEND trigger and 
-- queries DBA_RESUMABLE view for details of suspended operation. When DBA fixes error condition e.g.
ALTER DATABASE DATAFILE 'D:\ORACLEXEE\ORADATA\XE\DEMO.DBF' 
       AUTOEXTEND ON NEXT 1M;
-– Suspended operation resumes automatically and operation completes successfully
Sun Feb 25 18:54:55 2007
ALTER DATABASE DATAFILE 'D:\ORACLEXEE\ORADATA\XE\DEMO.DBF'
       AUTOEXTEND ON NEXT 1M
Sun Feb 25 18:54:55 2007
Completed: ALTER DATABASE DATAFILE 'D:\ORACLEXEE\ORADATA\XE\DEMO.DBF'
       AUTOEXTEND ON NEXT 1M
Sun Feb 25 18:54:57 2007
statement in resumable session 'Tonguc Demo' was resumed

-- In Oracle 10.1 and above resumable statements can be enabled at instance level
ALTER SYSTEM SET resumable_timeout = 3600;

-- Resumable statements can be disabled at instance level using
ALTER SYSTEM SET resumable_timeout = 0;