LLL的数据库培训-15-第二部分—Oracle基础知识培训—第7讲—Oracle11g的表空间和数据文件管理

LLL的数据库培训—第二部分—Oracle基础知识培训—第7讲—Oracle11g的表空间和数据文件管理

一、表空间与数据文件的关系

Oracle中表空间是一个或多个数据文件的样集合，而数据文件实际上是存储模式对象数据的一个容器/仓库。

一个数据库一般有多个表空间，每个表空间都由一个或多个数据文件组成，但每个数据文件只能属于一个表空间，而每个表空间只能属于一个数据库。

1、Oracle引入逻辑结构

Oracle数据库管理系统并没有像其他数据库管理系统那样直接地操作数据文件，而是引入了一组逻辑结构，如图所示：

上图虚线左边为逻辑结构，右边为物理结构。

在数据库Oracle数据库中，逻辑结构为 Oracle引入的结构，而物理结构为操作系统所拥有的结构表空间数据文件。

2、Oracle为什么要引入逻辑结构呢?

（1）首先是为了增加 Oracle的可移植性

Oracle公司声称其 Oracle数据库是与平台无关的，即在某一厂家的某个操作系统上开发的 Oracle数据库(包括应用程序等可以几乎不加修改移植到另一厂家的另外的操作系统上，要做到这一点就不能直接操作数据文件，因为数据文件是与操作系统相关的。

（2）其次是为了减少 Oracle从业人员学习的难度

因为有了逻辑结构 Oracle的从业人员就可以只对逻辑结构进行操作，而在所有的平台上逻辑结构的操作都几乎完全相同，至于从逻辑结构到物理结构的映射(转换)是由 Oracle数据库管理系统来完成的

3、Oracle数据库中存储结构之间的关系

上图，圆角型方框为实体，实线表示关系，单线表示单一的关系，三线（鹰爪线）表示多的关系，于是可以得到：

（1）每个数据库是有一个或多个表空间组成的（至少一个）

（2）每个表空间基于一个或多个操作系统的数据文件（至少一个）

（3）每个表空间中可以存放一个或多个段（segment）

（4）每个段有一个或多个区（extent）组成

（5）每个区是有一个或多个连续的oracle数据块组成

（6）每个oracle数据块是有一个或多个操作系统数据块组成

（7）每个操作系统数据文件是有一个或多个区组成

（8）每个操作系统数据文件是有一个或多个操作系统数块组成。

4、表空间和数据文件之间的关系

Oracle将数据逻辑地存放在表空间里而物理地存放在数据文件里。

（1）表空间（tablespaces)在任何一个时刻只能属于一个数据库，

（2）一个数据库可以有多个表空间，

（3）每个表空间都是由一个或多个操作系统的数据文件组成的，

（4）一个操作系统的数据文件只能属于一个表空间.

表空间可以被进一步划分成一些更小的逻辑存储单位，在一个Oracle数据库中，每个数据文件(datflie)可以而且只能属于一个表空间和一个数据库，数据文件实际上是存储模式对象数据的一个容器仓库在。

5、表空间的分类

一个 Oracle数据库中一般有两类表空间，分别是系统(system)表空间和非系统(non-system)表空间.

（1）系统表空间

系统表空间包括了两个表空间:一个是 SYSTEM表空间，另一个是 SYSAUX(系统辅助)表空间。它们是在创建数据库时自动创建的必须存在的表空间，这两个表空间通常必须联机。

-- SYSTEM表空间存放支持数据库核心功能的表，如数据字典表，并且还包含了系统还原(回滚)段。

--SYSAUX表空间存放了一些附加的数据库组件，例如企业管理器资料库( Enterprise Manager Repository)要使所有数据库组件正常运行，该表空间必须处于联机状态。

注：SYSAUX表空间是 Oracle 10g引入， Oracle 10g将之前版本中几个不同的存放系统工具或组件的表空间统一合并成了一个SYSAUX表空间，其目的主要是方便管理和维护，

