管理表空间和数据文件

在Oracle数据库中，表空间与数据文件之间的关系非常密切，这二者相互依存。

　　Oracle磁盘空间管理中的最高逻辑层是表空间（TABLESPACE），它的下一层是段（SEGMENT），并且一个段只能驻留在一个表空间内。段的下一层就是盘区，一个或多个盘区（EXTENT）可以组成一个段，并且每个盘区只能驻留在一个数据文件中。如果一个段跨越多个数据文件，它就只能由多个驻留在不同数据文件中的盘区构成。盘区的下一层就是数据块，它也是磁盘空间管理中逻辑划分的最底层，一组连续的数据块可以组成一个盘区。下图展示了数据库、表空间、数据文件、段、盘区、数据块及操作系统块之间的相互关系。

system模式下 查询表空间及包含的数据文件：

col tablespace_name for a10
col file_name for a50
col bytes for 999,999,999
select tablespace_name,file_name,bytes from dba_data_files order by tablespace_name;

Oracle默认表空间

• EXAMPLE:如果安装时选择“实例方案”，则此表空间存放各样例的数据
• SYSAUX：SYSTEM表空间的辅助空间。主要用于存储数据字典外的其它数据对象，这样可以减少SYSTEM表空间的负荷
• SYSTEM：存放数据字典，包括表、视图、存储过程的定义等。
• TEMP：存放SQL语句处理的表和索引的信息，比如，数据排序就占用此空间
• UNDOTBS1：存放撤销数据的表空间
• USERS：通常用于存放“应用系统”所使用的数据库对象。

创建表空间

　　为了简化表空间的管理并提高系统性能，Oracle建议将不同类型的数据对象存放到不同的表空间中。因此，在创建数据库后，数据库管理员（DBA）还应该根据具体应用的情况，建立不同类型的表空间。例如，建立专门用于存放表数据的表空间、建立专门用于存放索引或簇数据的表空间等，因此创建表空间的工作就显得十分重要，在创建表空间时必须考虑以下几点：

　　1.是创建小文件表空间还是大文件空间（默认为小文件表空间）

　　2.是使用局部盘区管理方式，还是使用传统的目录盘区管理方式（默认为局部盘区管理）

　　3.是手动管理段空间，还是自动管理段空间（默认为自动）

　　4.是否用于临时段或撤销段的特殊表空间。

　　创建表空间的语法

CREATE [SMALLFILE|BIGFILE] TABLESPACE tablespace_name
DATAFILE '/path/filename' SIZE num[k/m] REUSE
[,’/path/filename’ SIZE num[k/m] REUSE]
[,…]
[AUTOEXTEND [ON | OFF] NEXT num[k/m]
[MAXSIZE [UNLIMITED | num[k/m]]]]
[MININUM EXTENT num[k/m]]
[DEFAULT STORAGE storage]
[ONLINE | OFFLINE]
[LOGGING | NOLOGGING]
[PERMANENT | TEMPORARY]
[EXTENT MANAGEMENT DICTIONARY | LOCAL [AUTOALLOCATE | UNIFORM SIZE num[k/m]]]]

SMALLFILE|BIGFILE：表示创建的是小文件表空间还是大文件表空间

REUSE：表示若该文件存在则清除该文件再重新建立该文件，若该文件不存在，则创建该文件

AUTOEXTEND [ON | OFF] NEXT：
　　表示数据文件为自动扩展（ON）或非自动扩展（OFF）,如果是自动扩展，则需要设置NEXT的值。

MAXSIZE：当数据文件自动扩展时，允许数据文件扩展的最大长度字节数，如果指定UNLIMITED，则不需要指定字节长度

MININUM EXTENT：指定最小的长度，由操作系统和数据库的块决定

DEFAULT STORAGE：指定以后要创建的表、索引及簇的存储参数值，这些参数将影响以后表等的存储参数。

ONLINE | OFFLINE：在线或者离线

