Oracle Tablespace Data File movement

Oracle Tablespace Data File movement

System: Windows

oracle:10g

To move a data file in a user table space from one path to the other

First, for offline non-system table space

This example moves Oracle's case table space (example tablespace) from

D:\ORADATA\ORCL\ move to D:\ORACLE\ORADATA\

1. View the data file information for the tablespace you want to change

Sql> Select Tablespace_name,file_name,online_status

From Dba_data_files

where tablespace_name= ' EXAMPLE ';

Tablespace_name file_name Online_

--------------- ----------------------------------- -------

EXAMPLE D:\ORADATA\ORCL\EXAMPLE01. DBF ONLINE

2. Set the target tablespace to an offline state

sql> alter tablespace EXAMPLE offline;

3. Check the status of the target tablespace again to make sure it is offline

Sql> Select Tablespace_name,file_name,online_status

From Dba_data_files

where tablespace_name= ' EXAMPLE ';

Tablespace_name file_name Online_

--------------- ----------------------------------- -------

EXAMPLE D:\ORACLE\ORADATA\EXAMPLE01. DBF OFFLINE

4. Move (or copy) the original data file to a new path

Sql> host Move D:\ORADATA\ORCL\EXAMPLE01. DBF D:\ORACLE\ORADATA\

5. Modify the data file path for this tablespace

Sql> Alter Tablespace EXAMPLE

Rename DataFile ' D:\ORADATA\ORCL\EXAMPLE01. DBF '

To ' D:\ORACLE\ORADATA\EXAMPLE01. DBF ';

6. View the table space modified information to ensure that the information is correct

Sql> Select Tablespace_name,file_name,online_status

From Dba_data_files

where tablespace_name= ' EXAMPLE ';

Tablespace_name file_name Online_

--------------- ----------------------------------- -------

EXAMPLE D:\ORACLE\ORADATA\EXAMPLE01. DBF OFFLINE

7. Modify the table space to online status

sql> alter tablespace EXAMPLE online;

8. View the table space final result

Sql> Select Tablespace_name,file_name,online_status

From Dba_data_files

where tablespace_name= ' EXAMPLE ';

Tablespace_name file_name Online_

--------------- ----------------------------------- -------

EXAMPLE D:\ORACLE\ORADATA\EXAMPLE01. DBF ONLINE

Note: This method is also applicable to the Sysaux and users table spaces.

Second, the system table space movement

This method requires the database to be in the Mount State

1. Close the running database

sql> shutdown Immediate

2. Start the database to Mount state

Sql> Startup Mount

3. Moving the data file of the system tablespace

Sql> host Move D:\ORADATA\ORCL\SYSTEM01. DBF D:\ORACLE\ORADATA\

4. Modify the data file path for this tablespace

sql> ALTER DATABASE Rename file ' D:\ORADATA\ORCL\SYSTEM01. DBF ' to ' D:\ORACLE\ORA

Data\system01. DBF ';

5. Start the database, open the instance

sql> ALTER DATABASE open;

6. View table Space Modification Results

Sql> Select Tablespace_name,file_name,online_status from Dba_data_files where TA

Blespace_name= ' SYSTEM ';

Tablespace_name file_name Online_

--------------- ----------------------------------- -------

SYSTEM D:\ORACLE\ORADATA\SYSTEM01. DBF SYSTEM

Note: This method is also applicable to UNDOTBS1 and temp table spaces.

Acknowledgement: I refer to a number of articles after summing up, in this thank you network share!

This article is from the "4699096" blog, please be sure to keep this source http://4709096.blog.51cto.com/4699096/1728449

Oracle Tablespace Data File movement