oracle 11g及12c重命名数据文件方法

首先看11g的：
先查清楚要更改的数据文件属于哪个表空间
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    690      SYSTEM               ***     /u01/app/oracle/oradata/orcl/system01.dbf
2    580      SYSAUX               ***     /u01/app/oracle/oradata/orcl/sysaux01.dbf
3    90       UNDOTBS1             ***     /u01/app/oracle/oradata/orcl/undotbs01.dbf
4    27       USERS                ***     /u01/app/oracle/oradata/orcl/users01.dbf
5    50       TEST                 ***     /u01/app/oracle/oradata/orcl/test01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    29       TEMP                 32767       /u01/app/oracle/oradata/orcl/temp01.dbf

然后进入sql里让表空间离线：
SYS@orcl> alter tablespace test offline;
Tablespace altered.
然后更新物理文件的名字：
SYS@orcl> !mv /u01/app/oracle/oradata/orcl/test01.dbf /u01/app/oracle/oradata/orcl/test00.dbf
接着重命名数据文件：
SYS@orcl> alter database rename file '/u01/app/oracle/oradata/orcl/test01.dbf' to '/u01/app/oracle/oradata/orcl/test00.dbf';

Database altered.

最后再让表空间online
SYS@orcl> alter tablespace test online;
Tablespace altered.

然后查看
1    690      SYSTEM               ***     /u01/app/oracle/oradata/orcl/system01.dbf
2    580      SYSAUX               ***     /u01/app/oracle/oradata/orcl/sysaux01.dbf
3    90       UNDOTBS1             ***     /u01/app/oracle/oradata/orcl/undotbs01.dbf
4    27       USERS                ***     /u01/app/oracle/oradata/orcl/users01.dbf
5    50       TEST                 ***     /u01/app/oracle/oradata/orcl/test00.dbf

还可以只让单个的目标数据文件离线在线修改，不过那种必须开启归档模式


然后是12c的：
    12c的其中一个新特性就是能直接重名了数据文件
SQL> select * from v$version;


BANNER     CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production  0
PL/SQL Release 12.1.0.1.0 - Production  0
CORE 12.1.0.1.0 Production  0
TNS for Linux: Version 12.1.0.1.0 - Production  0
NLSRTL Version 12.1.0.1.0 - Production  0

SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/pdbseed/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/test01.dbf

SQL> alter database move datafile '/u01/app/oracle/oradata/orcl/test01.dbf' to '/u01/app/oracle/oradata/orcl/test00.dbf';  ---直接一条命令改
Database altered.

SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/pdbseed/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/test00.dbf

7 rows selected.

未开启归档
SQL> archive log list;
Database log mode       No Archive Mode
Automatic archival       Disabled
Archive destination       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     149
Current log sequence       151

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31098809/viewspace-2128802/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31098809/viewspace-2128802/