ORACLE数据库的物理结构和存储结构（1）

今日，耐着性子读了几页的书，趁着刚刚读完，赶紧将它记录下来，以备工作查询需要。
大致是内容与ORACLE存储结构与表空间操作相关

数据库逻辑结构与物理结构的关系如下：

存储结构：

数据块是ORACLE读写数据库的基本单位

select file_name,bytes/1024/1024 as mb,user_bytes/1024/1024 as user_mb ,autoextensible,increment_by,maxbytes/1024/1024 as max_mb from dba_data_files;

上图的1280就是指的这个数据文件下次自动扩展1280个数据块，如果每个块大小是8KB，则下一次扩展为10M
数据块分为标准块和非标准块，分别由db_block_size和db_nk_cache_size（n可以是2、4、8、16、32等，单位是KB）。
（1）标准块是在数据库创建后指定的，一旦创建好数据，便无法改变。
（2）非标准块如果没有指定过则可以指定大小，如果指定过大小但没有依赖它的表空间存在，则可以修改，否则便无法修改，非标准块的大小指定需要在SGA中定义相应的缓冲区缓存，且此缓存大小是该非标准块的倍数，定义了缓存，非标准块也就定义好了，假设定义缓存语句如下：

ALTER SYSTEM SET db_2K_cache_size=1024M;
#指定2KB的非标准块的缓存大小

若是创建该非标准块构成的表空间，则：

CREATE TABLESPACE xxx
DATAFILE '/u01/app/data/xxx01.dbf' size 2048M
BLOCKSIZE 2K; #其中2K指的是2KB的非标准块

表空间：

表空间分为：
（1）SYSTEM表空间
（2）SYSAUX表空间
（3）UNDO表空间
（4）临时表空间
（5）普通表空间
（1）SYSTEM表空间不能被删，不能OFFLINE,存储着数据库的系统数据，比如数据字典、数据对象的定义、存储程序代码、system回滚段dg等
（2）SYSAUX表空间存放一些其他的 metadata 组件，如 OEM,Streams 等会默认存放在 SYSAUX 表空间里(10g后引入，可以为SYSTEM表空间分担了一些压力)
（3）UNDO表空间由UNDO段组成，用于存储修改之前的数据，主要用于撤销修改操作、一致性读（保证提交修改事务前作出查询时，返回的记录为未发生变化的数据），以及实例恢复。
初始化参数：
a.UNDO_MANAGEMENT,指定管理方式：AUTO和MANUAL
b.UNDO_TABLESPACE,指定数据库使用哪个UNDO表空间，数据库只能使用一个UNDO表空间
c.UNDO_RETENTION，以秒为单位，提交了事务后，数据也会在UNDO中存在，不会立即删去，这个参数是设定保留时间

ALTER SYSTEM SET undo_management=AUTO SCOPE=SPFILE;
ALTER SYSTEM SET undo_tablespace=xxx;
ALTER SYSTEM SET undo_retention=1800;

（4）临时表空间由临时段组成，下面操作可能会用到临时表空间：排序（如果PGA无法满足排序）、在表上建立索引、对数据的导入导出、对数据的备份/恢复、对表select或insert，它的最大作用是排序，数据库重启后数据会清空。
查询数据库的默认临时表空间：

select property_value from database_properties where
property_name='DEFAULT_TEMP_TABLESPACE';

临时表空间的数据信息可以从DBA_TEMP_FILES和动态性能视图V$TEMPFILE中获得

select file_name,status,bytes/1024/1024 as MB from dba_temp_files;

有时可以从系统中找到临时表空间的文件，但是从视图V$TEMPFILE中得到的查询结果是空的，这时会报下面的错误：

ORA-25153:Temporary Tablespace is Empty
ORA-06512:在“SYS.DBMS_LOB”，line 520
ORA-06512:在“SYS_DBMS_DATAPUMP”,line 3901

其中第一行ORA-25153表示临时表空间出现故障，其余是临时表空间故障引发的问题，此时有两个处理方法：
a.重新创建一个新的临时表空间，并把它指定为数据库的默认表空间，以代替过去的临时表空间

CREATE TEMPORARY TABLESPACE XXX
TEMPFILE '/u01/app/data/XXX01.dbf' SIZE 500M
UNIFORM SIZE 128K
#临时表空间区的大小只能通过UNIFORM 指定，不能使用AUTOALLOCATE
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE XXX;
#指定默认临时表空间
ALTER USER xxx TEMPORARY TABLESPACE XXX;
#指定用户的默认临时表空间

b.为出现故障的临时表空间重新创建一个或多个数据文件，一代替过去的数据文件

ALTER TABLESPACE XXX
ADD TEMPFILE '/u01/app/XXX02.dbf' SIZE 500M;
ALTER TABLESPACE XXX
DROP TEMPFILE '/u01/app/XXX01.dbf';

从临时表空间中引申出临时表空间组，临时表空间组是为了避免争用资源，提高性能，降低维护,它不需要创建，也不需要单独删除。

ALTER TABLESPACE xx TABLESPACE GROUP xxx;
#创建临时表空间组同时将xx加入到xxx组中
AlTER TABLESPACE xx TABLESPACE GROUP '';
#将xx移出xxx组
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE xxx;
#指定数据库的临时表空间为xxx临时表空间组
SELECT tablespace_name FROM  dba_tablespace_groups
WHERE group_name='xxx';
#查询
ALTER TABLESPACE xx SHRINK SPACE KEEP 200m;
#对xx临时表空间的空间进行回收，缩小到200M
ALTER TABLESPACE xx SHRINK SPACE
ALTER TABLESPACE xx SHRINK  TEMPFILE '/u01/app/data/xx01.dbf'
#如果没指定大小，那么临时表空间或数据文件将缩小为可能的最小大小

