Shulou(Shulou.com)06/01 Report--

To rename or move a data file in previous versions of Oracle12.1, you need to close the database or set the tablespace / data file to the offline state. Refer to the previously summarized Oracle to modify the data file name / move the data file. However, with version 12.1, you can rename or move data files directly in the data file online state.

To achieve this function, you need to use an ALTER DATABASE MOVE DATAFILE statement, with the following syntax

ALTER DATABASE MOVE DATAFILE ('filename' |' ASM_filename' | file_number) [TO ('filename' |' ASM_filename')] [REUSE] [KEEP]

Test this feature in version 12.2 below

Zx@ORA12C > select * from v$version BANNER CON_ID -Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit Production 0PL/SQL Release 12.2.0.1.0-Production 0CORE 12.2.0.1.0 Production 0TNS for Linux: Version 12.2.0.1.0-Production 0NLSRTL Version 12.2.0.1.0-Production 0

1. Create test tablespaces and data files:

Sys@ORA12C > create tablespace t_move datafile'/ home/oracle/t_move.dbf' size 50m; Tablespace created.sys@ORA12C > col name for a50sys@ORA12C > select d.nameMagazine d.status from v$datafile dreel vested tablespace t / where t.ts#=d.ts# and t.namebooks NAME STATUS----/ home/oracle/t_move.dbf ONLINE

Now the data file'/ home/oracle/t_move.dbf' is in ONLINE state.

2. Perform rename operation

Sys@ORA12C > alter database move datafile'/ home/oracle/t_move.dbf' to'/ home/oracle/t_move01.dbf';Database altered.sys@ORA12C > select d.namephery d.status from v$datafile d where t.ts#=d.ts# and t where t.ts#=d.ts# and t. NAME STATUS----/ home/oracle/t_move01.dbf ONLINEsys@ORA12C >! ls-l / home/oracle/t_move01 .dbf-rw-r- 1 oracle oinstall 52436992 Jul 11 16:07 / home/oracle/t_move01.dbfsys@ORA12C >! ls-l / home/oracle/t_move.dbfls: cannot access / home/oracle/t_move.dbf: No such file or directory

You can see that the file name has been changed from t_move.dbf to t_move01.dbf, and the original file no longer exists.

3. Perform mobile directory operation

Sys@ORA12C > alter database move datafile'/ home/oracle/t_move01.dbf' to'/ u01According to an oracle.oradata.ora12c Database altered.sys@ORA12C >! ls-l / u01 racemet oracle.oradata; ora12c; home/oracle/t_move01.dbf; moving 01.dbfWhure; RWWhat r-1 oracle oinstall 52436992 Jul 11 16:10 / u01/app/oracle/oradata/ora12c/t_move01.dbfsys@ORA12C >! ls-l / home/oracle/t_move01.dbfls: cannot access / home/oracle/t_move01.dbf: No such file or directorysys@ORA12C > select d.namestamentd.status from v$datafile dlll vested tablespace t where t.ts#=d.ts# and t.namestame. NAME STATUS----/ u01/app/oracle/oradata/ora12c/t_move01.dbf ONLINE

From the above results, you can see that the data file has been moved from the'/ home/oracle' directory to the / u01max oradata'directory.

4. Copy data files to the target directory, keep the original files

Sys@ORA12C > alter database move datafile'/ u01 from v$datafile from v$datafile sys@ORA12C > oraclespace t where t.ts#=d.ts# and t where t.ts#=d.ts# and t after moving moving 01.dbf'to'/ home/oracle/t_move01.dbf' keep;Database altered.sys@ORA12C > d.namethed.status oradata dtitle NAME STATUS----/ home/oracle/t_move01.dbf ONLINEsys@ORA12C >! ls-l / home/oracle/t_move01 .dbf-rw-r- 1 oracle oinstall 52436992 Jul 11 16:15 / home/oracle/t_move01.dbfsys@ORA12C >! ls-l / u01 use oracle.oradata; ora12c; move01.dbfmuryr-1 oracle oinstall 52436992 Jul 11 16:15 / u01/app/oracle/oradata/ora12c/t_move01.dbf

From the above results, you can see that the data file has been changed to / home/oracle/t_move01.dbf, but the original data file is still retained.

5. Move data files to ASM storage

-- File system to ASMALTER DATABASE MOVE DATAFILE'/ u01 dgroup_01/data/orcl/datafile/user1.dbf';--ASM to ASMALTER DATABASE MOVE DATAFILE'+ dgroup_01/data/orcl/datafile/user1.dbf' TO'+ dgroup_02/data/orcl/datafile/user1.dbf'

Reference: http://docs.oracle.com/database/121/ADMIN/dfiles.htm#ADMIN13837

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.