【基础】Oracle 表空间和数据文件

多个表空间的优势:
1.能够将数据字典与用户数据分离出来，避免由于字典对象和用户对象保存在同一个数据文件中而产生的I/O冲突
2.能够将回退数据与用户数据分离出来，避免由于硬盘损坏而导致永久性的数据丢失
3.能够将表空间的数据文件分散保存到不同的硬盘上，平均分布物理I/O操作
4.能够将某个表空间设置为脱机状态或联机状态，以便对数据库的一部分进行备份和恢复
5.能够将某个表空间设置为只读状态，从而将数据库的一部分设置为只读状态
6.能够为某种特殊用途专门设置一个表空间，比如临时表空间等，以优化表空间的使用效率
7.能够更佳灵活的为用户设置表空间限额

SYSTEM表空间内存储：
1.数据库的数据字典
2.所有PL/SQL程序的源代码和解析代码
3.数据库对象的定义

（所有表空间的数据文件总和不能超过创建数据库时指定的maxdatafiles参数的限制）

本地管理表空间:
1.在存储分配过程中不需要访问数据库，可以提高存储分配操作的速度
2.能够避免在表空间的存储管理操作中产生的递归现象
3.不会产生重做和撤销记录
4.简化DBA对表空间的管理操作
5.降低用户对数据字典的依赖性

字典管理方式的表空间:
表空间中所有存储空间的管理信息都保存在数据字典中，在进行存储空间管理时会产生回退和重做记录

本地管理方式的表空间:
表空间中所有存储空间的管理信息都保存在数据文件头部的位图中

（在9i中创建表空间默认就是本地管理方式.8i以前的版本，只能用字典管理方式的表空间.）

创建表空间(字典管理方式)
create tablespace dmusertbs
datafile ‘i:\oracle\oradata\dmusertbs.dbf‘ size 50M
extent management dictionary;

多个数据文件(字典管理方式)
create tablespace dmusertbs
datafile ‘i:\oracle\oradata\dmusertbs01.dbf‘ size 50M,
         ‘i:\oracle\oradata\dmusertbs02.dbf‘ size 50M,
         ‘i:\oracle\oradata\dmusertbs03.dbf‘ size 50M
extent management dictionary;

指定默认存储参数(字典管理方式)
create tablespace dmusertbs
datafile ‘i:\oracle\oradata\dmusertbs.dbf‘ size 50M
default storage(
   initial 256K
   next 256K
   minextents 2
   pctincrease 0
   maxextents 4096)
minimum extent 128K
logging
online
permanent
extent management dictionary;

创建表空间(本地管理方式)

1.create tablespace lmusertbs
  datafile ‘i:\oracle\oradata\lmusertbs.dbf‘ size 50M
  extent management local autoallocate;
  (oracle对区的分配进行自动管理,这是默认的设置,在autoallocate方式下, 表空间中最小的区为64K)

2.create tablespace lmusertbs
  datafile ‘i:\oracle\oradata\lmusertbs.dbf‘ size 50M
  extent management local uniform size 512K;
  (所有的区必须具有统一的大小,Uniform方式带来的最大优点是在表空间中不会产生任何存储碎片,
   如果在uniform关键字后面没有指定size参数的值,SIZE参数将使用1MB做为默认值)

3.create tablespace lmusertbs
  datafile ‘i:\oracle\oradata\lmusertbs.dbf‘ size 50M
  extent management local uniform size 512K
  segment space management auto;
  (具有自动段存储管理方式的表空间)

创建临时表空间(字典管理方式)
一个临时表空间可以被多个数据库用户共享, oracle只会为一个实例创建一个临时段,这个临时段被实例中所有的排序操作共享使用，但是临时段中的每一个区只能由一个事务使用.临时段在数据库启动后执行第一次排序操作时被创建.

注意：字典管理方式的临时表空间可以象普通表空间那样使用 alter tablespace 来进行更改
建议将initial和next参数设置成相同的值,并且应当是初始化参数sort_area_size和db_block_size
两个值之和的整数倍,pctincrease参数应当设置成0
create tablespace usertemp
datafile ‘i:\oracle\oradata\sort01.dbf‘ size 50M
extent management dictionary
default storage(
  initial 192K
  next 192K
  minextents 1
  pctincrease 0)
temporary;

