[bbk5213] 第110集第13章表空间管理 08

　　项目案例：有一个事务，需要执行20小时，假如在执行过程中执行到第19个半的时候，突然遇到了空间不足的情况，此时运行的事务就会报错，错误解决后事务将不再执行，也就意味着之前的19个半小时全部前功尽弃，此种事务叫做不可恢复性事务；现在我们提供一种遇到空间不足的情况的事务，在中止暂停之后，等分配完自由空间之后，还能够继续执行的解决方案.

实现可恢复性空间分配的功能(可恢复模式)，需要在系统或者session级别开启此项功能，这样一旦事务运行过程中出现空间不足的情况，所有的事务资源将被挂起，不会被释放，知道有足够的资源可以供事务使用.假如空间分配问题一直没有得到解决，事务并不是始终处于挂起状态,默认情况下是2个小时.可恢复模式可以设置挂起的时间间隔.

执行删除的情况也会导致执行中断吗？

是的，当执行删除操作的时候，如果回退段中的空间不足的时候，就会产生挂起，此时就可以通过可恢复的空间分配管理功能，进行暂时挂起，然后新增空间进行解决.

Managing Resumable Space Allocation

A resumable statement

Enables you to suspend large operations instead of receiving an error
Gives you a chance to fix the problem while the operation is suspended,rather than starting over
Is suspended for the following conditions:
- Out of space
- Maximum extends reached
- Space quota exceeded
A resumable statement can be suspended and resumed multiple times.

Using resumable space allocation

Queries,DML operations,and certain DML operations can be resumed if they encounter an out-of-space error.
A resumable statement can be issued through SQL,PL/SQL,SQL * Loader and Data Pump utilities,or the Oracle Call Interface(OCI).
A statement executes in a resumable mode only if its session has been enabled by one of the following actions:
- The RESUMABLE_TIMEOUT initialization parameter is set to a nozero value.
- An ALTER SESSION ENABLE RESUMABLE statement is issued:

ALTER SESSION ENABLE RESUMABLE;
INSERT INTO sales_new SELECT * FROM sh.sales;
ALTER SESSION DISABLE RESUMABLE;

View Code

Resuming Suspended Statements

What Operations Are Resumable ?

The following operations are resumable:

Queries:SELECT statements that run out of temporary space(for sort areas)
DML:INSERT,UPDATE,and DELETE statements
The following DDL statements:
- CREATE TABLE ... AS SELECT
- CREATE INDEX
- ALTER INDEX ... REBUILD
- ALTER TABLE ... MOVE PARTITION
- ALTER TABLE ... SPLIT PARTITION
- ALTER INDEX ... REBUILD PARTITION
- ALTER INDEX ... SPLIT PARTITION
- CREATE MATERIALIZED VIEW

Summary

In this lesson,you should have learned how to: