体系_表空间和数据文件的管理

本文概要介绍了Oracle数据库表空间存储分配和管理的主要特性及操作。

I 表空间的物理关系和逻辑关系

数据库：database

表空间：tablespace，从性能和管理上做逻辑划分，总体上可划分为系统表空间、撤销表空间、临时表空间、用户表空间。应尽量减少系统表空间的负担，用户数据不要放在系统表空间。10g之后多了SYSAUX表空间（辅助系统表空间），主要用于记录大量的自调整、优化分析的数据等。
段：segment，一种存储结构，不同类型的数据库对象以不同的段形式存在，如数据段、索引段、临时段、撤销段等。手动/自动，选择自动
区：extent，Oracle表空间内部分配和回收空间的最小单位，由若干连续的数据块构成。范围的大小分配由创建表空间时指定，未指定时则采用Oracle的默认存储参数。数据字典管理/本地管理，选择本地管理。
Oracle数据块：data block，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：操作系统块，大小由操作系统决定。

方案：schema 数据库对象根据用户与对象的从属关系组织为不同的模式，一个数据库用户所拥有的所有对象称为一个模式，模式名与用户名相同，一个用户模式下的所有数据库对象是通过多个类型的段来存储的。

数据文件：data file，大块数据顺序访问用一个，小块数据随机访问用多个。

II 表空间存储参数的设置

1、文件属性的设置

大文件（bigfile）与小文件（smallfile）：大文件是Oracle 10g开始引进的特性，大文件表空间建立在单个数据文件上，文件大小可达32TB。而小文件表空间由一至多个数据文件构成。大文件特性主要为超大型数据库设计，为了克服在数据变更时Oracle要更新所有数据文件头信息而可能带来的效率问题。要使用大文件表空间，在创建表空间时使用bigfile或smallfile关键字，create [bigfile | smallfile] tablespace myjia datafile '/jia/test/myjia01.dbf' size 200m，省略时默认为小文件表空间。

自动扩展（autoextend）：autoextend on | off

文件状态信息：online | offline

表空间的数据文件可以同时创建多个

create tablespace myjia

datafile '/jia/test/myjia01.dbf' size 100m autoextend on next 10m maxsize 500m;

验证：

SQL> select TABLESPACE_NAME from dba_tablespaces where TABLESPACE_NAME='MYJIA';

TABLESPACE_NAME
------------------------------
MYJIA

2、空间管理的方式

Oracle提供两类参数设置：区管理，段空间管理。

区管理（extent management）：决定范围分配信息的存储位置，有数据字典管理（dictionary）和本地管理（local）两种方式。 选本地管理。如果system表空间是本地管理的，则不允许创建数据字典管理的表空间。

本地管理又分为

段空间管理（segment space management）：指定段内空间的管理方式，有auto和manual两种。Oracle推荐使用 auto管理方式，manual方式为向后兼容所保留，这需要手动设置段空间的使用参数pct_free和pct_used等。

ASSM的局限性

1.无法控制 tablespace内部的独立表和索引的存储行为。
2. 大型对象不能够使用AS5M,而且必须为包含有LOB数据类型的表格创建分离的 tablespace。
3.你不能够使用ASSM创建 临时的 tablespace。这是由排序时临时分段的短暂特性所决定的。
4.只有 本地管理的 tablespace才能够使用位图分段管理。
5·使用超高容量的DML(例如 INSERT、 UPDATE和 DELETE等)的时候可能会出现性能上的问题。

区管理和段管理的区别

创建表空间时指定范围管理和段空间管理方式
create tablespace myjia
datafile '/jia/test/myjia01.dbf' size 100m auto extend on next 10m maxsize 500m
extent management local uniform size 512k
segment space management auto;

--autoallocate | uniform

插入数据

SQL> alter tablespace lxtab1 add datafile '/oradata/lxtab/crtab2.dbf' size 20m;

SQL> alter tablespace lxtab2 add datafile '/oradata/lxtab/instab2.dbf' size 20m;

查询

SQL> select tablespace_name,contents,status,extent_management,segment_space_management from dba_tablespaces;

