Resumable space allocation

Normal 0 7.8 pt 0 2 false false false EN-US ZH-CN X-NONE 实验目的：理解 Resumable space allocation 原理及使用方法。

实验结果：在session 1 中插入大量数据到一个表中，使得该表出现 out of space error，此时触发一触发器，在该触发器中设置enable resumable; 然后在session 2中 extent space，此时发现session 1可以正常执行。

实验参考：Chapter 14 of “Administrator’s Guide”

范例：

Session 1:

         1) create small size(3m) of tablespace

         SQL> create tablespace tb_resumable

         2  datafile '/oracle/db11g/oradata/mydb/resumable.dbf' size 3m;

Tablespace created.

         2) grant privilege to user jay

         SQL> grant resumable to jay;

Grant succeeded.

         SQL> grant execute on dbms_resumable to jay;

Grant succeeded.

SQL> conn jay/jay

Connected.

         3) create a trigger

         SQL> create or replace trigger trig_resumable

         2     after suspend on database

         3             begin

         4                     dbms_resumable.set_timeout(60);

         5             end;

         6     /

Trigger created.

4) Enable resumable

SQL> ALTER SESSION ENABLE RESUMABLE;

Session altered.

         5) create a table and insert a large number of rows to this table.

         SQL> create table t_resumable tablespace tb_resumable  as select * from dba_objects;

         此时Session 1停止，等待60s。

Session 2:

         在60s内扩大tablespace tb_resumable空间

         SQL>  alter database datafile '/oracle/db11g/oradata/mydb/resumable.dbf'

       2  autoextend on;

Database altered.

         此时Session 1出现Table created.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24068527/viewspace-666796/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24068527/viewspace-666796/