表空间.txt

来自「本人在工程上收集的比较常用的oracle sql语句 有问题请发email:l」· 文本 代码 · 共 348 行 · 第 1/2 页

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;

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

将数据文件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可以确保使数据文件脱机时立即丢掉

改变数据文件的名称和位置:
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.