Drop datafile in Oracle 10g[转]

Drop datafile in Oracle 10g

引用：http://douqqwct.blog.163.com/blog/static/179167912008112753022331/

In 9i database, the datafiles can't be dropped directly unless you will drop the whole tablespace. From 10g, you can use ‘drop datafile’ clause to drop a single datafile. When you drop a datafile, the datafile will be removed from data dictionary and control file. However, you have to comply with some restrictions.

1) The database must be open.
2) If the datafile is not empty, it can’t be dropped.
3) You can’t drop the first or only datafile one a tablespace.
4) You can’t drop the datafile in read-only tablespace, system tablesapce.
5) If the datafile in local management tablespace is offline, you can’t drop it.

     

The following are some tests and instances in my own database.

SQL> create tablespace sdu datafile '/export/home/oracle/base10202/oradata/ORA10202/sdu_01.dbf' size 100m 
           extent management local ;

Tablespace created.

SQL> alter tablespace sdu add datafile '/export/home/oracle/base10202/oradata/ORA10202/sdu_02.dbf' size 50m,     
                                                         '/export/home/oracle/base10202/oradata/ORA10202/sdu_03.dbf' size 50m,
                                                         '/export/home/oracle/base10202/oradata/ORA10202/sdu_04.dbf' size 50m;

Tablespace altered.

SQL> select file#,f.name from v$datafile f,v$tablespace t where t.ts#=f.ts# and t.name='SDU';

     FILE# NAME
---------- ------------------------------------------------------------
         4 /export/home/oracle/base10202/oradata/ORA10202/sdu_01.dbf
         6 /export/home/oracle/base10202/oradata/ORA10202/sdu_02.dbf
         7 /export/home/oracle/base10202/oradata/ORA10202/sdu_03.dbf
         9 /export/home/oracle/base10202/oradata/ORA10202/sdu_04.dbf

SQL> alter tablespace sdu drop datafile 7;

Tablespace altered.

SQL> !ls -alt /export/home/oracle/base10202/oradata/ORA10202/sdu_03.dbf
/export/home/oracle/base10202/oradata/ORA10202/sdu_03.dbf: No such file or directory

SQL> alter tablespace sdu drop datafile 4;
alter tablespace sdu drop datafile 4
*
ERROR at line 1:
ORA-03263: cannot drop the first file of tablespace SDU         --- we can’t drop the first datafile, which comply with rule 3.

SQL> create table t_sdu tablespace sdu as select * from dba_objects;

Table created.

SQL> select distinct file_id from dba_extents where segment_name='T_SDU';

   FILE_ID
----------
         6
         4
         9

SQL> alter tablespace sdu drop datafile 9 ;
alter tablespace sdu drop datafile 9
*
ERROR at line 1:
ORA-03262: the file is non-empty                                   --- we can’t drop unempty datafile, which comply with rule 2.

     We have to move this table to other tablespace in order to drop this datafile.

SQL> alter table t_sdu move tablespace users;

Table altered.

SQL> alter tablespace sdu drop datafile 9 ;

Tablespace altered.

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /export/home/oracle/base10202/product/10.2.0.2/dbs/arch
Oldest online log sequence     774
Current log sequence           775

SQL> alter database datafile 6 offline drop;                       --- we have to offline drop a datafile in noarchivelog mode.

Database altered.

SQL> alter tablespace sdu drop datafile 6;
alter tablespace sdu drop datafile 6
*
ERROR at line 1:
ORA-03264: cannot drop offline datafile of locally managed tablespace      --- we can’t drop offlined datafile in LMT tablespace, which comply with rule 5.

SQL> recover datafile 6;
Media recovery complete.
SQL> alter database datafile 6 online;

Database altered.

SQL> alter tablespace sdu drop datafile 6;

Tablespace altered.