Method for location modification of Oracle database data files

How to change the location of an Oracle data file

A: In archive mode

1. In Sqlplus, connect to the Oracle database where you want to move the file, and then execute the following SQL statement to view the location of the Oracle database file:

Sql>select file_name from Sys.dba_data_files;

file_name
--------------------------------------------------------------
C:ora8oradataoracleusers01. Dbf
C:ora8oradataoracledr01. Dbf
C:ora8oradataoracletools01. Dbf
c:ora8oradataoracleindx01. Dbf
C:ora8oradataoraclerbs01. Dbf
C:ora8oradataoracletemp01. Dbf
C:ora8oradataoraclesystem01. Dbf

Query to 7 records. Record the location and name of the file you want to move.

2. Below we take the document "C:ora8oradataoracleusers01". DBF "moves to" d:oradatausers01. DBF "For example, to move other files, greet the same method (according to the actual situation to modify the file path and name) operation can be.

<1> first let all users stop connecting to the database and close the database. In the Command Line window, enter:

C:SVRMGRL (carriage return)
Svrmgr> Connect Internal (carriage return)
svrmgr> shutdown Immediate (carriage return)
#等待数据库提示关闭
Svrmgr> exit

<2> Locate the file "USERS01" from the "c:ora8oradataoracle" directory. DBF, and then copy to the directory "D:oradata" below. Repeat this step if you are moving multiple files.

<3> Enter the command window as follows:
C:SVRMGRL (carriage return)
Svrmgr> Connect Internal (carriage return)
Svrmgr> Startup Mount (carriage return)
#等待数据库提示装载成功
svrmgr> ALTER DATABASE Rename file ' C:ora8oradataoracleusers01. DBF ' to ' d:oradatausers01. DBF ';
#提示语句已处理后, if you want to move multiple files, modify the file path and name and repeat the above statement. Open the database when you are finished.
svrmgr> ALTER DATABASE open;
Svrmgr> exit

The database file was moved successfully.
B: The database is in non-archive mode, and you can change the file path by following these steps:
　　
1. Close the database
　　
2. File replication at the system level
　　
3. Start database to Mount state
　　
3. Modify data file location via SQL
　　
4. Open the Database
　　
Here is a demonstration of the steps in the actual operation:
　　
1. Database in non-archive mode
　　
sql> archive log list;
Database log mode No Archive mode
Automatic Archival Enabled
Archive destination/opt/oracle/oradata/conner/archive
Oldest online log sequence 150
Current log Sequence 153
　　
2. Need to move test.dbf files
　　
Sql> select name from V$datafile;
　　
NAME
------------------------------------------------------------
/opt/oracle/oradata/conner/system01.dbf
/opt/oracle/oradata/conner/undotbs01.dbf
/opt/oracle/oradata/conner/users01.dbf
/opt/oracle/test.dbf
　　
3. Close the database
　　
sql> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
　　
4. Copy files to a new location
　　
Sql>! Cp/opt/oracle/test.dbf/opt/oracle/oradata/conner/test.dbf
　　
5. Start database to Mount state
　　
sql> startup Mount;
ORACLE instance started.
　　
Total System Global area 101782828 bytes
Fixed Size 451884 bytes
Variable Size 37748736 bytes
Database buffers 62914560 bytes
Redo buffers 667648 bytes
Database mounted.
Sql> select name from V$datafile;
　　
NAME
-----------------------------------------------------------
/opt/oracle/oradata/conner/system01.dbf
/opt/oracle/oradata/conner/undotbs01.dbf
/opt/oracle/oradata/conner/users01.dbf
/opt/oracle/test.dbf
　　
6. Modify File Location
　　
sql> ALTER DATABASE rename file '/opt/oracle/test.dbf ' to '/opt/oracle/oradata/conner/test.dbf ';
　　
Database altered.
　　
sql> ALTER DATABASE open;
　　
Database altered.
　　
Sql> select name from V$datafile;
　　
NAME
----------------------------------------------------------------
/opt/oracle/oradata/conner/system01.dbf
/opt/oracle/oradata/conner/undotbs01.dbf
/opt/oracle/oradata/conner/users01.dbf
/opt/oracle/oradata/conner/test.dbf