创建临时表空间(本地管理方式,区的分配管理方式只能是uniform)
（在Oracle 9i中，强烈建议使用本地管理方式的临时表空间来代替字典管理方式的临时表空间）

create temporary tablespace lmtemp
 tempfile ‘i:\oracle\oradata\lmtemp01.dbf‘ size 50M
 extent management local uniform size 136K
 (必须使用tempfile子句,tempfile为临时数据文件,与普通数据文件相比,临时数据文件不能用alter database来创建)

对于本地管理方式的临时表空间,alter tablespace语句的作用仅仅是为临时表空间添加新的临时文件.
增加新的临时文件
alter tablespace lmtemp
 add tempfile ‘i:\oracle\oradata\lmtemp02.dbf‘ size 50M
(对于本地管理方式的表空间,alter tablespace语句的作用仅仅是为临时表空间添加新的临时文件,而不能对临时表空间进行其它任何修改)

表空间的管理主要包括修改默认存储参数，手工合并碎片，设置表空间的可用性等几个方面。尽量让表空间使用较少的数据文件，因为在某些操作系统中对一个进程可以同时打开的系统文件数量进行限制，由此会影响到同时处于联机状态的表空间数量。

修改表空间的默认存储参数
alter tablespace duusertbs
default storage(
  next 128K
  maxextents unlimited
  pctincrease 20);
(表空间创建之后不能对initial和minextents参数进行修改)

如果表空间的默认存储参数pctincrease不为0,oracle将定期启动SMON后台进程，合并表空间相邻的存储碎片,
如果pctincrease是0,SMON进程不会执行合并操作.
如果表空间中所有的区都具有相同的大小，则不需要手工合并存储碎片.

本地管理方式的表空间不需要进行存储碎片的合并操作，因为oracle会自动利用位图自动合并.
只需要在字典管理方式的表空间中手工合并存储碎片.

alter tablespace dmusertbs coalesce
(如果使用了coalesce子句，则不能在使用其它任何子句)

利用下面的查询可以看到表空间users中存在哪些存储碎片
SQL> select block_id,bytes,blocks
  2  from dba_free_space
  3  where tablespace_name=‘users‘
  4  order by block_id;

改变表空间的可用性:

1.alter tablespace user01 offline normal(正常方式)
oracle会执行一次检查点，将SGA区中与该表空间相关的脏缓存块都写入数据文件中，然后再关闭表空间对应的数据文件,下一次将表空间。恢复为联机状态时,不需要进行数据库恢复.

2.alter tablespace user01 offline temporary(临时方式)
oracle执行检查点的时候并不会检查各个数据文件的状态，即使某些数据文件不可用，oracle也会忽视这些错误,下一次将表空间恢复为联机状态时,可能需要进行数据库恢复.如果数据文件可用,oracle会将与该表空间相关的脏缓存块都写入数据文件中.

3.alter tablespace user01 offline immediate(立即方式)
oracle不会执行检查点，也不会检查各个数据文件的状态，而是直接将属于表空间的数据文件设置成脱机状态,下一次将表空间恢复为联机状态时,需要进行数据库恢复.运行在noarchivelog方式下的数据库不允许用这种方式切换到脱机状态.

恢复表空间为联机状态
alter tablespace user01 online;

设置表空间为只读状态
alter tablespace user01 read only

设置表空间为读写状态
alter tablespace user01 read write

删除表空间(不包括对应的数据文件)
drop tablespace users including contents;

删除表空间(包括对应的数据文件)
drop tablespace users including contents and datafiles;

表空间数据字典
v$tablespace 控制文件中获取的表空间的名称和编号信息
v$datafile 控制文件中获取的数据文件的名称和编号信息
v$tempfile 所有临时数据文件的基本信息
v$sort_segment 实例所创建的排序区的信息
v$sort_user 排序区的用户使用情况信息
dba_tablespaces 数据库中表空间的名称和编号信息
dba_segments 表空间中段的信息
dba_extents 表空间中区的信息
dba_free_space 表空间中空闲区的信息
dba_data_files 数据文件亦即所属表空间的信息
dba_temp_files 临时数据文件亦即所属表空间的信息

包括为表空间添加新的数据文件，更改已有数据文件的大小，名称或者位置。初始化参数db_files指定在SGA区中能够保存的数据文件信息的最大数量，也就是一个实例所能支持的数据文件的最大数量,能够在实例运行过程中修改它.