LOGGING | NOLOGGING：
　　指定该空间内的表再加载数据时是否产生日志，默认为产生日志。
　　即使设置为NOLOGGING，但在进行INSERT、UPDATE、和DELETE时，Oracle仍会将操作信息记录到Redo Log Buffer中

PERMANENT | TEMPORARY：指定创建的表空间是永久表空间还是临时表空间

EXTENT MANAGEMENT DICTIONARY | LOCAL：
　　指定表空间的扩展方式是使用数据字典管理还是本地化管理，默认为本地化管理。Oracle不推荐使用数据字典管理。

AUTOALLOCATE | UNIFORM SIZE：
　　如果采用本地化管理表空间，在表空间扩展时，指定每次盘区扩展的大小是由系统自动指定还是按照等同大小进行。
　　若是按照等同大小进行，则默认每次扩展的大小为1M。

通过本地化管理方式创建表空间

• 使用本地化的扩展功能（包括自动大小和等同大小），可以避免发生重复的空间管理操作。
• 本地化的自动扩展（AUTOALLOCATE)能够跟踪临近的自由空间，这样可以消除结合自由空间的麻烦。本地化的扩展大小可以根据系统自动确定（AUTOALLOCATE）也可以选择所有扩展有同样的大小(UNFORM),通常使用EXTENT MANAGEMENT LOCAL 子句创建本地化的可变空间。

　　案例：

　　1、通过本地化管理方式，创建一个大小为10M的表空间，其扩展大小为等同的256k

 CREATE TABLESPACE blog_data_tbs
 DATAFILE 'D:\oracle_19c\oradata\BLOG\DATAFILE\blog_data_tbs.DBF' SIZE 10m REUSE
 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256k;

　　2、通过本地化管理方式，创建一个大小为10M的表空间，其扩展大小为自动扩展

 CREATE TABLESPACE TEST1 
 DATAFILE 'D:\oracle_19c\oradata\BLOG\DATAFILE\TEST.DBF' SIZE 10M REUSE
 EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

通过段空间管理方式创建表空间

　　段空间管理方式是建立在本地化空间管理方式基础之上的，即只有本地化管理方式的表空间，才能在其基础上进一步建立段空间管理方式，它使用"SEGMENT SPACE MANAGEMENT MANUAL/AUTO"语句。

　　3、通过本地化管理方式，创建一个大小为10M的表空间，其扩展大小为自动扩展，其段空间的管理方式为手动管理（目的是向后兼容）

CREATE TABLESPACE test2 
DATAFILE 'D:\oracle_19c\oradata\BLOG\DATAFILE\TEST2.DBF' SIZE 10M REUSE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT MANUAL;

　　4、通过本地化管理方式，创建一个大小为10M的表空间，其扩展大小为自动扩展，其段空间的管理方式为自动管理（********）

　　由于自动段空间管理方式比手动段空间管理方式具有更好的性能，所以是创建表空间的首选方式

CREATE TABLESPACE test3 
DATAFILE 'D:\oracle_19c\oradata\BLOG\DATAFILE\TEST3.DBF' SIZE 10M REUSE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

　　注：自动段空间管理方式不能用于创建临时表空间和系统表空间

　　　　Oracle推荐使用自动段空间管理方式管理永久表空间，在创建表空间时需要明确指定为AUTO，因为默认为MANUAL。

维护表空间与数据文件

　　设置默认表空间　　

　　在Oracle数据库中创建用户时（CREATE USER），如果不指定表空间，则默认的临时表空间是TEMP，默认的永久表空间是SYSTEM，这样就导致应用系统与Oracle系统竞争使用SYSTEM表空间，会极大地影响Oracle系统的执行效率。为此，Oracle建议将非SYSTEM表空间设置为应用系统的默认表空间，并且将非TEMP临时表空间设置为应用系统的临时表空间。这样有利于数据库管理员根据应用系统的运行情况适时调整默认表空间和临时表空间。

 1、将临时表空间TEMP_1设置为默认的临时表空间 