虽然在 SYSTEM表空间和 SYSAUX表空间中可以存放用户数据，但考虑到 Oracle系统的安全和效率，以及管理上的方便，在系统表空间上不应该存放任何用户数据。

SYSAUX表空间可以设置为脱机以执行表空间恢复，而 SYSTEM空间不能设置为脱机。这两个表空间都不能设置为只读。

另外，在 Oracle10g. Oncle11g Oracle 12c中还可以建大文件表空间(大小为8TB-128TB，但默认创建的依然是小文件表空间。

（2）非系统表空间

非系统(non-system)表空间可以由数据库管理员创建，在非系统表空间中存储一些单独的段，这些段可以是用户的数据段、索引段，还原段和临时段等。

引入非系统表空间可以方便磁盘空间的管理，也可以更好地控制分配给用户磁盘空间的数量，引入非系表空间还可以将静态数据和动态数据有效地分开，也可以按照备份的要求将数据分开存放，可以使用如下的命令创建一个非系统表空间。

6、创建表空间语法：

CREATE TABLESPACE tablespacename DATAFILE 'filename' [SIZE integer [K|M]] [AUTOEXTEND [OFF|ON]];

create tablespace  表空间名      --所要创建的表空间名
[datafile 子句]                  --组成所要创建的表空间的文件  
[Minimum extent K|M]            --表空间中所使用的每个EXTENT都必须是该参数所指定数的整数倍。
[BlockSIZE K]                   --为改表空间说明非标准块的大小。在使用该子句之前，必须先设置DB_CACHE_SIZE和DB_nK_CACHE_SIZE参数，而且改子句中所说明的正整数一定与DB_nK_CACHE_SIZE参数的设定相对应。默认8k
[LOGGGING|NOLOGGING]            --LOGGING:说明在该表空间中所有数据的变化都将写入重做日志文件中，默认方式是logging
                                --NOLOGGING：说明该表空间中所有数据的变化不都写入重做日志文件中，NOLOGGING只影响一些DML和DDL命令。
[DEFAULT 存储子句]               --说明所有在改表空间中所创建的对象的默认存储参数。
[ONLINE|OFFLINE]                --该表空间在创建后是立即在线还是脱机,默认为ONLINE
[PREMANTENT|TEMPORARY]          --指出表空间的属性，是永久表空间还是临时表空间。   
                                  永久表空间存放的是永久对象，临时表空间存放的是session生命期中存在的临时对象。   
                                  这个参数生成的临时表空间创建后一直都是字典管理，不能使用extent management local选项。   
                                  如果要创建本地管理表空间，必须使用create temporary tablespace   
                                  注意，声明了这个参数后，不能声明block size   
EXTENT MANAGEMENT LOCAL AUTOALLOCATE/UNIFORM ---默认参数，区的管理，分本地管理和数据字典管理，9i后默认是本地（local)管理。（local下又分autoallocat/uniform管理） BLOCKSIZE 8K ---默认参数，如果不设置，是默认数据库创建时的数值；如果想要用大块，在此处设置
SEGMENT SPACE MANAGEMENT AUTO    ---默认参数，段空间自动管理，手动为MANUAL
FLASHBACK ON;                    --默认参数，闪回打开。前提是本地闪回已经开启

二、表空间中的磁盘空间管理

1、数据字典管理方法

oracle 8i及更早版本，表空间中的磁盘空间管理是由数据字典管理的。

所有的空闲区由数据字典来统一管理。每当区段被分配或收回时，Oracle服务器将修改数据字典中相应的（系统）表。

优点：

--此方法，可以按使用者的需要修改存储参数，所以存储管理比较灵活，但系统的效率较低。

缺点：

--导致系统效率较低；
--有时需要合并碎片；
--随着数据库规模变大，每个表空间的管理系统都存在数据字典中，也就是存在系统表空间中，这样系统表空间就有可能成为一个瓶颈从而使数据库系统的效率迅速地下降。

2、本地管理方法

为了克服以上缺点，Oracle从8i引入另一种表空间管理方法：本地管理的表空间。

