oracle 逻辑物理结构、表空间

1. 概念

数据库、表空间和数据⽂件是密切相关的，但是它们有重要的区别:

• Oracle数据库⾄少由两个称为表空间的逻辑存储单元组成，它们共同存储数据库的所有数据。必须拥有
  SYSTEM和SYSAUX表空间。
• Oracle数据库中的每个表空间由⼀个或多个名为datafiles的⽂件组成，这些⽂件是Oracle数据库运⾏的
  操作系统上的物理结构⽂件。
• 数据库的数据是集中存储在组成数据库的每个表空间的数据⽂件中。

2. 逻辑数据库结构

1. Oracle数据库为数据库中的所有数据，分配逻辑数据库空间。数据库空间分配的单位是数据块、区段和段。
2. 表空间是⼀个逻辑概念，物理上对应⼀个或多个数据⽂件 datafile 或临时⽂件tempfiles,逻辑上表空间是存储段的容器。(段也是逻辑概念，是数据库中的对象如表索引等)

2.1 Tablespace type 表空间类型

表空间类型

1. Permanent 永久表空间
2. Temporary 临时表空间
3. UNDO 回滚表空间

表空间管理⽅式

• 管理⽅式重点是段的管理⽅式和区的管理⽅式是在建⽴表空间时确定的。
• 段管理⽅式有 AUTO 和 MANUAL 两种
• 区管理⽅式有本地管理和字典管理(已淘汰)两种。

2.2 Segments, Extents and Blocks 段，区，块

