dbms_resumable

SQL> show parameter resumable_timeout(单位是秒)

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resumable_timeout                    integer     600

SQL> alter system set resumable_timeout=700;

System altered.

SQL> alter session enable resumable;

Session altered.

create tablespace small_qdl datafile '1.dbf' size 5m extent management local segment space management auto;
alter user qdl default tablespace qdl;
create table t as select * from dba_objects;

SQL> set serveroutput on
SQL>  execute print_table('select * from dba_resumable');
USER_ID                       : 49
SESSION_ID                    : 141
INSTANCE_ID                   : 1
COORD_INSTANCE_ID             :
COORD_SESSION_ID              :
STATUS                        : SUSPENDED
TIMEOUT                       : 7200
START_TIME                    : 02/20/06 15:36:32
SUSPEND_TIME                  : 02/20/06 15:36:33
RESUME_TIME                   :
NAME                          : User QDL(49), Session 141, Instance 1
SQL_TEXT                      : create table t_tmp as select * from dba_objects
ERROR_NUMBER                  : 1652
ERROR_PARAMETER1              : 128
ERROR_PARAMETER2              : SMALL_QDL
ERROR_PARAMETER3              :
ERROR_PARAMETER4              :
ERROR_PARAMETER5              :
ERROR_MSG                     : ORA-01652: unable to extend temp segment by 128
in tablespace SMALL_QDL

execute dbms_resumable.abort(session_id);

SQL> alter database datafile '/oradata10/DBA/datafile/small_qdl.dbf' resize 10m;

Database altered.
那边立刻执行结束

CREATE OR REPLACE TRIGGER res_default
after suspend on database
DECLARE
BEGIN
/* send an email to notify DBA */
COMMIT;
END;