-- 创建应用系统临时表空间
CREATE TEMPORARY TABLESPACE TEMP_DATA TEMPFILE 'E:/ORACLE_TABLESPACE/TEMP_DATA.DBF' SIZE 10M REUSE
EXTENT MANAGEMENT LOCAL;

ALTER DATABASE DEFAULT TEMPROARY TABLESPACE TEMP_1;

 2、将BLOG_DATA_TBS设置为默认表空间

ALTER DATABASE DEFAULT TABLESPACE BLOG_DATA_TBS;

　　更改表空间状态

　　表空间有只读和可写两种状态

　　　　只读：用户不能对该表空间的数据进行DML操作（INSERT，UPDATE，DELETE），但对某些对象的删除操作是可以进行的，比如索引和目录

　　　　只读状态可以保证表空间数据的完整性。通常在进行数据库的备份、恢复及历史数据的完整性保护时，可将指定的表空间设置成只读状态，设置只读状态的条件：

　　　　　　　　表空间必须为ONLINE状态

　　　　　　　　表空间不能包含任何回滚段

　　　　　　　　表空间不能在归档模式下

　　　　可写：用户可以对表空间中的数据进行任何正常的操作（默认）

ALTER TABLESPACE tablespace_name READ ONLY | READ WRITE

　　重命名表空间（方便管理和移植）

ALTER TABLESPACE tablespace_old_name RENAME TO  tablespace_new_name；

　　注：数据库管理员只能对普通的表空间进行更名，不能够对SYSTEM和SYSAUX表空间进行重命名，也不能对已经处于OFFLINE状态的表空间进行重命名

　　删除表空间

DROP TABLESPACE tbs_name[INCLUDING CONTENTS] [CASCADE CONSTRAINTS]

INCLUDING CONTENTS：选项表示表空间中存在数据时将数据一并删除，如果没有指定且表空间中存在数据，会拒绝删除

CASCADE CONSTRAINTS：级联删除相关约束

  维护表空间中的数据文件

• 查看表空间中的数据文件

SELECT TABLESPACE_NAME,FILE_NAME,BYTES,AUTOEXTENSIBLE FROM DBA_DATA_FILES;
SELECT * FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='AVATAR_DATA';

• 向表空间中添加数据文件

ALTER TABLESPACE AVATAR_DATA ADD DATAFILE 'E:\ORACLE_TABLESPACE\AVATAR_DATA2.DBF' SIZE 10M REUSE AUTOEXTEND ON NEXT 2M MAXSIZE UNLIMITED

• 从表空间中删除数据文件

ALTER TABLESPACE AVATAR_DATA DROP DATAFILE 'E:\ORACLE_TABLESPACE\AVATAR_DATA2.DBF';

•  对数据文件自动扩展进行设置(1.在创建时进行设置；2.使用alter子句)

ALTER DATABASE DATAFILE 'E:\ORACLE_TABLESPACE\AVATAR_DATA.DBF' AUTOEXTEND ON NEXT 2M MAXSIZE UNLIMITED;

 撤销表空间

 撤销表空间作用

• 使读写一致

　　　在不同的进程或用户模式下检索数据时，Oracle只能给用户提供被提交的数据，这样可以确保数据的一致性。

• 可以回退事务

　　    当执行修改（UPDATE）数据操作时，旧的数据（即UNDO数据）被存放到UNDO段，而新的数据则被存放到数据段中。

• 事务恢复

　　   事务恢复是例程恢复的一部分，它是由Oracle服务器自动完成的，如果在数据库运行过程中出现例程失败（如断电、内存故障等），那么当重启Oracle 服务器时，后台进程SMON会自动执行例程恢复。

• 闪回操作

　　   Oracle 11g新增了强大的闪回功能，其中，很多闪回技术都是基于UNDO段实现的，比如，闪回表，闪回事务查询，闪回版本查询等

　撤销表空间参数

• UNDO_TABLESPACE