表空间的操作

表空间创建

CREATE TABLESPACE xxx 
DATAFILE '/u01/app/data/xxx01.dbf' SIZE 1024M
AUTOEXTEND ON 
--自动扩展
NEXT 50M MAXSIZE 20480M 
--下次扩展50M 数据文件最大2G
EXTEND MANAGEMENT LOCAL  
--区管理方式为本地（区管理分为本地LOCAL和字典管理DICTIONARY）
AUTOALLOCATE
--设置区大小为默认的64KB（还可指定区大小，例UNIFORM SIZE 16K）
SGMENT SPACE MANAGMENT AUTO;  
--段管理为自动（段管理分为自动和手动MANAUL），自动有利于消除断中的存储-碎片，还可以把相邻的未被写满的数据块合并为一个数据块，提高存储空间利用率

表空间的ALTER

ALTER TABLESPACE xxx AUTOEXTED ON NEXT 500M MAXSIZE 30G;
#开启自动扩展

CREATE BIGFILE TABLESPACE XXX DATAFILE '/u01/app/data/xxx01.dbf' SIZE 50G;
#创建大文件类型的表空间，数据库默认的数据文件是smallfile
#另有大文件类型bigfile，只有一个数据表文件，可突破操作系统对单个文件大小的限制，最大可达4G个数据块
#大文件表空间的区管理方式必须是本地管理，段管理必须是auto
#采用本地管理的区管理方式和AUTO段管理方式的system表空间、临时表空间和undo表空间不能是大文件表空间。
SELECT property_value FROM database_properties  WHERE properity_name='DEFAULT_TBS_TYPE';
#查看数据库默认表空间类型 
ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE;
#修改数据库的默认表空间类型

ALTER TABLESPACE XXX RESIZE 200g;
#修改表空间大小
ALTER TABLESPACE XXX ADD DATAFILE '/u01/app/data/xxx02.dbf' size 1024M AUTOEXTEND ON NEXT 5M MAXSIZE 30G;
#新增数据文件，并且允许数据文件自动增长
ALTER DATABASE DATAFILE '/u01/app/data/xxx01.dbf'  
AUTOEXTEND ON NEXT 5M MAXSIZE 100M; 
#允许已存在的数据文件自动增长
ALTER DATABASE DATAFILE '/u01/app/data/xxx01.dbf'  
RESIZE 100M;
#手工改变已存在数据文件的大小
ALTER TABLESPACE DROP DATAFILE '/u01/app/data/xxx02.dbf';
#如果数据文件还没有写入数据，则可以删除

DROP TABLESPACE INCLUDING CONTENTS;
#删除表空间与下面的对象，需要去操作系统中手动删除数据文件
DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES;
#删除表空间、数据对象及其数据文件

SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 AS MB FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;
#查询剩余空间

表空间状态

分为ONLINE和OFFLINE

ALTER TABLESPACE XXX OFFLINE/ONLINE;
#修改状态
ALTER DATABASE DATAFILE '/u01/app/data/xxx02.dbf'；
#修改数据文件的状态 

对于数据而言，可设置表空间为只读和可写权限

ALTER TABLESPACE XXX READ ONLY;
ALTER TABLESPACE XXX READ WRITE;

数据文件的移动

(1)把一个表空间的数据文件移动到另一路径或改名
步骤：
a.将表空间状态改为OFFLINE

ALTER TABLESPACE XXX OFFLINE;

b.在操作系统中对数据文件重新命名，或复制到新路径下
c.利用sql修改数据文件名称或路径，将数据文件的新命名和路径重新写入控制文件，修改路径语句如下

ALTER TABLESPACE XXX RENAME DATAFILE '/u01/app/data/xxx01.dbf',
'/u01/app/data/xxx02.dbf' TO
 '/u02/app/data/xxx01.dbf','/u02/app/data/xxx02.dbf';

d.将表空间状态改为ONLINE
f.备份控制文件
（2）如果要对多个数据文件进行移动或命名，而这些数据文件不属于一个表空间，则：
需要在a步骤中，把所有相关表空间设为OFFLINE,或者把数据库切到MOUNT(表空间状态不用改变)，c步骤中执行ALTER DATABASE RENAME FILE '/u01/app/data/xxx01.dbf' to '/u02/app/data/xxx02.dbf';
（3）如果文件属于SYSTEM表空间、数据库默认的临时表空间或者当前正在使用的UNDO表空间，那么需要按（2）的方式操作。
以上是在文件系统中进行移动，如果是在文件系统和ASM磁盘组间移动，则需要借用rman工具或DBMS_FILE_TRANSFER的COPY_FILE（需要创建目录对象和授权）;
使用DBMS_FILE_TRANSFER如下：
a.对路径定义目录对象

CREATE DIRECTORY source_xxx as '/u01/app/data'；
CREATE DIRECTORY target_xxx as '/u01/app/data';

b.授权

GRANT EXECUTE ON DBMS_FILE_TRANSFER TO XXX;
GRANT READ ON DIRECTORY source_xxx TO XXX;
GRANT WRITE ON DIRECTORY target_xxx TO XXX;

c.执行

BEGIN 
DBMS_FILE_TRANSFER.COPY_FILE(
source_directory_object=>'source_xxx',
source_file_name=>'XXX01.dbf',
destination_directory_object=>'target_xxx',
destination_file_name=>'XXX011.dbf'
) ;