数据文件重命名

最近一段时间，发现不少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.