oracle Resumable Space Allocation

 

从9i开始，Oracle提供了一种避免因为space Error而导致事务异常的操作: resumable spaceallocation. 在Oracle 10g的OCP 考试中有考resumable session 的这个知识点。

官网对Resumable的解释：

Advantage is forDBAs who are running yearly reports and do not know how much extra disk spaceis required for TEMP, UNDO or DATA tablespaces in order to complete the job.This functionality does not break the job, but raises an alert for the DBA tofix the issue. The job automatically resumes once the DBA has fixed the issue.

--当我们执行一个事务操作，如果使用了resumable space allocation，那么如果遇到space空间不够的情况，事务不会中断，而是生成一条alert log 发送给DBA，当DBA 解决这个问题之后，事务自动恢复运行。

在Oracle 10g中对resumable session功能做了增强，这些新特性在MOS的文档上有说明：

10g NEW FEATUREon RESUMABLE TIMEOUT [ID 240991.1]

10g中增强的内容有：

1）增加了一个resumable_timeout的参数

   该参数可以在system和session level级均可以修改.对RAC db,每个instance可以单独设置. 而9i中只能在session一级中设置。

A resumableoperation is suspended whenever it encounters some space issue. (See DatabaseAdministrator's Guide for information about enabling resumable  space allocation, what conditions areresumable, and what statements can be made resumable.  For example,DEADLOCK or ORA-00600 error are not resumable situations).

Once theoperation is suspended, an alert message is sent to the DBA. Once the causethat caused the failure is fixed, the suspended statement automatically resumesits execution.  Every"resumable" operation has a time-out period associated. The defaultvalue of the time-out period is 2 hours (unless the user issues an altersession enable resumable). A suspended operation is automatically aborted ifthe error condition is not fixed within the "time-out".

--设置resumable_timeout 之后，在指定的timeout 时间内会自动恢复，如果超过这个时间没有解决问题，事务操作还是会被中断。

2）对分布式事务的支持

Distributed Transactions Behavior

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

In 9i, users arenot allowed to start a distributed transaction in a resumable enabled session.  And if a session has a distributedtransaction, users are not allowed to enable resumable.

Theserestrictions are removed in 10g. However, in a distributed transaction, if usersenable/disable resumable or change resumable_timeout, only the local instancesare affected. In a distributed transaction, sessions on remote instances aresuspended if resumable has been enabled in the remote instance.

3）增加了一个监控的视图

在10g中，我们也可以通过DBA_OUTSTANDING_ALERTS来监控resumablesession.