Oracle数据库空间管理和规划

本文希望通过系统地介绍这方面的有关概念 让大家能更好地规划使用数据空间 正确使用Oracle提供的有关功能特性 提高应用的执行效率

数据库空间的有效使用和维护不仅是数据库管理的重要工作 也是大多数开发人员所关心的内容 因为它直接关系到数据库性能的发挥 同时数据碎片是经常令人头疼的问题 碎片不仅造成空间的浪费 更重要的是会直接影响应 用程序的响应速度

Oracle提供了不少方法用于数据空间的使用 监控和维护 同时也在各版本中陆续对这方面的功能进行了增强 目的在于简化这方面工作的复杂度 提高应用的运行效率

一 相关概念

数据库的空间在逻辑上分为多个表空间 每个表空间则由系统中的一个或多个物理数据文件构成 Oracle存储数据的基本单位是块 其大小在建库时由DB_BLOCK_SIZE参数确定 一个或多个连续的块构成一个区片（EXTENT） 它作为数据对象存储的基本单位来使用 在Oracle中 每个基本数据对象使用的空间称为段（Segment） 段存放在唯一的表空间上 每个段实际上是一系列区片（更为准确地是数据块）的集合 每个简单数据对象对应一个段 对于分区对象如分区表 索引 则每个（子）分区对应一个段 由各个（子）分区共同构成一个完整的数据对象 因此 可以把表空间看作桶 里面放著许多段 一个段只能放在一个桶中 而不能跨越多个桶

二 表空间的使用

表空间碎片的产生

由于同一个表空间中存放有多个数据段 各个数据段可以有不同的区片尺寸 不同段的区片可以交叉存放 当这些段中的区片经过分配（如创建表） 释放（如删除表）后 就可能使表空间中原本连续的空闲数据块变成不连续 而区片必须由连续的数据块构成 这时 当某一段需要分配新的区片时 就有可能出现虽然表空间空闲数据块的总和大于所需区片的大小 但却无法找到一串连续的块来供此区片分配使用 这种情况就称为表空间的Extent Fragmentation 我们经常会遇到这种情况 明明从DBA_FREE_SPACE中计算表空间还有几百MB 但其中的某一个表却无法再扩展几个MB的空间

消除表空间碎片

Oracle在段的区片分配上为用户提供了很大的灵活性 然而如果未能正确使用创建表空间和数据对象的各个可选择参数 则在最后将不可避免的要面对区片碎片的问题 Oracle 的Bhaskar Himatsingka 和 Juan Loaiza 为此提出了SAFE（Simple Algorithm for Fragmentation Elimination ）配制规则 通过遵循这套规则 区片级碎片可以完全的避免 而实际上 Oracle i引入的新特性 Local Managed Tablespace就是SAFE规则在Oracle Server的内置实现 SAFE原则概括起来包括

对每个表空间上的段使用相同的区片尺寸 段参数INITIAL=NEXT PCTINCREASE= 可以通过使用Create Tablespace 的 MINIMUM EXTENT 子句来确保分配的区片是此参数的倍数

仅在表空间级指定INITIAL NEXT参数 在创建数据段时不要指定这些参数

区片的大小根据段大小来确定 原则是均衡顺序扫描的效率和空间的利用率 同时确保段的区片数目控制在 之下 根据此原则 在进行相应测试之后 确定以下区片选取规则

段大小(Oracle ) 区片大小(Oracle ) 段大小(Oracle ) 区片大小(Oracle ) &M K &M K M G M M G M &G M &G M

有此数据库中可以只使用三种区片大小的表空间 在对象创建之前需对其大小进行评估 并放到相应的表空间中

Oracle 引入了本地管理表空间 它在管理和性能上都优于传统的字典管理表空间 它已融合了规则 要使用此特性 在CREATE TABLESPACE语句中指定EXTENT MANAGEMENT LOCAL子句

段的区片数目上限应在 之下 DML *** 作在此区片数目范围内不会有明显的性能差异 但某些DDL *** 作的速度则与区片的数目关系较大 因此合理的区片数目应保持在 之下 对于持续不断扩展的段 应监控区片数目 在必要时移至其它表空间

