表空间和数据文件的管理

本文概要介绍了Oracle数据库表空间存储分配和管理的主要特性及操作。 一、表空间的物理关系和逻辑关系 database：数据库。 users：用户，一个数据库包含多个用户。 schema：数据库对象根据用户与对象的从属关系组织为不同的模式，一个数据库用户所拥有的所有对象称为一个模式，模式名与用户名相同，一个用户模式下的所有数据库对象是通过多个类型的段来存储的。 tablespace：表空间，从性能和管理上做逻辑划分，总体上可划分为系统表空间、撤销表空间、临时表空间、用户表空间。应尽量减少系统表空间的负担，用户数据不要放在系统表空间。10g之后多了SYSAUX表空间（辅助系统表空间），主要用于记录大量的自调整、优化分析的数据等。 data file：数据文件，大块数据顺序访问用一个，小块数据随机访问用多个。 segment：段，一种存储结构，不同类型的数据库对象以不同的段形式存在，如数据段、索引段、临时段、撤销段等。 extent：范围，Oracle表空间内部分配和回收空间的最小单位，由若干连续的数据块构成。范围的大小分配由创建表空间时指定，未指定时则采用Oracle的默认存储参数。 data block：Oracle数据块，Oracle的最小IO单位，由多个操作系统块（os block）组成。主块大小由初始化参数db_block_size指定（一般默认8K），从块大小在创建表空间时指定。设定db_nk_cache_size参数为1，在SGA中可分配非标准的DB块内存，如db_2k_cache_size、db_4k_cache_size、db_32k_cache_size等。 os block：操作系统块，大小由操作系统决定。 二、表空间存储参数的设置 1、文件属性的设置 大文件（bigfile）与小文件（smallfile）：大文件是Oracle 10g开始引进的特性，大文件表空间建立在单个数据文件上，文件大小可达32TB。而小文件表空间由一至多个数据文件构成。大文件特性主要为超大型数据库设计，为了克服在数据变更时Oracle要更新所有数据文件头信息而可能带来的效率问题。要使用大文件表空间，在创建表空间时使用bigfile或smallfile关键字，create [bigfile | smallfile] tablespace，省略时默认为小文件表空间。 自动扩展（autoextend）：autoextend on | off 文件状态信息：online | offline 表空间的数据文件可以同时创建多个 create [bigfile | smallfile] tablespace mytbs datafile 'd:\oradata\mes\mytbs01.dbf' size 100m autoextend on next 10m [maxsize 500m | unlimited], 'd:\oradata\mes\mytbs02.dbf' size 100m autoextend off; 2、空间管理的方式 Oracle提供两类参数设置：范围管理，段空间管理。 范围管理（extent management）：决定范围分配信息的存储位置，有数据字典管理（dictionary）和本地管理（local）两种方式。前者将表空间的范围分配信息存储于系统表空间的数据字典中，后者将范围分配信息以bitmap形式存储于表空间数据文件头部的特殊区域。Oracle推荐并默认使用本地管理的表空间，以提高表空间的操纵性能，Oracle保留数据字典方式管理的表空间只是为了向后兼容。如果system表空间是本地管理的，则不允许创建数据字典管理的表空间。 段空间管理（segment space management）：指定段内空间的管理方式，有auto和manual两种。Oracle推荐使用auto管理方式，manual方式为向后兼容所保留，这需要手动设置段空间的使用参数pct_free和pct_used等。 创建表空间时指定范围管理和段空间管理方式 create [bigfile | smallfile] tablespace mytbs datafile 'd:\oradata\mes\mytbs01.dbf' size 100m autoextend on next 10m [maxsize 500m | unlimited], 'd:\oradata\mes\mytbs02.dbf' size 100m autoextend off extent management [local | dictionary] autoallocate | uniform size 512k segment space management [auto | manual]; 为了减少不必要的额外I/O操作，uniform size的最佳大小应为参数db_block_size和参数db_file_multiblock_read_count设定值两者的乘积。 3、内部存储参数的设置 创建表空间时如果指定用老式的数据字典范围管理方式，则可进一步设置默认的存储参数（default storage），以控制段内空间的范围分配。 另可通过关键字blocksize指定表空间的非标准数据块大小，否则按标准数据块大小（db_block_size参数指定）来设置。创建非标准数据块大小的表空间，需要事先在SGA中分配非标准数据块的内存区域，可通过参数db_nk_cache_size = 1来设定。 default storage( [initial n K|M] [next n K|M] [minextents n] [maxextents n|unlimited] [pctincrease n] initial：段空间分配的第一个范围的字节数。当用户创建模式对象时，Oracle为范围分配空间。 next：分配下一个范围的字节数。 minextents：指定当创建对象时分配范围的最小数目，默认为1，含义是Oracle仅分配初始范围，最大值取决于操作系统。如果minextents大于1，则Oracle根据initial、next、pctincrease的值计算下一个范围的大小。 maxextents：指定Oracle可以分配给一个段结构范围的最大数目，包括第一个范围。最小值为1，默认值和最大值取决于操作系统。unlimited表示按需要自动分配范围数目。 pctincrease：指定第三个和以后的范围比前一个范围增大的百分比。早期默认值是50，表示每个扩展比前一个大50%，最小值为0，表示第一个范围之后的所有范围的大小都相同。pctincrease参数设定大于0，可令表空间中的小段拥有小的范围，大段拥有大的范围并使范围的数目尽可能少。 使用举例 create tablespace mytbs datafile 'd:\oradata\mes\mytbs01.dbf' size 100m blocksize 4096 default storage( initial 256k next 256k minextents 2 maxextents 100 pctincrease 50); 此代码创建前需要对非标准数据块数据缓冲区db_4k_cache_size进行设置 alter system set db_4k_cache_size=1; 之后可看到Oracle实际分配的缓冲区大小 show parameter db_4k_cache_size; NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ db_4k_cache_size                     big integer 32M 从Oracle 10g开始，创建表空间时自动设置为extent management local autollocate和segment space management auto，此时create tablespace语句将忽略default storage子句。 三、表空间的维护管理 1、更改表空间的状态 表空间脱机，如在做系统恢复、数据文件移位等操作时。system表空间、默认的临时表空间、包含活动回滚段的表空间（当前的撤销表空间）不能脱机。 alter tablespace … offline; offline包括三种模式： offline normal：默认，所有数据文件做检查点，有脱机的数据文件时则无法正常脱机。 offline immediate：立即，脱机时不做检查点，用于基于时间点的恢复。 offline temporary：临时，所有联机的数据文件做检查点，数据文件可分联机和脱机，未联机的不管，则恢复时可能需要介质恢复。 表空间联机 alter tablespace … online; 对于一些用来专门存储历史或静态数据的表空间，可将其设置为只读状态，可以防止对数据的意外更新、删除。只读表空间不必周期性备份，只需备份一次即可。 表空间设置为只读 alter tablespace … read only; 表空间设置为默认的可读写 alter tablespace … read write; 2、表空间重命名 alter tablespace … rename to …; 3、删除表空间 drop tablespace … [including contents [and datafiles]]; 当表空间中包含有永久的数据库对象时，必须显示指定including contents子句。若要一并删除磁盘上的操作系统文件，可同时指定and datafiles子句，否则仅将表空间从数据字典中删除。 表空间一旦删除，其中数据将永久丢失，因此在正式删除表空间前，最好先将其设置为offline状态，经时间考验确认不再需要后再删除。 4、表空间的扩容 三种方法： 1）打开表空间数据文件的自动扩展属性。 alter database datafile 'd:\oradata\mes\mytbs01.dbf' autoextend on; 2）手动调整已有数据文件的大小，使用alter database语句，如 alter database datafile 'd:\oradata\mes\mytbs01.dbf' resize 200m; 对于临时表空间文件 alter database tempfile 'd:\oradata\mes\temp01.dbf' resize 200m; resize也可以将数据文件尺寸调小，前提是已有的数据能够被新的大小容纳。 3）为表空间添加新的数据文件，使用alter tablespace语句，如 alter tablespace mytbs add datafile 'd:\oradata\mes\mytbs02.dbf' size 200m; 对于临时表空间文件 alter tablespace temp add tempfile 'd:\oradata\mes\temp02.dbf' size 200m; 表空间数据文件一旦添加，则不能从中去除，除非删除整个表空间。 5、合并自由空间 基于数据字典管理的表空间，在经过一段时间的使用后，由于不断的分配和释放空间，表空间中的碎片会逐渐增多，此时可以对自由空间做合并。 alter tablespace … coalesce; 本地管理的表空间对此做了改善，不需要该操作。 6、临时表空间 主要用于存放临时排序数据等，数据首先会存放在PGA内存的排序区中，排序区大小由参数sort_area_size指定。 show parameter sort_area_size; NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ sort_area_size                       integer     65536 创建临时表空间 create temporary tablespace ... tempfile '...'; 修改数据库默认的临时表空间 alter database default temporary tablespace ...; 查看数据库默认的临时表空间 col property_value for a30 select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE'; PROPERTY_VALUE ------------------------------ TEMP 7、撤销表空间 数据库当前的UNDO表空间由参数undo_tablespace指定 show parameter undo_tablespace; NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ undo_tablespace                      string      UNDOTBS1 创建撤销表空间 create undo tablespace UNDOTBS2 datafile 'd:\oradata\mes\undotbs02.dbf' size 200m autoextend on; 将数据库的默认UNDO表空间切换到新创建的表空间 alter system set undo_tablespace = UNDOTBS2; 查看更改结果 show parameter undo_tablespace; NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ undo_tablespace                      string      UNDOTBS2 四、数据文件的管理 管理数据文件需要考虑以下几方面的问题： 1、数据文件的数量 操作系统会限制每个过程可同时打开的文件数量。 Oracle会限制每个数据库实例可打开的数据文件的数量，该限制由初始化参数db_files决定。 在创建数据库时，参数maxdatafiles决定了在控制文件中用于记录数据文件的部分的大小，这也限制了数据库可拥有的数据文件数量。 少量的大数据文件要优于大量的小数据文件，因为这样可以减少同时打开的文件个数，减少对数据文件头部的更新数。 2、数据文件的尺寸 除SYSTEM、SYSAUX、UNDO、TEMP等几个系统必要的表空间需要保证其大小外，其余表空间根据数据量需要确定大小，无特殊限制。 3、数据文件的位置 数据文件的物理位置将影响数据库性能。 应考虑将数据文件和索引文件分别放在不同的磁盘上以改善性能。 应考虑将数据文件和日志文件放在不同的磁盘上，避免读写数据文件和写日志文件的I/O竞争。 出于安全考虑也应将数据文件和日志文件分磁盘存放，若日志组有多个日志成员，这些成员应分别置于不同的物理磁盘上，以保证日志文件的安全性。 4、OMF的数据文件 Oracle管理的数据文件，由Oracle来管理定义文件名和路径，不再需要手工指定数据文件。可以OMF的文件包括： Datafiles Tempfiles Redo log files Control files Block change tracking files 通过设定参数db_create_file_dest来指定目标文件位置 alter system set db_create_file_dest='d:\oradata'; 不再需要指定数据文件 create tablespace …; 此时Oracle在指定路径下的<SID>\DATAFILE\下自动创建了数据文件，如d:\oradata\mes\DATAFILE\O1_MF_MYTBS_FV8S2RLX_.DBF，默认大小100M，且autoextend属性为unlimited。 可以自定义文件大小 create tablespace ... datafile size 200m; 增加数据文件 alter tablespace ... add datafile; 五、数据文件的维护操作 1、为表空间添加数据文件 alter tablespace mytbs add datafile 'd:\oradata\mes\mytbs02.dbf' size 200m; 2、改变数据文件大小，这里的操作同前面描述的给表空间扩容的操作类似。 1）通过允许或禁止数据文件的自动扩展 alter database datafile 'd:\oradata\mes\mytbs01.dbf' autoextend on; 2）通过手工改变数据文件的大小 alter database datafile 'd:\oradata\mes\mytbs01.dbf' resize 200m; 3、改变数据文件的可用性 1）数据文件联机 alter database datafile 'd:\oradata\mes\mytbs01.dbf' online; 2）数据文件脱机 alter database datafile 'd:\oradata\mes\mytbs01.dbf' offline [drop]; 数据库打开时被脱机的数据文件，在回到联机状态时，需要介质恢复。对于非归档模式的数据库来说，脱机需使用drop选项，脱机后如果进行了日志切换，文件将无法恢复。 4、重定位数据文件 方法1：重定位表空间的数据文件，需要表空间脱机。 1）alter tablespace … offline; 2）将数据文件复制到新的位置，按需要重命名 3）alter tablespace … rename datafile '…' to '…'; 或 alter database rename file '…' to '…'; 4）alter tablespace … online; 方法2：重定位数据库文件，适用于不能脱机的表空间，该方法同样适用于对联机日志文件的重定位。 1）启动数据库到mount状态 2）复制或移动数据库文件到新位置，按需要重命名 3）alter database rename file '…' to '…'; 4）打开数据库 六、没有备份情况下数据文件的恢复实验 在归档模式下实验： 1）创建一个表空间mytbs（mytbs01.dbf） 2）在mytbs表空间内创建一张表t1（insert into） 3）shutdown immediate 4）手工删除表空间mytbs的数据文件 5）startup 6）将数据文件mytbs01.dbf脱机 7）alter database open; 8）alter database create datafile '…\mytbs01.dbf'; 9）recover datafile '…\mytbs01.dbf'; 10）将数据文件mytbs01.dbf联机 11）检查数据是否恢复