Oracle HowTo: how to change the location of Oracle data files in read-only mode

Oracle HowTo: how to change the location of Oracle data files in Oracle Database in read-only mode. There are multiple ways to move the location of data files. I have introduced several methods before: Oracle HowTo: in non-archive mode, how does one change the data file location? Oracle HowTo: How does one move the data file location? The preceding two methods may not be suitable for 24x7 systems with high requirements, because they will make the tablespace unavailable for a long time. In particular, physical copying may take a long time when the tablespace data file is huge. Another method is recommended in this article to make a compromise. The following is a simple step description:

1. putting the tablespace In the read-only status can make the data still accessible to users. alter tablespace tablespace_name read only; 2. physical copy file 3. offlinealter tablespace tablespace_name offline; 4. rename data file alter database rename file 'old _ dir_file 'to 'new _ dir_file'; 5. online alter tablespace tablespace_name online; 6. place the tablespace in read write mode alter tablespace tablespace_name read write; The following is an example step: 1. place the tablespace In the read-only status [oracle @ jumper oracle] $ sqlplus "/as sysdba" SQL * Plus: Release 9. 2.0.4.0-Production on Sat Nov 12 21:10:49 2005 Copyright (c) 1982,200 2, Oracle Corporation. all rights reserved. connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0-ProductionWith the Partitioning optionJServer Release 9.2.0.4.0-ProductionSQL> archive log list; database log mode Archive ModeAutomatic archival EnabledArchive destination/opt/oracle/oradata/conner/archiveOldest online lo G sequence 7 Next log sequence to archive 10 Current log sequence 10SQL> select name from v $ datafile; NAME users/opt/oracle/oradata/conner/system01.dbf/opt/oracle/oradata/conner/undotbs01.dbf/opt/oracle/oradata/conner/users01.dbfSQL> alter tablespace users read only; tablespace altered.2. physical copy file SQL>! Cp/opt/oracle/oradata/conner/users01.dbf/opt/oracle/oradata/users01.dbf3. offline SQL> alter tablespace users offline; tablespace altered.4. Modify the file name SQL> alter database rename file '/opt/oracle/oradata/conner/users01.dbf' to '/opt/oracle/oradata/users01.dbf '; database altered.5. online SQL> alter tablespace users online; Tablespace altered.6. SQL> alter tablespace users read write; Tablespace altered. SQL> select name from v $ datafile; NAME alias/opt/oracle/oradata/conner/system01.dbf/opt/oracle/oradata/conner/undotbs01.dbf/opt/oracle/oradata/users01.dbfSQL>