本文链接：《alter database datafile offline drop 与 alter tablespace drop datafile 区别》：http://blog.csdn.net/tianlesoftware/article/details/6305600
针对这篇文章需要说明的是，
1).必须在满足以下条件的情况下才能使用ALTER TABLESPACE DROP DATAFILE语句：
Specify DROP to drop from the tablespace an empty data file or temp file specified by filename or file_number. This clause causes the data file or temp file to be removed from the data dictionary and deleted from the operating system. The database must be open at the time this clause is specified.
The ALTER TABLESPACE ... DROP TEMPFILE statement is equivalent to specifying the ALTER DATABASE TEMPFILE ... DROP INCLUDING DATAFILES.
Restrictions on Dropping Files To drop a data file or temp file, the data file or temp file:
Must be empty.
Cannot be the first file that was created in the tablespace. In such cases, drop the tablespace instead.
Cannot be in a read-only tablespace that was migrated from dictionary managed to locally managed. Dropping a data file from all other read-only tablespaces is supported.
Cannot be offline.
See Also:
ALTER DATABASE alter_tempfile_clause for additional information on dropping temp files
Oracle Database Administrator's Guide for information on data file numbers and for guidelines on managing data files
下面是对这两类命令作用的演示：
[oracle1@redhat4 ~]$ sql
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 4 15:42:27 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select tablespace_name,file_name from dba_temp_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
TEMP
/u01/app/oracle1/oradata/orcl/temp01.dbf
TEMP1
/u01/app/oracle1/oradata/orcl/temp0101.dbf
TEMP2
/u01/app/oracle1/oradata/orcl/temp02.dbf
SQL> alter database tempfile '/u01/app/oracle1/oradata/orcl/temp0101.dbf' drop including datafiles;
Database altered.
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle1/oradata/orcl/system01.dbf
/u01/app/oracle1/oradata/orcl/undotbs01.dbf
/u01/app/oracle1/oradata/orcl/sysaux01.dbf
/u01/app/oracle1/oradata/orcl/user01.dbf
/u01/app/oracle1/oradata/orcl/example01.dbf
/u01/app/oracle1/oradata/orcl/data01.dbf
6 rows selected.
SQL> alter tablespace users add datafile '/u01/app/oracle1/oradata/orcl/user02.dbf' size 10m;
Tablespace altered.
SQL> alter tablespace users drop datafile '/u01/app/oracle1/oradata/orcl/user02.dbf';
Tablespace altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 2020480 bytes
Variable Size 109054848 bytes
Database Buffers 201326592 bytes
Redo Buffers 2170880 bytes
Database mounted.
SQL> alter database flashback off;
Database altered.
SQL> alter database noarchivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter tablespace users offline;
Tablespace altered.
SQL> alter tablespace users online;
Tablespace altered.
SQL> alter tablespace users offline immediate;
Tablespace altered.
SQL> recover tablespace users;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SQL> alter tablespace users online;
Tablespace altered.
SQL>
SQL>
SQL>
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle1/oradata/orcl/system01.dbf
/u01/app/oracle1/oradata/orcl/undotbs01.dbf
/u01/app/oracle1/oradata/orcl/sysaux01.dbf
/u01/app/oracle1/oradata/orcl/user01.dbf
/u01/app/oracle1/oradata/orcl/example01.dbf
/u01/app/oracle1/oradata/orcl/data01.dbf
6 rows selected.
SQL> alter database datafile '/u01/app/oracle1/oradata/orcl/user01.dbf' offline;
Database altered.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 7
Current log sequence 9
SQL> alter database datafile '/u01/app/oracle1/oradata/orcl/user01.dbf' online;
Database altered.
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS01 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS READ ONLY
TEMP1 ONLINE
TEMP2 ONLINE
EXAMPLE ONLINE
DATA01 ONLINE
9 rows selected.
SQL> alter tablespace users read write;
Tablespace altered.
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS01 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
TEMP1 ONLINE
TEMP2 ONLINE
EXAMPLE ONLINE
DATA01 ONLINE
9 rows selected.
SQL> alter tablespace users offline;
Tablespace altered.
SQL> alter tablespace users online;
Tablespace altered.
SQL> alter tablespace users offline immediate;
alter tablespace users offline immediate
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled
SQL> alter tablespace users offline immediate for drop;
alter tablespace users offline immediate for drop
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> alter database datafile '/u01/app/oracle1/oradata/orcl/user01.dbf' offline;
alter database datafile '/u01/app/oracle1/oradata/orcl/user01.dbf' offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled
SQL> alter database datafile '/u01/app/oracle1/oradata/orcl/user01.dbf' offline for drop;
Database altered.
SQL> alter database datafile '/u01/app/oracle1/oradata/orcl/user01.dbf' online;
alter database datafile '/u01/app/oracle1/oradata/orcl/user01.dbf' online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/u01/app/oracle1/oradata/orcl/user01.dbf'
SQL> recover datafile '/u01/app/oracle1/oradata/orcl/user01.dbf';
Media recovery complete.
SQL> alter database datafile '/u01/app/oracle1/oradata/orcl/user01.dbf' online;
Database altered.
QQ截图20130407000522.jpg