Oracle Change data File location

Method one: Offline table space

1, offline table space: Alter tablespace tablespace_name offline;
2, copy the data file to the new directory;
3, rename Modify the table space, and modify the control file;
4, online table space;



1. Offline table Space Zerone
Sql> select name from V$datafile;
NAME
--------------------------------------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01. Dbf
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01. Dbf
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01. Dbf
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01. Dbf
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01. Dbf
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ZERONE01. Dbf
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\RMANTS. Dbf
7 rows have been selected.

sql> alter tablespace zerone offline;
Table space has changed.

2. Copying data files to a new directory
Copy the data file C:\\PRODUCT\10.2.0\ORADATA\ZERONE01. DBF to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ZERONE01. Dbf.

3. Rename Modify the Tablespace data file to a new location and modify the control file
sql> alter tablespace zerone rename datafile ' c:\oracle\product\10.2.0\oradata\zerone01.dbf ' to ' c:\oracle\product\ 10.2.0\ORADATA\ORCL\ZERONE01.DBF ';
Table space has changed.

4. Online table Space
sql> alter tablespace Zerone online;
Table space has changed.

Sql> select name from V$datafile;
NAME
--------------------------------------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01. Dbf
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01. Dbf
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01. Dbf
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01. Dbf
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01. Dbf
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ZERONE01. Dbf
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\RMANTS. Dbf
7 rows have been selected.

Sql> Select File_name,tablespace_name from dba_data_files where tablespace_name= ' zerone ';
file_name Tablespace_name
------------------------------------------------------------------------------------------------
Zerone C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ZERONE01. Dbf





Method Two: SQL modifies data file location

1, close;
2, copy the data file to the new location;
3, start the database to Mount State;
4. Modify data file location through SQL;
5, open the database;

1. Close the database
Sql> select name from V$datafile;
NAME
--------------------------------------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01. Dbf
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01. Dbf
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01. Dbf
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01. Dbf
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01. Dbf
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ZERONE01. Dbf
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\RMANTS. Dbf
7 rows have been selected.

sql> shutdown immediate;
The database is closed.
The database has been uninstalled.
The ORACLE routine has been closed.

2, copy the data file to the new location;
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ZERONE01 the data file. DBF is copied to C:\ORACLE\PRODUCT\10.2.0\ORADATA\ZERONE01. Dbf.

3, start the database to Mount State;
Sql> Startup Mount
The ORACLE routine has been started.
Total System Global area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 96470372 bytes
Database buffers 67108864 bytes
Redo buffers 2945024 bytes
The database is loaded.

4. Modify data file location through SQL;
sql> ALTER DATABASE rename file ' c:\oracle\product\10.2.0\oradata\orcl\zerone01.dbf ' to ' c:\oracle\product\10.2.0\ ORADATA\ZERONE01.DBF ';
The database has changed.

5, open the database;
sql> ALTER DATABASE open;
The database has changed.

Sql> select name from v$www1.qixoo.com;
NAME
--------------------------------------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01. Dbf
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01. Dbf
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01. Dbf
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01. Dbf
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01. Dbf
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ZERONE01. Dbf
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\RMANTS. Dbf
7 rows have been selected.

Sql> Select File_name,tablespace_name from dba_data_files where tablespace_name= ' zerone ';
file_name Tablespace_name
-------------------------------------------------------------------------------------------------
Zerone C:\ORACLE\PRODUCT\10.2.0\ORADATA\ZERONE01. Dbf

Oracle Change data File location