1. 创建表空间lmusertbs时将数据文件设置为自动增长方式
create tablespace lmusertbs
  datafile ‘i:\oracle\oradata\lmusertbs01.dbf‘ size 50M
  autoextend on
  next 5M
  maxsize 500M
  extent management local;
(next参数指定每次自动增长的大小,maxsize为数据文件的最大大小)

2. 在表空间lmusertbs上添加一个自动增长方式的数据文件
alter tablespace lmusertbs
  add datafile ‘i:\oracle\oradata\lmusertbs02.dbf‘ size 50M
  autoextend on
  next 5M
  maxsize 500M;

3.如果数据文件已经创建，将它设置成自动增长方式
alter database
  datafile ‘i:\oracle\oradata\dmusertbs01.dbf‘
  autoextend on
  next 5M
  maxsize 500M;

4.取消已有数据文件的自动增长方式
alter database
  datafile ‘i:\oracle\oradata\dmusertbs01.dbf‘
  autoextend off;

5.手工改变数据文件的大小:

将数据文件dmusertbs01.dbf增大为500MB

alter database datafile ‘i:\oracle\oradata\dmusertbs01.dbf‘ resize 500M;

单独改变数据文件的可用性(数据库运行在归档模式下):
alter database datafile ‘i:\oracle\oradata\dmusertbs01.dbf‘ online;(联机状态)
alter database datafile ‘i:\oracle\oradata\dmusertbs01.dbf‘ offline;(脱机状态)

单独改变数据文件的可用性(数据库运行在不归档模式下):
alter database datafile ‘/u02/oracle/oradata/user01.dbf‘ offline drop;
用offline drop可以确保使数据文件脱机时立即丢掉

6.改变数据文件的名称和位置:
A.要改变的数据文件属于同一个表空间

1.将包含数据文件的表空间设置为脱机状态
       alter tablespace users offline normal

2.在操作系统中重新命名或者移动数据文件

3.在数据库内部修改数据文件的名称或者位置(用alter tablespace ... rename datafile子句,to子句后的数据文件必须存在)
改变名称:
alter tablespace users
  rename datafile
    ‘i:\oracle\oradata\user01.dbf‘,
    ‘i:\oracle\oradata\user02.dbf‘
  to
    ‘i:\oracle\oradata\lmuser01.dbf‘,
    ‘i:\oracle\oradata\lmuser02.dbf‘;

改变位置:
alter tablespace users
  rename datafile
    ‘i:\oracle\oradata\user01.dbf‘,
    ‘i:\oracle\oradata\user02.dbf‘
  to
    ‘h:\oracle\oradata\user01.dbf‘,
    ‘h:\oracle\oradata\user02.dbf‘;

4.重新将表空间设置为联机状态
alter tablespace users online

5.备份控制文件

B.要改变的数据文件属于多个表空间
1.关闭数据库
2.在操作系统中重新命名或者移动数据文件
3.加载数据库,startup mount
4.在数据库内部修改数据文件的名称或者位置(用alter database ... rename file子句,to子句后的数据文件必须存在)
alter database
  rename file
    ‘i:\oracle\oradata\user01.dbf‘,
    ‘i:\oracle\oradata\temp01.dbf‘
  to
    ‘i:\oracle\oradata\lmuser01.dbf‘,
    ‘i:\oracle\oradata\lmtemp01.dbf‘;

5.alter database open;
6.备份控制文件

数据文件数据字典
DBA_DATA_FILES 数据库中所有数据文件的信息
DBA_TEMP_FILES 数据库中所有临时数据文件的信息
DBA_EXTENTS 表空间中已分配的区的描述信息，包括区所属的数据文件的编号
DBA_FREE_SPACE 表空间中空闲区的信息

删除表空间的数据文件
(适用情况:不小心给一个表空间增加了一个数据文件，或者你把文件大小设得过大，所以想把它删掉)

(注:Oracle不提供如删除表。视图一样删除数据文件的方法，数据文件是表空间的一部分，所以不能“移走”表空间。
 在对表空间/数据文件进行任何脱机、删除之前，首先对数据库进行一个全备份)

