12C新特性： online data files move

参考手册：

Oracle Database Online Documentation 12c Release 1 (12.1) / Database Administration

14.5.1 Renaming and Relocating Online Data Files
You can use the ALTER DATABASE MOVE DATAFILE SQL statement to rename or relocate online data files. This statement enables you to rename or relocate a data file while the database is open and users are accessing the data file.

When you rename or relocate online data files, the pointers to the data files, as recorded in the database control file, are changed. The files are also physically renamed or relocated at the operating system level.

You might rename or relocate online data files because you want to allow users to access the data files when you perform one of the following tasks:

Move the data files from one type of storage to another

Move data files that are accessed infrequently to lower cost storage

Make a tablespace read-only and move its data files to write-once storage

Move a database into Oracle Automatic Storage Management (Oracle ASM)

移动本地文件到asm

SQL> alter database move datafile 16 to '+data/cdb/datafile/system02.dbf';

SQL>  select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------------------------------------
     1 +DATA/CDB/DATAFILE/system.290.985011811
     3 +DATA/CDB/DATAFILE/sysaux.289.985011873
     4 +DATA/CDB/DATAFILE/undotbs1.293.985011907
     5 +DATA/CDB/64A52F53A7693286E053CDA9E80AED76/DATAFILE/system.300.985012289
     6 +DATA/CDB/64A52F53A7693286E053CDA9E80AED76/DATAFILE/sysaux.299.985012289
     7 +DATA/CDB/DATAFILE/users.294.985011909
     8 +DATA/CDB/64A52F53A7693286E053CDA9E80AED76/DATAFILE/undotbs1.301.985012289
     9 +DATA/CDB/DATAFILE/undotbs2.303.985012575
    10 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/system.308.985012979
    11 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/sysaux.309.985012979
    12 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/undotbs1.307.985012979
    13 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/undo_2.311.985013015
    14 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/users.312.985013017
    15 +DATA/CDB/DATAFILE/user02.dbf
    16 /soft/system02.dbf

15 rows selected.

SQL> alter  database move datafile 16 to '+data/cdb/datafile/system02.dbf';

Database altered.

SQL>  select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------------------------------------
     1 +DATA/CDB/DATAFILE/system.290.985011811
     3 +DATA/CDB/DATAFILE/sysaux.289.985011873
     4 +DATA/CDB/DATAFILE/undotbs1.293.985011907
     5 +DATA/CDB/64A52F53A7693286E053CDA9E80AED76/DATAFILE/system.300.985012289
     6 +DATA/CDB/64A52F53A7693286E053CDA9E80AED76/DATAFILE/sysaux.299.985012289
     7 +DATA/CDB/DATAFILE/users.294.985011909
     8 +DATA/CDB/64A52F53A7693286E053CDA9E80AED76/DATAFILE/undotbs1.301.985012289
     9 +DATA/CDB/DATAFILE/undotbs2.303.985012575
    10 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/system.308.985012979
    11 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/sysaux.309.985012979
    12 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/undotbs1.307.985012979
    13 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/undo_2.311.985013015
    14 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/users.312.985013017
    15 +DATA/CDB/DATAFILE/user02.dbf
    16 +DATA/cdb/datafile/system02.dbf

15 rows selected.

移动部分文件到新目录

move 完成之后，源目录文件自动清除。

SQL> alter database move datafile 1 to '/soft/system01.dbf';
SQL> alter database move datafile 3 to '/soft/sysaux01.dbf';

[oracle@host01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 19 22:36:24 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options

SQL> col name format a50;
set linesize 800;
set pagesize 900; 
select file#,name from v$datafile;SQL> SQL> SQL> 

     FILE# NAME
---------- --------------------------------------------------
     1 /u01/app/oracle/oradata/PRODCDB/system01.dbf
     2 /u01/app/oracle/oradata/PRODCDB/pdbseed/system01.dbf
     3 /u01/app/oracle/oradata/PRODCDB/sysaux01.dbf
     4 /u01/app/oracle/oradata/PRODCDB/pdbseed/sysaux01. bf
     5 /u01/app/oracle/oradata/PRODCDB/undotbs01.dbf
     6 /u01/app/oracle/oradata/PRODCDB/users01.dbf
     7 /u01/app/oracle/oradata/PRODCDB/PDBPROD1/system01.dbf
     8 /u01/app/oracle/oradata/PRODCDB/PDBPROD1/sysaux01.dbf
     9 /u01/app/oracle/oradata/PRODCDB/PDBPROD1/PDBPROD1_users01.dbf
    10 /u01/app/oracle/oradata/PRODCDB/PDBPROD2/system01. dbf
    11 /u01/app/oracle/oradata/PRODCDB/PDBPROD2/sysaux01.dbf
    12 /u01/app/oracle/oradata/PRODCDB/PDBPROD2/PDBPROD2_users01.dbf
    13 /u01/app/oracle/oradata/PRODCDB/PDBPROD1/example01.dbf
       
13 rows selected.

SQL> alter database move datafile 1 to '/soft/system01.dbf';
                                           
Database altered.

SQL> alter database move datafile 3 to '/soft/sysaux01.dbf';

Database altered.

select file#,name from v$datafile;SQL> SQL> SQL> 

     FILE# NAME
---------- --------------------------------------------------------------------------------
     1 /soft/system01.dbf
     2 /u01/app/oracle/oradata/PRODCDB/pdbseed/system01.dbf
     3 /soft/sysaux01.dbf
     4 /u01/app/oracle/oradata/PRODCDB/pdbseed/sysaux01.dbf
     5 /u01/app/oracle/oradata/PRODCDB/undotbs01.dbf
     6 /u01/app/oracle/oradata/PRODCDB/users01.dbf
     7 /u01/app/oracle/oradata/PRODCDB/PDBPROD1/system01.dbf
     8 /u01/app/oracle/oradata/PRODCDB/PDBPROD1/sysaux01.dbf
     9 /u01/app/oracle/oradata/PRODCDB/PDBPROD1/PDBPROD1_users01.dbf
    10 /u01/app/oracle/oradata/PRODCDB/PDBPROD2/system01.dbf
    11 /u01/app/oracle/oradata/PRODCDB/PDBPROD2/sysaux01.dbf
    12 /u01/app/oracle/oradata/PRODCDB/PDBPROD2/PDBPROD2_users01.dbf
    13 /u01/app/oracle/oradata/PRODCDB/PDBPROD1/example01.dbf

13 rows selected.