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. Dbfc:\oracle\product\10.2.0\oradata\orcl\undotbs01. dbfc:\oracle\product\10.2.0\oradata\orcl\sysaux01. Dbfc:\oracle\product\10.2.0\oradata\orcl\users01. Dbfc:\oracle\product\10.2.0\oradata\orcl\example01. Dbfc:\oracle\product\10.2.0\oradata\zerone01. Dbfc:\oracle\product\10.2.0\oradata\orcl\rmants. DBF has selected 7 rows. sql> alter tablespace zerone offline, table space changed.

2. Copying data files to a new directory
Copy the data file C:\Oracle\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; tablespace has changed. Sql> select name from V$datafile; NAME--------------------------------------------------------------------------------C:\ORACLE\PRODUCT\10.2.0\ Oradata\orcl\system01. Dbfc:\oracle\product\10.2.0\oradata\orcl\undotbs01. dbfc:\oracle\product\10.2.0\oradata\orcl\sysaux01. Dbfc:\oracle\product\10.2.0\oradata\orcl\users01. Dbfc:\oracle\product\10.2.0\oradata\orcl\example01. Dbfc:\oracle\product\10.2.0\oradata\orcl\zerone01. Dbfc:\oracle\product\10.2.0\oradata\orcl\rmants. DBF has selected 7 rows. 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 the database;
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. Dbfc:\oracle\product\10.2.0\oradata\orcl\undotbs01. dbfc:\oracle\product\10.2.0\oradata\orcl\sysaux01. Dbfc:\oracle\product\10.2.0\oradata\orcl\users01. Dbfc:\oracle\product\10.2.0\oradata\orcl\example01. Dbfc:\oracle\product\10.2.0\oradata\orcl\zerone01. Dbfc:\oracle\product\10.2.0\oradata\orcl\rmants. DBF has selected 7 rows. 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 Mountoracle Routine has been started. Total System Global area 167772160 bytesfixed size 1247900 bytesvariable size 96470372 bytesdatabase buffers 67108864 byte Sredo buffers 2945024 bytes 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; Sql> select name from V$datafile; NAME--------------------------------------------------------------------------------C:\ORACLE\PRODUCT\10.2.0\ Oradata\orcl\system01. Dbfc:\oracle\product\10.2.0\oradata\orcl\undotbs01. dbfc:\oracle\product\10.2.0\oradata\orcl\sysaux01. Dbfc:\oracle\product\10.2.0\oradata\orcl\users01. Dbfc:\oracle\product\10.2.0\oradata\orcl\example01. Dbfc:\oracle\product\10.2.0\oradata\zerone01. Dbfc:\oracle\product\10.2.0\oradata\orcl\rmants. DBF has selected 7 rows. 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 Turn: http://blog.csdn.net/magerguo/article/details/38059073

Oracle Change data File location