1. 在最细粒度级别上，Oracle数据库将数据存储在Block 数据块(也称为逻辑块、Oracle块或⻚⾯)中。⼀个数据块对应于磁盘上物理数据库空间的特定字节数。(默认 8KB）
2. Extents(区段)。区段是分配给存储特定类型信息的特定数量的连续数据块。
3. 比Extents⼤的逻辑数据库存储级别称为Segments(段)。Segments是⼀组Extents，每个Extents分配给⼀个特定的数据结构，所有Extents都存储在相同的tablespace表空间中。例如，每个表的数据存储在它⾃⼰的数据段中，⽽每个索引的数据存储在它⾃⼰的索引段中。如果表或索引已分区，则每个分区存储在其⾃⼰的段中。

Segments是⼀组Extent构成，其中包含表空间中特定逻辑存储结构的所有数据。
例如，对于每个表，Oracle数据库分配⼀个或多个extents来形成该表的data segment，对于每个索引，Oracle数据库分配⼀个或多个extents来形成其index

2.2.1 Data Segments 数据段

1. 段是逻辑概念，对应数据库中的对象。 segment

2. 段的类型:表段、索引段、undo 段、临时段

3. 创建⼀个表，ORACLE 为表创建⼀个(或多个)段，在⼀个段中保存该表的所有表数据(表数据不能跨段)。

4. 通常段中⾄少有⼀个初始区。当这个段数据增加使得区(extent)不够时，将为这个段分 配新的后续区。(11g 段空间延时分配)

5. 表空间在逻辑上可以存在多个段，物理上可以对应多个数据⽂件，⼀个段⽐较⼤时可以跨多个数据⽂件。(表可以跨数据⽂件）。

2.2.2 Segments 段

Free Space: 空闲空间可以⾃动管理，也可以⼿动管理

空闲空间可以在数据库段内⾃动管理。段内空闲/使⽤的空间使⽤位图跟踪，⽽不是使⽤空闲列表。⾃动分段空间管理提供了以下好处:

1. 易⽤性
2. 更好的空间利⽤率，特别是对于⾏⼤⼩差异很⼤的对象
3. 更好地对并发访问中的变化进⾏运⾏时调整
4. 在性能/空间利⽤率⽅⾯，更好的多实例⾏为

在创建本地管理的表空间时，指定⾃动段空间管理。然后，该规范应⽤于随后在这个表空间中创建的所有段。

2.2.3 Extent 区

Extent是 ORACLE 进⾏存储空间分配的基本单位。⼀个区是由⼀系列逻辑上连续的 Oracle 数据块组成的逻辑存储结构。

数据块是数据库使⽤的最⼩数据单元。
段中第⼀个区叫初始区，随后分配的区叫后续区。

Extent 管理⽅式

1. 字典管理:在数据字典中管理表空间的区空间分配。Oracle 8i 以前只有通过 uet$和fet$的 字典管理。 缺点:某些在字典管理⽅式下的存储分配有时会产⽣递归操作，并且容易产⽣碎⽚，从⽽影响了系统的性能，现在已经淘汰了。
2. 本地管理:在每个数据⽂件中使⽤位图管理空间的分配。表空间中所有区(extent)的分配 信息都保存在该表空间对应的数据⽂件的头部。
   每个区的⼤⼩:autoallocate ⾃动调整 /uniformsize 固定⼤⼩
   优点:速度快，存储空间的分配和回收只是简单地改变数据⽂件中的位图，⽽不像字
   典管理⽅式还需要修改数据库。⽆碎⽚，更易于维护。

数据表和Extent的关系
当建⽴表的时候建⽴段，然后⾃动分配相应的 extent(1 个或者多个)，亦可以⼿⼯提前分配 extent(⽤于需⼤量插⼊数据的表)

3. SYSTEM系统表空间

每个Oracle数据库都包含⼀个名为SYSTEM的表空间，Oracle数据库在创建数据库时⾃动创建这个表空间。当数据库打开时，system表空间总是online状态。

• 当SYSTEM表空间是本地管理⽅式时，⽆法创建字典管理的表空间。
• SYSTEM表空间包含以下信息：
  1. 数据字典：系统表空间总是包含整个数据库的数据字典表。
  2. PL/SQL程序单元描述：代表已存储PL/SQL程序单元(即过程、函数、包和触发器)存储的所有数据都驻留在系统表空间中。如果数据库包含许多这样的程序单元，那么数据库管理员必须在系统表空间中提供这些单元所需的空间。

4. SYSAUX表空间

• SYSAUX表空间是SYSTEM表空间的⼀个辅助表空间。许多数据库组件使⽤SYSAUX表空间作为它们存储数据的默认位置。因此，SYSAUX表空间总是在数据库创建或数据库升级期间创建的。
• 在正常的数据库操作期间，Oracle数据库不允许删除或重命名SYSAUX表空间。

5. UNDO表空间

• Undo 表空间是专⽤于存储撤销信息的表空间。不能在undo表空间中创建任何其他段类型(例如表或索引)。撤消表空间仅在数据库处于⾃动撤消管理模式(默认)时使⽤。⼀个数据库可以包含多个undo表空间，但是在任何时候只能使⽤⼀个。Undo数据在Undo表空间中使⽤数据库⾃动创建和维护的Undo段进⾏管理。
• 当第⼀个DML操作在⼀个事务中运⾏时，该事务被绑定(分配)到当前Undo 表空间中的撤销段(从⽽绑定到⼀个事务表)。在很少的情况下，如果实例没有指定的undo表空间，事务就绑定到系统的undo段。
• 每个Undo 表空间由⼀组数据⽂件组成，并且是本地管理的。与其他类型的表空间⼀样，撤销块按区段分组，每个区段的状态在位图中表示。在任何时间点，区段要么被分配给事务表(并由事务表使⽤)，要么是空闲的。

6. Temporary 表空间

• 本地管理系统表空间时，在创建数据库时必须⾄少定义⼀个默认临时表空间。
• 临时⽂件是⼀个属于临时表空间的⽂件;它是使⽤TEMPFILE选项创建的。临时表 空间不能包含永久数据库对象(例如表)，并且通常⽤于排序。
• ⽤于缓存排序的数据(中间结果) 可以建⽴多个临时表空间，但默认的临时表空间只能有⼀个且不能offline 和 drop。temp 表 空间是 nologing 的(不记⽇志)

本地管理的临时表空间具有临时数据⽂件(tempfile)，与普通数据⽂件类似，但有以下例外:

1. Tempfiles总是设置为NOLOGGING模式。
2. 不能设置tempfile为只读状态。
3. 不能使⽤ALTER DATABASE语句创建tempfile。
4. 数据恢复时不识别tempfiles:
   • 备份控制⽂件不会为tempfile⽣成任何信息。
   • 创建控件⽂件不能指定关于tempfile的任何信息。

• 除了系统表空间之外，您可以在任何时候Offline⼀个联机(Online）表空间。当您分别将表空间脱机或联机时，表空间的所有数据⽂件将作为⼀个单元online or Offline。
• 您可以将单个数据⽂件脱机。但是，这通常只在某些数据库在做数据恢复的过程中完成

7. Row Chaining and Migrating 行迁移，行连接

⾏迁移和⾏连接，分别发⽣在以下两种情况下

• 第⼀次插⼊该⾏时，该⾏太⼤，⽆法装⼊⼀个数据块。在这种情况下，Oracle数据库将⾏数据存储在为该段预留的数据块链(⼀个或多个)中。⾏链接通常发⽣在⼤型⾏中，例如包含数据类型为LONG或LONG RAW的列的⾏。在这些情况下，⾏链接是不可避免的。
• 最初适合于⼀个数据块的⾏被更新，从⽽使整个⾏⻓度增加，并且块的空闲空间已经完全被填满。在这种情况下，Oracle数据库将整个⾏数据迁移到⼀个新的数据块，假设整个⾏可以放⼊⼀个新的块中。Oracle数据库保存已迁移⾏的原始⾏块，以指向包含已迁移⾏的新块。迁移后的⾏rowid不会更改。为⾏迁移当⼀⾏被链接或迁移时，与该⾏关联的I/O性能会降低，因为Oracle数据库必须扫描多个数据块才能检索该⾏的信息。

8. PCTFREE、PCTUSED 数据块使用参数

• PCTFREE参数：将数据块的最⼩百分⽐设置为为该块中已经存在的⾏可能的更新保留的空闲空间。

  例如，假设在CREATE TABLE语句中指定了以下参数:

  PCTFREE 20

  这表明：插⼊数据时，向Data Block的Rows data区域添加新数据，并由数据库⾃身⾃动将相应的信息添加到开销区域的可变部分，直到⾏数据和开销合计占总块⼤⼩的80% （1-20%）。块中剩余的20% 的空间进⾏保留，⽤于对每个块中已经存在的⾏进⾏更新。

• PCTUSED参数设置可以⽤于⾏数据的块的最⼩百分⽐，以及在向块添加新⾏之前的开销。在将数据块填充到 PCTFREE确定的限制之后，Oracle数据库认为插⼊新⾏时该块不可⽤，直到该块的百分⽐低于所使⽤的参数
  PCTUSED。在实现此值之前，Oracle数据库仅对数据块中已经包含的⾏进⾏更新时使⽤数据块的空闲空间。

  例如，假设您在CREATE TABLE语句中指定了以下参数:

  PCTUSED 40

9. 表空间与数据文件

9.1 增加表空间大小的方式

• 表空间的⼤⼩，就是构成表空间的数据⽂件的⼤⼩。
• 所有表空间的集合⼤⼩，就是数据库的⼤⼩。
• 可以通过以下三种⽅式扩⼤数据库:

  1. add datafile to a tablespace
     alter tablespace system
     add datafile ‘DATA02.dbf’ SIZE 10G;

  2. add a new tablespace
     create tablespace users
     datafile ‘user01.dbf’ SIZE 10G;

  3. Increase the size of a datafile
     alter datafie ‘DATA03.dbf’
     autoextend on next 20M maxsize 1000M;

9.2 Data File 数据文件

1. Oracle数据库中的表空间由⼀个或多个物理数据⽂件组成。数据⽂件只能与⼀个表空间和⼀个数据库相关联。
2. Oracle数据库通过分配指定数量的磁盘空间和⽂件头所需的开销，为表空间创建⼀个数据⽂件。如果⽂件很⼤，这个过程可能会花费⼤量的时间。任何数据库中的第⼀个表空间总是SYSTEM表空间，因此Oracle数据库在创建数据库时⾃动为SYSTEM表空间分配，数据库的第⼀个数据⽂件。

Size of datafiles数据文件大小

• 可以在数据⽂件创建之后更改其⼤⼩。
• 也可以指定数据⽂件，模式为随着表空间中的对象的增⻓⽽动态增长。

查看表空间类型、段、区管理⽅式
（1）查看表空间
查看表空间及其数据⽂件⼤⼩、是否为⾃动扩展

col file_name for a45
col tablespace_name for a10
select file_id,file_name,tablespace_name,bytes/1024/1024 M,status,AUTOEXTENSIBLE from dba_data_files order by 1；

(2)⼀个表空间可能对应多个数据⽂件

select TABLESPAC_NAME,sum(bytes)/1024/1024
  from dba_data_files
  group by tablespace_name；

(3)查看表空间空闲空间

select tablespace_name,sum(bytes)/1024/1024
  from dba_free_space
group by tablespace_name

9.3 创建表空间

表空间的⼤⼩等同它下的数据⽂件⼤⼩之和，默认使⽤ small 表空间，当发⽣表空间不⾜的问题时常⽤的 3 个解决办法:

• 增加原有数据⽂件⼤⼩(resize)
• 增加⼀个数据⽂件(add datafile)
• 设置表空间⾃动增⻓(autoextend)

示例：
1.创建表空间

create tablespace prod
  datafile '/u01/app/oracle/oradata/PROD/prod01.dbf'
  size 5m

2.在该表空间下创建⼀张数据表

create table scott.test1 (id number) tablespace prod;

3. 插⼊数据

insert into scott.test1 select empno from scott.emp;
insert into scott.test1 select * from scott.test1;

4. 表空间resize方式

select file_id,file_name,tablespace_name from dba_data_files;

alter database datafile 7 resize 10m;
insert into scott.test1 select * from scott.test1;

5. add datafile 扩充表空间方式

修改数据⽂件为可⾃动扩展模式

alter database datafile 7 autoextend on next 10m maxsize 500m;

为表空间添加⼀个新的数据⽂件：

alter tablespace prod add datafile '/u01/app/oracle/oradata/PROD/PROD/PROD/PROD/PROD/
prod02.dbf' size 20m

6. 查看表空间信息

select FILE_ID,TABLESPACE_NAME,BYTES/1024/1024 Tsize,
  AUTOEXTENSIBLE,MAXBYTES/1024/1024 Tmax
  from dbadatafiles;

7. 删除表空间及其数据⽂件

drop tablespace prod ;
drop tablespace prod including contents and datafiles；

9.4 read only tablespace 表空间只读模式

1.创建一个表空间

SQL> create tablespace readonly datafile '/u01/app/oracle/oradata/PROD/PROD/PROD/PROD/
readonly01.dbf' size 10m;
SQL> create tablespace readwrite datafile '/u01/app/oracle/oradata/PROD/PROD/PROD/
PROD/readwrite01.dbf' size 10m;

举例修改

SQL> alter tablespace xxx read only;
SQL> alter tablespace xxx read write;

2.查看状态

select name,file#,checkpoint_change# from v$datafile where name like '%read%';
  
  NAME FILE# CHECKPOINT_CHANGE#
--------------------------------------------- ---------- ------------------
  /u01/app/oracle/oradata/PROD/PROD/PROD/readonly01.dbf 6 4342263
  /u01/app/oracle/oradata/PROD/PROD/PROD/readwrite01.dbf 7 43419

我们先记录下当前状态下两个表空间数据⽂件的SCN号，这个号表示表空间数据⽂件在Oracle数据库运⾏时某个时间点的状态。SCN是Oracle⾥⾯的⼀个序号，⽤来标识⼀个先后顺序，通常⽤于保护数据块的完整性或者⼀致性的查询。当数据块做了修改，它的SCN值就会发⽣相应的改变。

SQL> alter system checkpoint;

我们发出⼀个CHECKPOINT命令，这个命令实际上是将内存中的脏数据块写⼊到磁盘上的⽂件中，并更新⽂件头部信息，以保证数据块中数据的⼀致性。

SQL> select name,file#,checkpoint_change# from v$datafile where name like '%read%';
  NAME FILE# CHECKPOINT_CHANGE#
--------------------------------------------- ---------- ------------------
/u01/app/oracle/oradata/PROD/PROD/readonly01.dbf 6 4342263
/u01/app/oracle/oradata/PROD/PROD/readwrite01.dbf 7 43436

9.5 offline tablespace 表空间离线

表空间修改为离线状态，查看状态

SQL> alter tablespace readonly offline;
SQL> alter tablespace readwrite offline;
SQL> col name for a45;
SQL> select name,file#,checkpoint_change# from v$datafile where name like '%read%';
NAME FILE# CHECKPOINT_CHANGE#
--------------------------------------------- ---------- ------------------
/u01/app/oracle/oradata/PROD/readonly01.dbf 6 4342263
/u01/app/oracle/oradata/PROD/readwrite01.dbf 7 4351566
SQL> alter system checkpoint;
SQL> select name,file#,checkpoint_change# from v$datafile where name like '%read%';
NAME FILE# CHECKPOINT_CHANGE#
--------------------------------------------- ---------- ------------------
/u01/app/oracle/oradata/PROD/readonly01.dbf 6 4342263
/u01/app/oracle/oradata/PROD/readwrite01.dbf 7 4351566

9.6 Tablespace and Data Files 文件大小格式

• Small file
  在⼀个表空间可以建⽴1-1024个数据⽂件(默认)， 单个⽂件不⽀持 32G 以上。
• Big file
  在⼀个表空间只能建⽴⼀个数据⽂件 (8k 的 block 时，最⼤可达 32T)，简化对 数据⽂件管理。

数据块⼤⼩(单位：K)	BF 最⼤值(单位：T)
2k	8T
4k	16T
8k	32T
16k	64T
32k	128T

Big File

• 创建Small file的表空间

create tablespace test datafile '/u01/app/oracle/oradata/PROD/PROD/PROD/PROD/
PROD/test01.dbf' size 32G;

• 创建Big file的表空间

create bigfile tablespace big_tbs datafile '/u01/app/oracle/oradata/PROD/PROD/
_PROD/PROD/PROD/bigtbs01.dbf' size 32G;

• 尝试在BIfile 表空间下添加⼀个数据⽂件

alter tablespace big_tbs add datafile '/u01/app/oracle/oradata/PROD/PROD/PROD/
_PROD/PROD/bigtbs02.dbf' size 32G;

查看表空间是否有使⽤bigfile

select name,bigfile from v$tablespace；

10. 查看表空间缺省选项dbms_metadata.get_ddl

create tablespace a datafile '/u01/app/oracle/oradata/PROD/PROD/PROD/PROD/PROD/
a01.dbf' size 10M;

set serveroutput on

declare 
v_sql varchar2(2000);
begin
select dbms_metadata.get_ddl('TABLESPACE','A') into v_sql FROM dual;
dbms_output.put_line(v_sql);
end;


CREATE TABLESPACE "A" DATAFILE '/u01/app/oracle/oradata/PROD/PROD/PROD/PROD/PROD/a01.dbf'
SIZE 10485760 LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO

11. 查看创建数据表的DDL语句

set lines 200
set pages 200
set long 900000
SELECT DBMS_METADATA.GET_DDL('TABLE','DEPT','SCOTT') FROM DUAL；

12. 删除表空间

• drop tablespace test including contents and datafiles;
  contents 包括控制⽂件和数据字典信息，datafiles 是物理数据⽂件。

• 数据库 OPEN 下不能删除的表空间是

  1. system
  2. active undo tablespace
  3. default temporary tablespace
  4. default tablespa