优点：

--本地管理的表空间其空闲 EXTENTS是在表空间中管理的，它是使用位图(bitmap)来记录空闲EXTENTS，位图中的每一位对应于一块或一组块，而每位的值指示空闲或分配。
--当一个 EXTENT被分配或释放时， Oracle服务器就会修改位图中相应位的值以反映该 EXTENT的新的状态。位图存放在表空间所对应的数据文件的文件头中。
--使用本地管理的表空间减少了数据字典表的竞争，而且当磁盘空间分配或收回时也不会产生回滚(还原)，不需要合并碎片。

缺点：

--在本地管理的表空间中无法按实际需要来随意地修改存储参数，所以存储管理不像数据字典(系统)管理的表空间那样灵活，但系统的效率较高。

--因为在本地管理的表空间中，表空间的管理（如磁盘空间的分配与释放）已经不再需要操作数据字典了，所以系统表空间的瓶颈问题得到了很好的解决。因此Oracle公司建议用户创建的表空间应该尽可能地使用本地管理的表空间。

在Oracle9i或以上版本中，本地管理的表空间为默认方式，但是在oracle 8i中，数据字典管理的表空间为默认方式。

三、表空间的各项管理

1、创建本地管理的表空间

create tablespace test_tbs datafile '/oracle/oradata/test01.dbf' size 100m autoextend on next 100m 
extent management local
uniform size 1M;

2、还原表空间的创建

还原表空间是Oracle9i开始引入的，它是用来自动地管理还原（回滚）数据的。还原表空间是用来存储还原段的，在还原表空间中不能包含任何其他的对象。

还原表空间中的区段（extent）是由本地管理的，而且在创建还原表空间的SQL语句中，只能使用DATAFILE和EXTENT MANAGEMENT子句。

create undo tablespace test_undo datafile '/oracle/oradata/undotbs03.dbf' size 10m;

3、临时表空间的创建

（1）临时表空间主要是作为排序操作使用的

--当在用户的SQL语句中使用了诸如 ORDER BY、 GROUP BY子句时, Oracle服务器就需要对所选取的数据进行排序,如果排序的数据量很大,内存的排序区(在PGA中)就可能装不下,因此 Oracle服务器就要把一些中间的排序结果写到磁盘上,即临时表空间中。
--当用户的SQL语句中经常有大规模的多重排序而内存的排序区不够时,使用临时表空间就可以改进数据库的效率。
--临时表空间可以由多个用户共享,在其中不能包含任何永久对象。
--临时表空间中的临时段是在实例启动后当有第一个排序操作时创建的,临时段在需要时可以通过分配 EXTENTS来扩展,并一直可以扩展到大于或等于在该实例上所运行的所有排序活动的总和。
--当创建临时表空间时，必须使用标准数据块。

（2）表空间创建

create temporary tablespace temp2 tempfile '/oracle/oradata/temp02.dbf' size 100m;

（3）本地管理的临时表空间特性

本地管理的临时表空间是基于临时数据文件( temp files)的,这些文件与普通的数据文件非常相似,但它们还具有如下特性:

--临时数据文件的状态不能置为只读。
--不能将临时数据文件重新命名。
--临时数据文件总是置为 NOLOGGING状态。
--不能使用 ALTER DATABASE命令创建临时数据文件以只读方式运行的数据库需要临时数据文件。
--介质恢复是不能恢复临时数据文件的。
--另外,为了优化某一临时表空间中排序的效率,还应将 UNIFORM SIZE设为 SORT AREASIZE(PGA中排序区的大小)参数的整数倍。

4、默认临时表空间的管理

