Use the MOVE DATAFILE clause to move an online data file to a new location. The database can be open and accessing the data file when you perform this operation. The database creates a copy of the data file when it is performing this operation. Ensure that there is adequate disk space for the original data file and the copy before using this clause.
You can specify the original data file using the file_name, ASM_filename, or file_number. Refer to ASM_filename for information on ASM file names. If you identify the file by number, then file_number is an integer representing the number found in the FILE# column of the V$DATAFILE dynamic performance view or in the FILE_ID column of the DBA_DATA_FILES data dictionary view.
Use the TO clause to specify the new file_name or ASM_filename. If you are using Oracle Managed Files, then you can omit the TO clause. In this case, Oracle Database creates a unique name for the data file and saves it in the directory specified by the DB_CREATE_FILE_DEST initialization parameter.
If you specify REUSE, then the new data file is created even if it already exists.
If you specify KEEP, then the original data file will be kept after the MOVE DATAFILE operation. You cannot specify KEEP if the original data file is an Oracle Managed File. You can specify KEEP if the new data file is an Oracle Managed File.
Syntax:
MOVE DATAFILE ( 'filename' | 'ASM_filename' | file_number )
[ TO ( 'filename' | 'ASM_filename' ) ]
[ REUSE ] [ KEEP ]
Examples:
SQL> select f.con_id,p.pdb_name,f.file_id,f.file_name
2 from cdb_data_files f left join cdb_pdbs p
3 on f.con_id=p.pdb_id
4 order by f.con_id,f.file_id;
CON_ID PDB_NAME FILE_ID FILE_NAME
------ ---------- ------- -----------------------------------------------------------------
1 1 /u01/app/oracle/oradata/cdborcl/system01.dbf
1 3 /u01/app/oracle/oradata/cdborcl/sysaux01.dbf
1 4 /u01/app/oracle/oradata/cdborcl/undotbs01.dbf
1 6 /u01/app/oracle/oradata/cdborcl/users01.dbf
2 PDB$SEED 5 /u01/app/oracle/oradata/cdborcl/pdbseed/system01.dbf
2 PDB$SEED 7 /u01/app/oracle/oradata/cdborcl/pdbseed/sysaux01.dbf
3 DEMO 8 /u01/app/oracle/oradata/cdborcl/demo/system01.dbf
3 DEMO 9 /u01/app/oracle/oradata/cdborcl/demo/sysaux01.dbf
3 DEMO 10 /u01/app/oracle/oradata/cdborcl/demo/SAMPLE_SCHEMA_users01.dbf
3 DEMO 11 /u01/app/oracle/oradata/cdborcl/demo/example01.dbf
4 PDB1 12 /u01/app/oracle/oradata/cdborcl/pdb1/system01.dbf
4 PDB1 13 /u01/app/oracle/oradata/cdborcl/pdb1/sysaux01.dbf
12 rows selected.
SQL> show con_id
CON_ID
------------------------------
1
SQL> alter database move datafile 6 to '/u01/app/oracle/oradata/cdborcl/users01_new.dbf';
Database altered.
SQL> ! ls -l /u01/app/oracle/oradata/cdborcl/users01.dbf
ls: cannot access /u01/app/oracle/oradata/cdborcl/users01.dbf: No such file or directory
SQL> ! ls -l /u01/app/oracle/oradata/cdborcl/users01_new.dbf
-rw-r-----. 1 oracle oinstall 5251072 Dec 9 21:06 /u01/app/oracle/oradata/cdborcl/users01_new.dbf
SQL> alter database move datafile 10 to '/u01/app/oracle/oradata/cdborcl/demo/SAMPLE_SCHEMA_users01_new.dbf';
alter database move datafile 10 to '/u01/app/oracle/oradata/cdborcl/demo/SAMPLE_SCHEMA_users01_new.dbf'
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "10"
SQL> alter session set container=demo;
Session altered.
SQL> alter database move datafile 10 to '/u01/app/oracle/oradata/cdborcl/demo/SAMPLE_SCHEMA_users01_new.dbf';
Database altered.
SQL> ! ls -l /u01/app/oracle/oradata/cdborcl/demo/SAMPLE_SCHEMA_users01_new.dbf
-rw-r-----. 1 oracle oinstall 5251072 Dec 9 21:07 /u01/app/oracle/oradata/cdborcl/demo/SAMPLE_SCHEMA_users01_new.dbf
SQL> alter session set container=cdb$root;
Session altered.
SQL> select f.con_id,p.pdb_name,f.file_id,f.file_name
2 from cdb_data_files f left join cdb_pdbs p
3 on f.con_id=p.pdb_id
4 order by f.con_id,f.file_id;
CON_ID PDB_NAME FILE_ID FILE_NAME
------ ---------- ------- ------------------------------------------------------------------
1 1 /u01/app/oracle/oradata/cdborcl/system01.dbf
1 3 /u01/app/oracle/oradata/cdborcl/sysaux01.dbf
1 4 /u01/app/oracle/oradata/cdborcl/undotbs01.dbf
1 6 /u01/app/oracle/oradata/cdborcl/users01_new.dbf
2 PDB$SEED 5 /u01/app/oracle/oradata/cdborcl/pdbseed/system01.dbf
2 PDB$SEED 7 /u01/app/oracle/oradata/cdborcl/pdbseed/sysaux01.dbf
3 DEMO 8 /u01/app/oracle/oradata/cdborcl/demo/system01.dbf
3 DEMO 9 /u01/app/oracle/oradata/cdborcl/demo/sysaux01.dbf
3 DEMO 10 /u01/app/oracle/oradata/cdborcl/demo/SAMPLE_SCHEMA_users01_new.dbf
3 DEMO 11 /u01/app/oracle/oradata/cdborcl/demo/example01.dbf
4 PDB1 12 /u01/app/oracle/oradata/cdborcl/pdb1/system01.dbf
4 PDB1 13 /u01/app/oracle/oradata/cdborcl/pdb1/sysaux01.dbf
12 rows selected.
Alert Log Output:
2013-12-09 21:06:38.668000 +08:00
alter database move datafile 6 to '/u01/app/oracle/oradata/cdborcl/users01_new.dbf'
Moving datafile /u01/app/oracle/oradata/cdborcl/users01.dbf (6) to /u01/app/oracle/oradata/cdborcl/users01_new.dbf
Move operation committed for file /u01/app/oracle/oradata/cdborcl/users01_new.dbf
Completed: alter database move datafile 6 to '/u01/app/oracle/oradata/cdborcl/users01_new.dbf'
2013-12-09 21:07:55.245000 +08:00
alter database move datafile 10 to '/u01/app/oracle/oradata/cdborcl/demo/SAMPLE_SCHEMA_users01_new.dbf'
Moving datafile /u01/app/oracle/oradata/cdborcl/demo/SAMPLE_SCHEMA_users01.dbf (10) to /u01/app/oracle/oradata/cdborcl/demo/SAMPLE_SCHEMA_users01_new.dbf
Move operation committed for file /u01/app/oracle/oradata/cdborcl/demo/SAMPLE_SCHEMA_users01_new.dbf
Completed: alter database move datafile 10 to '/u01/app/oracle/oradata/cdborcl/demo/SAMPLE_SCHEMA_users01_new.dbf'
No comments:
Post a Comment