Oracle 删除数据文件基本命令（转）

在我们详细介绍之前，我们必须说清楚一点：Oracle不提供如删除表、视图一样删除数据文件的方法，数据文件是表空间的一部分，所以不能“移走”表空间。

 

一、使用offline数据文件的方法

非归档模式使用：alter database datafile '...' offline drop;

归档模式使用：  alter database datafile '...' offline;

说明：

1)         以上命令只是将该数据文件OFFLINE，而不是在数据库中删除数据文件。该数据文件的信息在控制文件种仍存在。查询v$datafile，仍显示该文件。

2)         归档模式下offline和offline drop效果是一样的

3)         offline后，存在此datafile上的对象将不能访问

4)         noarchivelog模式下，只要online redo日志没有被重写，可以对这个文件recover后进行online操作

 

实际使用案例：

直接删除数据文件后无法进入系统的解决方案

 

正常情况下，删除表空间的正确方法为：

DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

 

如果没有通过以上命令删除而直接删除了数据文件，将导致数据库无法打开。

 

如果直接删除了数据文件

普通用户登录时，则报错：

ORA-01033: ORACLE initialization or shutdown in progress

sys用户可以正常登录

但进行操作时（SELECT count(1) FROM user_tables），则会报错：

ORA-01219: 数据库未打开: 仅允许在固定表/视图中查询

如果执行命令alter database open以打开数据库时，又报如下错：

ORA-01157: 无法标识/锁定数据文件 12 - 请参阅 DBWR 跟踪文件

ORA-01110: 数据文件 12: 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TSTEST001.DBF'

 

说明数据库没找到这个数据文件

因为数据文件在没有被offline的情况下物理删除了,导致oracle的数据不一致,因此启动失败.

通过以下方法即可解决

 

解决方法：

sqlplus sys/orcl@orcl as sysdba;

SQL> alter database datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TSTEST001.DBF' offline drop;

SQL> alter database open;

SQL> drop tablespace CTBASEDATA;

 

二、Oracle 10G R2开始，可以采用：Alter tablespace tablespace_name drop datafile file_name;来删除一个空数据文件，并且相应的数据字典信息也会清除:

 

sys@ORCL>select file_id,file_name,tablespace_name from dba_data_files

  2  where tablespace_name='USERS';

FILE_ID  FILE_NAME                        TABLESPACE_NAME

-------    --------------------------------------------       ------------------

4        /u01/app/oracle/oradata/orcl/users01.dbf    USERS

 

sys@ORCL>alter tablespace users add datafile

  2  '/u01/app/oracle/oradata/orcl/users02.dbf' size 5M autoextend off;

Tablespace altered.

sys@ORCL>select file_id,file_name,tablespace_name from dba_data_files

  2  where tablespace_name='USERS';

FILE_ID  FILE_NAME                          TABLESPACE_NAME

-------    --------------------------------------------        -------------------

4        /u01/app/oracle/oradata/orcl/users01.dbf     USERS

9        /u01/app/oracle/oradata/orcl/users02.dbf     USERS

 

sys@ORCL>drop table test;

Table dropped.

sys@ORCL>create table test tablespace users

  2  as

  3  select * from dba_objects;

Table created.

sys@ORCL>select SEGMENT_NAME,FILE_ID,BLOCKS from dba_extents

  2  where file_id=9;

SEGMENT_NAME                      FILE_ID     BLOCKS

------------------------------                  ----------      ----------

TEST                                    9          8

TEST                                    9          8

TEST                                    9          8

TEST                                    9          8

TEST                                    9          8

TEST                                    9          8

TEST                                    9          8

TEST                                    9          8

TEST                                    9          8

TEST                                    9          8

TEST                                    9          8

TEST                                    9          8

TEST                                    9          8

TEST                                    9          8

TEST                                    9          8

TEST                                    9        128

TEST                                    9        128

 

17 rows selected.

sys@ORCL>alter table test move tablespace PERFSTAT; --把表移动到其它表空间

Table altered.

sys@ORCL>select SEGMENT_NAME,FILE_ID,BLOCKS from dba_extents

  2  where file_id=9;

no rows selected

sys@ORCL>alter tablespace users drop datafile

  2  '/u01/app/oracle/oradata/orcl/users02.dbf';

Tablespace altered.

sys@ORCL>select file_id,file_name,tablespace_name from dba_data_files

  2  where tablespace_name='USERS';

FILE_ID  FILE_NAME                       TABLESPACE_NAME

-------    --------------------------------------------      ---------------------

4        /u01/app/oracle/oradata/orcl/users01.dbf   USERS

 

三、oracle 10g可以删除临时表空间的文件

alter database tempfile '/home/oracle/temp01.dbf' drop including datafiles;

 

ALTER DATABASE 与 ALTER TABLESPACE OFFLINE的区别

一．DataFile脱机或联机的两种方法：

① ALTER DATABASE 语句修改单独的DataFile

② ALTER TABLESPACE 语句修改所有的DataFile

1、在ARCHIVRLOG模式下的更改DataFile状态

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' ONLINE;

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' OFFLINE;

或者用文件号来表示 :

ALTER DATABASE DATAFILE 5 ONLINE;

ALTER DATABASE DATAFILE 5 OFFLINE;

注：只有在ARCHIVELOG模式下才可使用ALTER DATABASE来更改DataFile

 

2、在NOARCHIVELOG模式下使DataFile脱机

由于在NOARCHIVELOG模式下，数据文件脱机后会造成数据的遗失，所以只能使用ALTER DATABASE语句下带有DATAFILE和OFFLINE DROP子句的选项将该DataFile直接取消，例如该DataFile只包含临时段数据，并没有备份时

 

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users3.dbf' OFFLINE DROP;

3、修改TableSpace中所有DataFile或TempFile的可用性

ALTER TABLESPACE ... DATAFILE {ONLINE|OFFLINE}

ALTER TABLESPACE ... TEMPFILE {ONLINE|OFFLINE}

 

注：修改某TableSpace中的所有数据文件，但是TableSpace本身的状态不改变。

总结：

① ALTER TABLESPACE可以在数据库装载状态时发布，无需打开

② 涉及到系统表空间、撤销表空间、默认临时表空间时，必须是未打开的数据库

③ ALTER DATABASE DATAFILE 语句中必须填入文件全名

 

二． 表空间 与 数据文件 脱机的区别

1. ALTER TABLESPACE ... OFFLINE

Does a checkpoint on the datafiles

Takes the datafiles offline

表空间Offline时，数据文件的SCN会被冻结，而且表空间的数据文件offline/online时又会发生文件检查点，使单个数据文件SCN和数据库其他问题不一致。

表空间online时，Oracle会取得当前SCN，解冻offline文件SCN，和当前SCN同步。

tablespace offline有几种选项可供选择normal, temporary,immediate, for recovery,而在datafile中则没有这些选项。

 

2.  ALTER DATABASE DATAFILE ... OFFLINE

单纯的offline datafile，将不会触发文件检查点，只有针对offline tablespace的时候才会触发文件检查点，这也是为什么online datafile需要media recovery而online tablespace不需要。

注：只有在ARCHIVELOG模式下才可使用ALTER DATABASE来更改DataFile