[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;
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
分类:
Oracle 10G新特性
, Oracle 11G新特性
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· C++代码改造为UTF-8编码问题的总结
· DeepSeek 解答了困扰我五年的技术问题
· 为什么说在企业级应用开发中,后端往往是效率杀手?
· 用 C# 插值字符串处理器写一个 sscanf
· Java 中堆内存和栈内存上的数据分布和特点
· 为DeepSeek添加本地知识库
· 精选4款基于.NET开源、功能强大的通讯调试工具
· DeepSeek智能编程
· 大模型工具KTransformer的安装
· [计算机/硬件/GPU] 显卡