--默认临时表空间是在 Oracle9 9i开始引入的。
--如果在创建一个数据库时没有设定默认临时表空间,那么任何一个用户在被创建时，如果没有使用 TEMPORARY TABLESPACE子句,他就将使用 system表空间作为排序区。
--这将使 system表空间碎片化，从而降低了数据库系统的效率。
--如果在创建一个数据库时没有设定默认临时表空间，则 Oracle服务器将把 system表空间是默认临时表空间的报警信息写入报警文件。
--因此说明一个数据库范围的默认临时表空间可以消除使用系统(system)表空间对临时数据进行排序的现象，从而提高数据库系统的效率。
--默认临时表空间既可以在创建数据库时同时创建，也可以在数据库创建之后单独创建。
--在创建数据库的同时创建默认临时表空间需使用 CREATE DATABASE命令，在数据库创建之后改变默认临时表空间需使用 ALTER DATABASE命令。

（1）首先可以利用数据字典 database_properties,查看默认临时表空间是什么？

select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

（2）创建新的临时表空间

create temporary tablespace temp2 tempfile '/oracle/oradata/temp02.dbf' size 100m;

（3）更改新的临时表空间为默认临时表空间

alter database default temporay tablespace temp2;

5、设置表空间为脱机

一个表空间的正常状态是联机(online)状态,此时数据库用户可以访问该表空间中的数据。

然而有时数据库管理员需要将某一表空间设置为脱机状态,以进行数据库的维护。

（1）维护工作包括:

--在数据库处于打开状态下移动数据文件；
--在数据库处于打开状态下恢复一个表空间或一个数据文件；
--执行对表空间的脱机备份(虽然对表空间可以进行联机备份)；
--使数据库的一部分不可以被访问,而其他的部分可以被正常地访问；
--当一个表空间被设置为脱机状态时,该表空间上的数据是不可以访问的。如果用户试图访问该表空间上的数据就会收到出错信息。
--当一个表空间被设置为脱机状态时或重新被设置为联机状态时, Oracle会把这一事件记录在数据字典和控制文件中,也会记录在报警文件中。
--如果当数据库被关闭时,某一表空间为脱机状态,那么当数据库被加载(mount)和重新打开时,该表空间仍保持为脱机状态。

（2）并不是所有的表空间都可以被设置为脱机状态,以下的表空间不能被设置为脱机状态:

--SYSTEM表空间
--上面有活动的还原回滚段的表空间
--默认临时表空间。

（3）使表空间脱机

alter tablespace test2_tbs offline;

6、只读表空间

--当一个表空间处在只读(read-only)状态时,在该表空间中的数据只能进行读操作,也就是说在上面的数据是不会变化的,因此也就不需要重做日志的保护,
--所以操作只读状态表空间上的数据时,不会产生重做操作,这提高了系统的效率。
--由于只读表空间上的数据是不变的,所以对该表空间只要做一次备份就够了。
--这不但减少了数据库系统的维护工作量,同时减轻系统的负荷。
--因此,如果将一个数据库精心设计并把许多不变的数据归类放在一个或几个表空间中,然后将它们的状态改为只读,将会使该数据库的维护更加容易且效率更高。
--当使用命令将一个表空间的状态改为只读时,一开始该表空间处于一种中间的状态。
--在这种状态下除了那些没有完成的事务之外在该表空间中不能进行任何的写操作,一旦那些没有完成的事务结束(既可以是提交也可以是回滚),该表空间就被置为只读状态。
--将一个表空间的状态改为只读时, Oracle会产生检查点。可以删除只读表空间中的对象,如表和索引,这是因为删除对象的命令是DDL语句,它们只修改数据字典而不是数据文件。
--将一个表空间的状态改为只读时或反过来将一个表空间的状态改为可读可写(正常)状态时,该表空间必须处于联机状态。

（1）更改表空间为只读状态

ALTER TABLESPACE LLLTEST READ ONLY;

--插入数据测试

案例

（2）更改表空间为可读可写（正常）状态

SQL> ALTER TARLESPACE LLLTEST READ WRITE;

--查看表空间状态

SQL> select tablespace_name,status,contents from dba_tablespaces;

--插入数据测试

案例

7、重置表空间大小

（1）数据字典管理的表空间

如果是数据字典管理的表空间,在创建表空间时所设置的存储参数不合适,可以使用如下的命令进行修改:

