[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:

  • Describe how the Oracle database server automatically manages space
  • Save space by using compression
  • Proactively monitor and mange tablespace space usage
  • Describe segment creation in the Oracle database
  • Control deferred segment creation
  • Use the Segment Advisor
  • Reclaim wasted space from tables and indexes by using the segment shrink functionality
  • Manage resumable space allocation

 

posted @ 2013-06-09 11:57  ArcerZhang  阅读(189)  评论(0)  编辑  收藏  举报