对于特别大的数据段应控制在 G G（Oracle 为 G G）之间 它们应存放到单独的表空间上 同时对于这些特大段应考虑使用分区拉提高性能

用户的临时表空间应使用TEMPORARY类型

当系统的事务规模比较均衡时可以对回滚段使用OPTIMAL参数 否则应避免制定OPTIMAL参数 而定期监控回滚段的大小 并在必要时重建

临时段和回滚段绝对不要将用户数据存放到SYSTEM表空间 它是专为永远不会Drop和Truncate的系统数据对象而设计的

创建表空间时指定数据文件的大小应=区片整数倍+ 数据块 对于Local Managed Tablespace则为区片整数倍+ K

当表空间使用统一的区片大小时 不要对其进行空间整理 重整的结果不仅耗费精力而且可能会使性能变差 对于未使用统一的区片尺寸的表空间应通过Export/Import重整

i 提供了Alter Table …Move [Tablespace…]命令可用于快速重整表 Alter Index …Rebuild…[Tablespace…] 命令可用于快速重建索引

有关使用单个区片的误导

在许多关于碎片整理的文档中建议在Export时使用Compress=Y选项 将表中的所有数据调整到一个区片中 期望在Import后获得良好性能 由此让许多人产生一个观点 认为当表中数据全部存放到一个区片中时 可以获得良好性能 实际上单区片段只在以下条件成立时 才具有优越性

数据主要以（全段）扫描方式访问

段所对应的数据块在物理磁盘上连续存放 Oracle可以发布较大的顺序磁盘读 *** 作

通过对这两条进行分析可以发现 一方面数据库中大部分表是通过索引来访问 另一方面现在的数据库文件一般在物理上使用了RAID 或RAID + 技术 数据以条带化方式分布到多个物理磁盘上 逻辑上的单个区片和多区片在物理上并无本质上的区别 另外 从Oracle的角度来看 管理几百个区片的段是非常轻松的并不会有性能的下降 由此可见将整个段放到一个区片中并无明显好处 而这种做法却会导致表空间碎片的产生

三 表数据段的使用

表空间的组织

Heap表的空间由一系列区片链接而成 每个数据块除块头外其余部分可用于存放数据 在创建表时可以指定以下参数

PCTFREE 块中保留用于UPDATE *** 作的空间百分比 当数据占用的空间达到此上限时 新的数据将不能再插入到此块中

PCTUSED 指定块中数据使用空间的最低百分比 当一个块在达到PCTFREE 之后经历了一些DELETE *** 作 在其空间使用下降到PCTUSED后便可以重新被用于INSERT数据 这就是PCTFREE/PCTUSED参数的含义

调整PCTFREE PCTUSED参数的目标一方面是提高性能 另一方面则主要是提高空间使用效率 避免出现块中存在有许多未用的空间 但却无法找到一个块可以被用于插入新数据行的情况发生

PCTFREE的使用

在Oracle中表的每一行数据由唯一的ROWID标记 而Oracle支持的数据类型中有一些长度是可变的 如VARCHAR 当对这些数据进行UPDATE时 如果块中的可用空间不能容纳UPDATE后的数据行时 Oracle将会把此行移到其它数据块 同时保留此数据行的ROWID不变 并在原有块中建一指针指向行迁移后的位置 在这种情况下读取一行数据将需要访问 个数据块 从而导致性能下降 PCTFREE保留的空间就是为确保更改后的数据行可以仍存放于原有数据块中 避免行迁移的情况发生

由此 如果PCTFREE设置不足时可能产生行迁移 而另一方面如果PCTFREE设置过高 将会造成空间浪费 因此正确设置PCTFREE需要对表中数据的使用进行分析 对于数据长度不会变化或极少更新的情况 可以采用较小的PCTFREE 对于其它大多数情况应采用稍大的PCTFREE（PCTFREE的缺省值是 如果不好估计需预留的空间 可以使用 的范围） 不要为节约块中的空间而使用较小的PCTFREE值

PCTUSED的使用