ALTER TABLESPACE 表空间名
[MINIMUM EXTENT]  正整数 K M
[DEFAULT 存储子句]

（2）本地管理的表空间

如果是本地管理的表空间,则表空间的存储设置是不能改变的。但用户可以重新设置表空间的大小。可以通过如下的方法来增加表空间的大小;

--改变数据文件的大小

在创建表空间时使用AUTOEXTEND ON 自动改变(扩展)数据文件的大小;

在创建表空间之后使用带有 AUTOEXTEND ON选项的 ALTER DATABASE命令,手动地开启自动扩展数据文件大小的功能。

--使用ALTER TABLESPACE语句来增加数据文件

可以利用数据字典 dba_data_files使用类似于例6-41的查询语句来确定哪些表空间或数据文件可以自动扩展。

SQL>col file name for a40;
SQL>set line 100;
SQL> col tableapace name for a15;
SQL> SELECT file_id, tablespace_name, file_name, autoextensible FROM dba_data_files  WHERE file name LIKE 'JIN4' ;

8、手工重置数据文件的大小

首先应利用数据字典 dba_data_files使用如下查询语句来确定 表空间所对应的数据文件的尺寸。

SQL> select * from dba_data_files;

如果表空间的容量不够了，数据库管理员可以使用 ALTER DATABASE来手工增加或减少数据文件的大小。

（1）通过改变某个数据文件的大小来增加表空间，就不用增加更多的数据文件。--扩容

SQL>alter database datafile '/data/u01/app/oracle/oradata/orcl2/users01.dbf' resize 100m;

（2）通过增加数据文件的个数来增加表空间--扩容

SQL>alter tablespace add datafile '/data/u01/app/oracle/oradata/orcl2/users04.dbf' size  100m;

（3）用户也可以通过手工重置某个数据文件的大小来重新收回数据文件中没用的空间。--收缩

SQL>alter database datafile '/data/u01/app/oracle/oradata/orcl2/users01.dbf' resize 100m;

如果所说明的数据文件的大小已经小于数据文件中所存的全部对象的大小总和，那么数据文件的大小将只被减少到数据文件中最后一个对象的最后一个数据块。

假设某个表空间中的磁盘空间已经接近用完，此时若想手工地将该表空间所对应的一个数据文件增加到100MB。

案例：

--查看当前表空间对应的数据文件及大小

select file_name,file_id,tablespace_name,bytes/1024/1024 total_mb from dba_data_files where tablespace_name='TEST_TBS02';
--100m

--查看当前表空间下的用户和表

select * from all_tables where tablespace_name='TEST_TBS02';
--TEST02_T
select count(*) from test02.TEST02_T;

--向表TEST02_T中疯狂的插入数据

insert into test02.test02_t values(1,'LLL');
insert into test02.test02_t select * from test02.test02_t;--重复数次到8388608行

--查看当前表空间对应的数据文件及大小

select file_name,file_id,tablespace_name,bytes/1024/1024 total_mb from dba_data_files where tablespace_name='TEST_TBS02';
--150m

----truncate表

truncate table test02.test02_t;

--查看当前表空间对应的数据文件及大小

select file_name,file_id,tablespace_name,bytes/1024/1024 total_mb from dba_data_files where tablespace_name='TEST_TBS02';
--150m

alter database datafile '/data/oradata/mesorcl/test02.dbf' resize 120m;
alter database datafile '/data/oradata/mesorcl/test02.dbf' resize 90m;

--查看当前表空间对应的数据文件及大小

select file_name,file_id,tablespace_name,bytes/1024/1024 total_mb from dba_data_files where tablespace_name='TEST_TBS02';
--90m

--查看数据文件变化

[oracle@localhost mesorcl]$ ll test02.dbf 
-rw-r----- 1 oracle oinstall 125837312 Oct 19 10:41 test02.dbf
[oracle@localhost mesorcl]$ ll test02.dbf 
-rw-r----- 1 oracle oinstall 104865792 Oct 19 10:43 test02.dbf
[oracle@localhost mesorcl]$ ll test02.dbf 
-rw-r----- 1 oracle oinstall 94380032 Oct 19 10:43 test02.dbf