A:如果数据文件是所在表空间的唯一的数据文件，你只要简单地删除表空间即可：
DROP TABLESPACE <tablespace name> INCLUDING CONTENTS

B:如果你的表空间有多个数据文件，而你不需要表空间中的内容，或者你可以很容易重新产生表空间的内容，你可以使用
DROP TABLESPACE <tablespace name> INCLUDING CONTENTS;命令来从Oracle数据字典删除表空间、数据文件和表空间的内容。Oracle不会再访问该表空间中的任何内容。然后重新创建表空间并重新导入数据。

C:如果你的表空间有多个数据文件，而你还需保留该表空间中的其它数据文件中的内容，则你必须首先export出该表空间中的所有内容。为了确定表空间中包含那些内容，运行：
select owner,segment_name,segment_type
from dba_segments
where tablespace_name=‘<name of tablespace>‘

export出你想保留的内容。如果export结束，你可以使用DROP TABLESPACE tablespace INCLUDING CONTENTS. ，这样永久删除表空间的内容，使用操作系统命令物理删除数据文件，按所需数据文件重新创建表空间，把数据import至表空间。

注意：

ALTER DATABASE DATAFILE <datafile name> OFFLINE DROP命令不能允许你删除数据文件，它的目的是脱机该数据文件以删除表空间。如果在归档模式下，使用ALTER DATABASE DATAFILE <datafile name> OFFLINE DROP来代替OFFLINE DROP。一旦数据文件脱机，Oracle不会再访问该数据文件的内容，但它仍然是表空间的一部分。这个数据文件在控制文件中标记OFFLINE，在数据库启动时不会对它与控制文件进行SCN的比较。在控制文件中保留这个数据文件的入口是方便以后的恢复。

如果你不想按照上述方法来删除表空间，还有其它一些解决方法。

1.如果你想删除数据文件的原因是因为分配了不合适的文件大小，你可以考虑RESIZE命令。
2.如果你不小心增加了一个数据文件，而这个文件还没有分配空间，你可以使用

ALTER DATABASE DATAFILE <filename> RESIZE;命令使其小于5个 Oracle块大小，如果数据文件的大小小于这个，Oracle将不会进行扩展数据文件。在以后，Oracle可以重建的时候来剔除这个不正确的文件。

---------------------------------------------------------------------------------------
<例子>
Question:

比如我的uses tablespace 有2个数据文件：users01.dbf 和 users02.dbf ,（数据库为非归档模式）假如我执行
alter tablespace users offline;
alter database datafile users02.dbf offline drop；
这时如果我在操作系统级别上删除users02.dbf,下次重启时会提示丢失文件，
我估计此命令只是在controlfile 级别做了修改，但dictionary中还是记录有这个文件，请问如何完全删除表空间中的一个数据文件？

ASK:
在noarchivelog mode， alter database datafile ...offline drop;
必须加上drop 选项，但是drop选项，并不从database 移除datafile。为了drop datafile， 你必须删除datafile所在的表空间。用drop选项，datafile任然保留在数据字典中，状态为recover 或者offline。
 
alter database datafile ...offline drop;
一旦数据文件offline，oracle不再访问那个datafile，但是它still是那个表空间的一部分。datafile在controlfile中被标记为offline，在startup的时候，在controlfile和datafile之间并没有对scn 做比较（这也运行你startup数据库，伴随一个不关键的datafile丢失）。datafile并没有从controlfile上删除，从而给你recover the datafile。
 
如果你真的想删除user02.dbf, 你可以用transport tablespace特性，或者导出你想要保存的在user tablespace空间的object， 重新建立一个new tablespace

If you do not wish to follow any of these procedures, there are other things 
that can be done besides dropping the tablespace.
 
If the reason you wanted to drop the file is because you mistakenly created 
the file of the wrong size, then consider using the RESIZE command.  
 
If you really added the datafile by mistake, and Oracle has not yet allocated  any space within this datafile, then you can use ALTER DATABASE DATAFILE <filename> RESIZE; command to make the file smaller than 5 Oracle  blocks.  If the datafile is resized to smaller than 5 oracle blocks, then it will never be considered for extent allocation. At some later date, the tablespace can be
rebuilt to exclude the incorrect datafile.

--原文地址：http://blog.csdn.net/zhaokeke2004/archive/2005/03/30/334388.aspx