当块的使用的空间下降到PCTUSED后 此块被重新放回空闲链表（Freelist）中 作为后续Insert的候选块 同样 设置PCTUSED需要视数据行的特性和Insert Update Delete的模式而定 但必须遵守的原则是 db_block_size * （ PCTFREE PCTUSED）必须比行的长度大

对于数据行长度变化较大的情况 应使用最大行长度来计算PCTUSED 并且应使用较低的PCTUSED值 因为在执行Insert时 如果数据块的可用空间不能装下一行数据 当块的使用的空间是在PCTUSED之上 Oracle将把此块从Freelist中移走 当块的使用的空间是在PCTUSED之下 Oracle将会扩展段空间 因此 PCTUSED如果设得过高 将导致段的不断扩展

lishixinzhi/Article/program/Oracle/201311/18776

Oracle 创建数据表

数据库中的每一个表都被一个模式（或用户）所拥有，因此表是一种典型的模式对象。在创建数据表时，Oracle 将在一个指定的表空间中为其分配存储空间。最初创建的表时一个空的逻辑存储结构，其中不包含任何数据记录。

一、数据表的逻辑结构

表是最常见的一种组织数据的方式，一张表一般都具有多个列（即多个字段）。每个字段都具有特定的属性，包括字段名、数据类型、字段长度、约束、默认值等，这些属性在创建表的时被确定。

Oracle 常用的5种数据类型：

1、字符类型

字符类型用于什么包含字母、数字数据的字段。对字符数据类型再细分可包括定长字符串和变长字符串两种，分别对应着 char 数据类型和 varchar 数据类型。

CHAR 数据类型，用于存储固定长度的字符串。一旦定义了 char 类型的列，该列就会一直保持声明时所规定的长度大小。当为该列的某个单元格（行与列的交叉处就是单元格）赋予长度较短的数值后，空余部分 Oracle 会用空格自动填充；如果字段保存的字符长度大于规定的长度，则 Oracle 会产生错误信息。char 类型的长度范围为 1~ 2000字节。

VarChar2 类型 : varchar2 用于存储变长的字符串。将字段定义为 varchar2 数据类型时，该字段的长度将根据实际字符数据的长度自动调整（即如果该列的字符串长度小于定义时的长度，系统不会使用空格填充，而是保留实际的字符串长度）。因此，在大多数情况下，都会使用 varchar2 替换 char 数据类型。

2、数值类型

数值数据类型的字段用于存储带符号的整数或浮点数。Oracle 中的 number 数据类型具有精度（precision）和范围（scale）。精度指定所有数字位的个数，范围值定小数的位数，这两个参数都是可选的。如果插入的数据超过指定的位数，Oracle 将自动进行四舍五入。

3、日期时间类型

Oracle 提供的日期时间数据类型时 DATE，它可以存储日期和时间的组合数据。用DATE 数据类型存储日期时间比使用字符数据类型进行存储更简单，并且可以借助于 Oracle 提供的日期时间函数方便处理数据。

在 Oracle 中，可以使用不同的方法建立日期值。最常用的获取日期的方法是通过 sysdate 函数，调用该函数可以获取当前系统的日期值。还可以使用 To_DATE 函数将数值或字符串转换为 DATE 类型。 Oracle 默认的日期和时间格式由初始化参数 NLS_DATE_FORMAT 指定，一般为 DD-MM-YY。

4、LOB类型

Lob 数据类型用于大型的、未被结构化的数据，例如二进制文件、图片文件和其他类型的外部文件。LOB 类型的数据可以存储在数据库内部，也可以将数据存储在外部文件中，而将指向数据的指针存储在数据库中。LOB 数据类型分为 BLOB、CLOB 和 BFILE 数据类型。

BLOB 类型：blob类型用于存储二进制对象。典型的 blob 可以包括图像、音频文件、视频文件等。在 blob 类型的字段中能够存储最大为 128MB的二进制对象。

CLOB 类型： clob 类型用于存放字符格式的大型对象，CLOB 类型的字段能够存储最大为 128MB 的对象。Oracle 首先把数据转换成Unicode格式的编码，然后再将它存储在数据库中。