9、移动数据文件的方法

有时某个磁盘的IO可能过于繁忙,这可能影响到 Oracle数据库系统的整体效率,此时就应该将一个或几个数据文件移动到其他的磁盘上以平衡IO。

有时某个磁盘可能已经毁损,此时为了能使数据库系统继续运行,也要将一个或几个数据文件移动到其他的磁盘上, Oracle一共提供了两条移动数据文件的语句

（1）方法1：移动表空间（open模式，非系统表空间)

ALTER TABLESAPCE 表空间名  RENAME DATAFILE '文件名' to '文件名' ；

--适用条件：

该语句只适用于上面没有活动的还原数据或临时段的非系统表空间中的数据文件。

--要求：

在使用该语句时，表空间必须为脱机状态且目标数据文件必须存在，因为该语句只修改控制文件中指向数据文件的指针(地址)

--移动数据文件或重新命名数据文件的步骤如下：

①使用数据字典获取所需的表空间和数据文件的相关信息
②将表空间置为脱机,并查看
alter tablespace 表空间名 offline;
select * from dba_tablespaces;
③使用操作系统命令移动或复制要移动的数据文件
④执行ALTER TABLESPACE RENAME DATAFILE命令
⑤将表空间置为联机；
⑥使用数据字典获取所需的表空间和数据文件的相关信息
⑦如果需要,使用操作系统命令删除无用的数据文件

（2）方法2：移动数据文件(mount，所有数据文件）

ALTER DATABASE 数据库名  RENAME FILE 文件名,文件名1  to  文件名t,文件名
SQL> alter database mesorcl rename file '/oracle/app/oracle/oradata/mesorcl/test01.dbf' to '/oracle/app/oracle/oradata/mesorcl/12345/test01.dbf';

--适用条件：

该语句适用于系统表空间和不能置为脱机的表空间中的数据文件。

--要求：

要求在使用该语句时,数据库必须运行在加载(mount)状态且目标数据文件必须存在,因为该语句只修改控制文件中指向数据文件的指针(地址)

--移动数据文件或重新命名数据文件的步骤如下：案例

①使用数据字典获取所需的表空间和数据文件的相关信息;
②关闭数据库系统,
③使用操作系统命令移动或复制要移动的数据文件,
④将数据库置为加载状态,
⑤执行 ALTER DATABASE RENAME FILE命令,
⑥打开数据库系统,
⑦使用数据字典获取所需的表空间和数据文件的相关信息
⑧如果需要,使用操作系统命令删除无用的数据文件

11、删除表空间

（1）当一个表空间没用时,可以使用命令删除它。

（2）但是以下几种表空间不能删除:

第一种是系统表空间,
第二种是上面有活动段的表空间。

（3）删除表空间的SQL命令格式如下:

DROP TABLESPACE 表空间名  [INCLUDING CONTENTS  [AND DATAFILES] [CASCADE CONSTRAINTS] ]

其中:

INCLUDING CONTENTS 子句用来删除段。
AND DATAFILES 子句用来删除数据文件。
CASCADE CONSTRAINTS子句用来删除所有的引用完整性约束(referential integrity constraints)。

12、利用OMF来管理表空间

利用 Oracle管理文件OMF(Oracle Managed Files)来自动管理和维护表空间和所对应的数据文件是Oracle9i开始引入的。该方法简化了表空间和所对应的数据文件的管理和维护。

（1）设置参数

在使用OMF自动管理和维护表空间和所对应的数据文件之前，必须使用下列方式之一来定义DB_CREATEFILE_DEST参数:

--在初始化参数文件中设置该参数;
--使用 ALTER SYSTEM命令动态地设置该参数。

之后在表空间被创建时,数据文件将被自动地建立并存放在 DBCREATEFILE_DEST参数所定义的目录中,其文件的默认大小为100MB,并且 AUTOEXTEND参数被设置为 UNLIMITED

（2）案例