Oracle 逻辑物理结构

1. 概念

数据库、表空间和数据文件是密切相关的，但是它们有重要的区别:
Oracle数据库至少由两个称为表空间的逻辑存储单元组成，它们共同存储数据库的所有数据。必须拥有SYSTEM和SYSAUX表空间。
Oracle数据库中的每个表空间由一个或多个名为datafiles的⽂件组成，这些文件是Oracle数据库运行的
操作系统上的物理结构文件。
数据库的数据是集中存储在组成数据库的每个表空间的数据文件中。

2. 逻辑数据库结构

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

2.1 Tablespace type 表空间类型

表空间类型

Permanent 永久表空间
Temporary 临时表空间
UNDO 回滚表空间
表空间管理方式

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

3. SYSTEM系统表空间

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

当SYSTEM表空间是本地管理方式时，方法创建字典管理的表空间。
SYSTEM表空间包含以下信息：
数据字典：系统表空间总是包含整个数据库的数据字典表。
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)，与普通数据文件类似，但有以下例外:

Tempfiles总是设置为NOLOGGING模式。
不能设置tempfile为只读状态。
不能使用ALTER DATABASE语句创建tempfile。
数据恢复时不识别tempfiles:
• 备份控制文件不会为tempfile生成任何信息。
• 创建控件文件不能指定关于tempfile的任何信息。
除了系统表空间之外，您可以在任何时候Offline一个联机(Online）表空间。当您分别将表空间脱机或联机时，表空间的所有数据文件将作为一个单元online or Offline。
您可以将单个数据一件脱机。但是，这通常只在某些数据库在做数据恢复的过程中完成

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

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

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

PCTFREE 20

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

8. 表空间与数据文件

8.1 增加表空间大小的方式

可以通过以下三种方式扩大数据库:

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

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

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

8.2 Data File 数据文件

Oracle数据库中的表空间由一个或多个物理数据文件组成。数据文件只能与一个表空间和一个数据库相关联。
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

8.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;

插入数据

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

表空间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;

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

查看表空间信息

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

删除表空间及其数据文件

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

8.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

8.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

8.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；

9. 查看表空间缺省选项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

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

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

11. 删除表空间

drop tablespace test including contents and datafiles;

contents 包括控制文件和数据字典信息，datafiles 是物理数据文件。

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

system
active undo tablespace
default temporary tablespace
default tablespa