BFILE 类型：bfile 类型用于存储二进制格式的文件；在 bfile 类型的字段中可以将最大为128MB 的二进制文件作为 *** 作系统文件存储在数据库外部，文件的大小不能超过 *** 作系统的限制；bfile 类型的字段中仅保存二进制文件的指针，并且 bfile 字段是只读的，不能修改。

5、RowID 数据类型

RowID 数据类型被称为 “伪列类型”，用于在 Oracle 内部保存表中的每条记录的物理地址。在 Oracle 内部通过 rowid 来定位所需记录的。由于 rowID 实际上保存的时数据记录的物理地址，所以通过 rowid 来访问数据记录可以获得最快的访问速度。为了便于使用，Oracle 自动为每一个表建立一个名称为 rowID 的字段，可以对这个字段进行查询、更新和删除等 *** 作，设置利用 rowID 来访问表中的记录以获取最快的 *** 作速度。

注意：由于 rowid 字段是隐式的，用户检索表时不会看到该字段，因此，如果要使用 rowid 字段，则必须显示地指定其名称。

二、创建数据表

创建表通常使用 create table 语句。如果用户在自己的模式中创建一个表，则用户必须具有 create table 系统权限。如果要在其他用户模式中创建表，则必须具有 create any tables 的系统权限。此外，用户还必须在指定的表空间中设置一定的配额存储空间。

创建表应该包含哪些列并且指定这些列的数据类型，而这些内容最好在对用户需求分析的基础上进行确定。

语法格式：

create table table_name(

字段名1 数据类型，

字段名2 数据类型

)；

注意：1、如果用户要在其他模式中创建表，则必须在表名前加上某个模式的名称。

2、在create table 语句中可以使用嵌套子查询，基于已经存在的表或视图来创建新表，不需要为新表定义字段。

例：

SQL >create table students_2 as select * from students

三、数据表的特性

在 Oracle 中创建表时，表的特性将决定系统如何创建表、如何在磁盘上存储表，以及表创建后使用时的最终执行方式等。

1、存储参数

当用户在 Oracle 中创建表时，Oracle 允许用户指定该表如何使用磁盘上的存储空间。如果仅为表指定了表空间，而没有设置存储参数，则该表将采用其所属表空间的默认的存储参数。然而，表空间的默认存储参数不一定对表空间的每一个表都适用，因此，当表所需要的存储参数与表空间的默认存储参数不匹配时，需要用户在创建表时显式指定存储参数以体寒表空间的默认存储参数。

在创建表时，可以通过使用 storage 子句来设置存储参数，这样可以控制表中盘区的分配管理方式。对于本地化管理的表空间而言，如果指定盘区的管理方式为 autoallocate(自动化管理) ，则可以在 storage 子句中指定 initial、next 和 minextents 这3个存储参数，Oracle 将根据这3个存储参数的值为表分配的数据段初始化盘区大小，以后盘区的分配将由 Oracle 自动管理。如果指定的盘区管理方式为 uniform(等同大小管理)，这时不能为表指定任何 storage 子句，盘区的大小将是统一大小。

参数 next 用于指定为存储表中的数据分配的第二个盘区大小。该参数在字典管理的表空间中起作用，而在本地化管理的表空间中不再起作用，因为随后分配的盘区将由 Oracle 自动决定其大小。参数 minextents 用于指定允许为表中的数据所分配的最小盘区数目，同样在本地化管理的表空间中该参数也不再起作用。因此，在存储参数中，主要是设置 inital 参数。该参数用于为表指定分配的第一个盘区大小，以 KB 和 MB 为单位。当为已知大小的数据建立表时，可以将 initial 设置为一个可以荣脑所有数据的数值，这样可以将表中所有的数据存储在一个盘区从而避免产生碎片。

例：

SQL >select initial_extent from user_tables where table_name = 'students'

在user_tables 数据字典表中查询表 students 的第一个盘区的大小。

2、数据块管理参数

对于一般不带有LOB 类型的数据表而言，一个数据块可以存放表的多行记录，用户可以设置的数据块管理参数主要分为以下两类：

1、Pctfree 和 pctused

这两个参数用于控制数据块中空闲空间的使用方法

---