　　　该初始化参数用于指定例程所要使用的UNDO表空间，使用自动UNDO管理模式时，通过配置该参数可以指定例程所要使用的UNDO表空间。

• UNDO_MANAGEMENT

　　    该初始化参数用于指定UNDO数据的管理模式，如果为AUTO，则为自动撤销管理模式，如果为MANUAL，则为回滚段管理模式。
　　    需要注意的是，使用自动撤销管理模式时，如果没有配置初始化参数UNDO_TABLESPACE，Oracle会自动选择第一个可用的UNDO表空间存放UNDO数据；如果没有可用的UNDO表空间，Oracle会使用  　　    SYSTEM回滚段存放UNDO记录，并在ALTER文件中记载警告。

• UNDO_RETENTION

　　　该初始化参数用于控制UNDO数据的最大保留时间，默认为900秒，从9i版本开始，通过配置该初始化参数，可以指定UNDO数据的保留时间，从而也决定了基于UNDO数据的闪回操作能够闪回的最早时间　　点。

撤销表空间管理

　　1、创建UNDO表空间

　　创建UNDO表空间需要使用CREATE UNDO TABLESPACE 语句

　　2、修改UNDO表空间

　　与修改普通的永久性表空间比较类似，修改UNDO表空间也使用ALTER TABLESPACE语句。当事务用尽了UNDO表空间后，可以使用ALTER TABLESPACE…ADD DATAFILE语句添加新的数据文件；当UNDO表空间所在的磁盘填满时，可以使用ALTER TABLESPACE…RENAME DATAFILE语句将数据文件移动到其它磁盘上；当数据库处于ARCHIVELOG模式时，可以使用ALTER TABLESPACE…BEGIN BACKUP/END BACKUP语句备份UNDO表空间。

　　3、切换UNDO表空间

　　启动例程并打开数据库后，同一时刻指定例程只能使用一个UNDO表空间，切换UNDO表空间是指停止例程当前使用的UNDO表空间，启动其它UNDO表空间

　　4、删除UNDO表空间

　　如果某个自定义的UNDO表空间确定不在使用了，数据库管理员就可以删除掉。删除UNDO表空间与删除普通的永久表空间一样，都使用DROP TABLESPACE语句。

　　5、查询UNDO表空间的信息

　　通过查询UNDO表空间的相关信息，可以给管理员提供决策和管理支持，管理员经常需要查看的UNDO表空间信息主要有以下几种。

临时表空间

　　临时表空间是一个磁盘空间，主要用于内存排序区不够而必须将数据写到磁盘的那个逻辑区域，由于该空间在排序操作完成后可以由Oracle系统自动释放，所以也称作临时表空间。

　　临时表空间主要用于临时段，而临时段是由数据库根据需要创建、管理和删除的，这些临时段的生成通常与排序之类的操作有关，下面的几种操作经常会用到临时表空间。
　　（1）SELECT DISTINCT不重复检索
　　（2）UNION联合查询
　　（3）MINUS计算
　　（4）ANALYZE分析
　　（5）连接两个没有索引的表
　　通常使用CREATE TEMPORARY TABLESPACE语句来创建临时表空间。
　　Oracle 11g将临时表空间与相应的临时文件信息存放在DBA_TEMP_FILES数据字典当中。在V$TEMPFILES视图中，可以查看到临时表的使用情况。
　　在Oracle 11g中，可以创建多个临时表空间，然后把它们组成一个临时表空间组，这样，应用系统中的数据在排序时就可以使用组里的多个临时表空间，在一个临时表空间组里至少有一个临时表空间，其最大个数没有限制，但是组的名字不能和其中某个临时表空间的名字相同。用户使用临时表空间组来管理临时数据具有以下作用：
（1）避免因大量的排序数据而导致单一临时表空间不足。
（2）当一个用户同时有多个会话时，可以使得它们使用组中的不同临时表空间。
（3）使并行的服务器在单节点上能够使用多个临时表空间。