TABLESPACE_NAME CONTENTS STATUS EXTENT_MAN SEGMEN
------------------------------ --------- --------- ---------- ------
SYSTEM PERMANENT ONLINE LOCAL MANUAL
SYSAUX PERMANENT ONLINE LOCAL AUTO
UNDOTBS1 UNDO ONLINE LOCAL MANUAL
TEMP TEMPORARY ONLINE LOCAL MANUAL
USERS PERMANENT ONLINE LOCAL AUTO
UNDOTBS2 UNDO ONLINE LOCAL MANUAL
LXTAB1 PERMANENT ONLINE LOCAL AUTO
LXTAB2 PERMANENT ONLINE LOCAL MANUAL

说明：区管理local（本地）管理有压倒性优势，段管理auto和manual两种各有优势

PCTFREE和PCTUSED的值可以在创建表时指定，也可以在创建表后用修改，但是要注意的是，修改后的值，只对修改后的数据操作有影响，对之前的无效。

为了减少不必要的额外I/O操作，uniform size的最佳大小应为参数db_block_size和参数db_file_multiblock_read_count设定值两者的乘积。

3、内部存储参数的设置

可通过关键字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 myjia
datafile '/jia/test/myji01.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子句。

III 表空间的维护管理

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 myjia rename to mytbsjia;

3、删除表空间

drop tablespace … [including contents [and datafiles]];

当表空间中包含有永久的数据库对象时，必须显示指定including contents子句。若要一并删除磁盘上的操作系统文件，可同时指定and datafiles子句，否则仅将表空间从数据字典中删除。

表空间一旦删除，其中数据将永久丢失，因此在正式删除表空间前，最好先将其设置为offline状态，经时间考验确认不再需要后再删除。

4、表空间的扩容

三种方法：

1）打开表空间数据文件的自动扩展属性。
alter database datafile '/jia/test/myjia01.dbf' autoextend on next 10M maxsize 1000M;

2）手动调整已有数据文件的大小
alter database datafile '/jia/test/myjia01.dbf' resize 200m;

对于临时表空间文件
alter database tempfile ''/jia/test/myjia01.dbf' resize 200m;

resize也可以将数据文件尺寸调小，前提是已有的数据能够被新的大小容纳。

3）为表空间添加新的数据文件如
alter tablespace myjia add datafile '/jia/test/myjia02.dbf' size 200m;

对于临时表空间文件
alter tablespace temp add tempfile '/jia/test/mes/temp01.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

创建临时表空间

SQL> create temporary tablespace mytemp tempfile '/jia/test/mes/mytemp01.dbf' size 100m;

修改数据库默认的临时表空间
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表空间由参数undo_tablespace指定
show parameter undo_tablespace;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1

创建undo表空间
create undo tablespace UNDOTBS2 datafile '/jia/test/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

IIII数据文件管理

管理数据文件需要考虑以下几方面的问题：

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='/u01/oradata';

IIIII 数据文件的维护操作

1、为表空间添加数据文件
alter tablespace mytbs add datafile '/jia/test/mytbs02.dbf' size 200m;

2、改变数据文件大小，这里的操作同前面描述的给表空间扩容的操作类似。

1）通过允许或禁止数据文件的自动扩展
alter database datafile '/jia/test/mytbs02.dbf' autoextend on;

2）通过手工改变数据文件的大小
alter database datafile '/jia/test/mytbs02.dbf' resize 200m;

3、改变数据文件的可用性

1）数据文件联机
alter database datafile '/jia/test/mytbs02.dbf' online;

2）数据文件脱机
alter database datafile '/jia/test/mytbs02.dbf' offline [drop];

数据库打开时被脱机的数据文件，在回到联机状态时，需要介质恢复。对于非归档模式的数据库来说，脱机需使用drop选项，脱机后如果进行了日志切换，文件将无法恢复。

4、重定位数据文件

方法1：重定位表空间的数据文件，需要表空间脱机。

1）alter tablespace … offline;
2）将数据文件复制到新的位置，按需要重命名 cp
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）打开数据库