A method to move Oracle table spatial data files _oracle

To move a data file from one path to another in a user's table space

One, for the offline of the non-system table space

This example moves Oracle's case table space (example table space) 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 table space to offline

sql> alter tablespace EXAMPLE offline;

3. View the status of the target table space again to ensure that it is already 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 table space

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 for online status

sql> alter tablespace EXAMPLE online;

8. View the final result of the table space

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 Sysaux, the users table space

Second, the system table space movement

This method requires the database to be in the Mount State

1. Shut down the running database

sql> shutdown Immediate

2. Start database to Mount state

Sql> Startup Mount

3. Data files for mobile system tablespaces (systems table spaces)

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

4. Modify the data file path for this table space

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.

The above content is small to share the Oracle table space data file Mobile method, I hope you like.