最近一段时间，发现不少pub上不少新手都因为一时大意，添加数据文件名称不规范，然后想重命名该数据文件（或者想删除该数据文件然后重建），处理思路有些不妥，导致一些悲剧的发现，我这里通过实验提供一个自认为比较合理的处理思路：处理思路是数据文件离线重命名
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/oradata/test/archivelog
Oldest online log sequence 210
Next log sequence to archive 212
Current log sequence 212
--确认数据库是归档模式，使得数据库离线后，可以有归档日志恢复到在线状态
SQL> col name for a50
SQL> select name,file# from v$datafile;
NAME FILE#
-------------------------------------------------- ----------
/opt/oracle/oradata/test/system01.dbf 1
/opt/oracle/oradata/test/undotbs01.dbf 2
/opt/oracle/oradata/test/sysaux01.dbf 3
/opt/oracle/oradata/test/users01.dbf 4
/opt/oracle/oradata/test/user32g.dbf 5
/opt/oracle/oradata/test/xifenfei01.dbf 6
/opt/oracle/oradata/test/user02.dbf 7
/opt/oracle/oradata/test/odu02.dbf 8
/opt/oracle/oradata/test/odu01.dbf 9
/opt/oracle/oradata/test/odu03.dbf 10
10 rows selected.
SQL> alter tablespace xff add datafile '/opt/oracle/oradata/test/xifenfei02.chf'
2 size 10m autoextend off;
Tablespace altered.
SQL> select name,file# from v$datafile;
NAME FILE#
-------------------------------------------------- ----------
/opt/oracle/oradata/test/system01.dbf 1
/opt/oracle/oradata/test/undotbs01.dbf 2
/opt/oracle/oradata/test/sysaux01.dbf 3
/opt/oracle/oradata/test/users01.dbf 4
/opt/oracle/oradata/test/user32g.dbf 5
/opt/oracle/oradata/test/xifenfei01.dbf 6
/opt/oracle/oradata/test/user02.dbf 7
/opt/oracle/oradata/test/odu02.dbf 8
/opt/oracle/oradata/test/odu01.dbf 9
/opt/oracle/oradata/test/odu03.dbf 10
/opt/oracle/oradata/test/xifenfei02.chf 11
11 rows selected.
SQL> create table chf.xff_test tablespace xff
2 as
3 select * from dba_objects;
select * from dba_objects
*
ERROR at line 3:
ORA-01536: space quota exceeded for tablespace 'XFF'
SQL> alter user chf quota 100m on xff;
User altered.
SQL> create table chf.xff_test tablespace xff
2 as
3 select * from dba_objects;
Table created.
--需要重命名的数据文件内有数据，模拟数据库在生产环境中工作
SQL> alter database datafile 11 offline drop ;
Database altered.
--数据文件离线处理
SQL> !mv /opt/oracle/oradata/test/xifenfei02.chf /opt/oracle/oradata/test/xifenfei02.dbf
--系统级别把数据文件修改为正确名称
SQL> alter database rename file '/opt/oracle/oradata/test/xifenfei02.chf'
2 to '/opt/oracle/oradata/test/xifenfei02.dbf';
Database altered.
--修改控制文件中数据文件名称
SQL> recover datafile 11;
Media recovery complete.
SQL> alter database datafile 11 online;
Database altered.
--恢复数据文件，并使其online
SQL> select name,file# from v$datafile;
NAME FILE#
-------------------------------------------------- ----------
/opt/oracle/oradata/test/system01.dbf 1
/opt/oracle/oradata/test/undotbs01.dbf 2
/opt/oracle/oradata/test/sysaux01.dbf 3
/opt/oracle/oradata/test/users01.dbf 4
/opt/oracle/oradata/test/user32g.dbf 5
/opt/oracle/oradata/test/xifenfei01.dbf 6
/opt/oracle/oradata/test/user02.dbf 7
/opt/oracle/oradata/test/odu02.dbf 8
/opt/oracle/oradata/test/odu01.dbf 9
/opt/oracle/oradata/test/odu03.dbf 10
/opt/oracle/oradata/test/xifenfei02.dbf 11
11 rows selected.
如果数据库满足以下条件，可以删除数据文件，重新添加：
1、The database must be open.
2、If a datafile is not empty, it cannot be dropped. If you must remove a datafile that is not empty and that cannot be made empty by dropping schema objects, you must drop the tablespace that contains the datafile.
3、You cannot drop the first or only datafile in a tablespace. This means that DROP DATAFILE cannot be used with a bigfile tablespace.
4、You cannot drop datafiles in a read-only tablespace.
5、You cannot drop datafiles in the SYSTEM tablespace.
6、If a datafile in a locally managed tablespace is offline, it cannot be dropped.
7、db version >= 10g R2
SQL> alter tablespace xff drop datafile 11; alter tablespace xff drop datafile 11 * ERROR at line 1: ORA-03262: the file is non-empty SQL> alter tablespace xff add datafile '/opt/oracle/oradata/test/xifenfei03.chf' size 10m autoextend off; Tablespace altered. SQL> alter tablespace xff drop datafile '/opt/oracle/oradata/test/xifenfei03.chf'; Tablespace altered.
