Shulou(Shulou.com)06/01 Report--

Oracle tablespace data file movement

System: windows

Oracle:10g

Implement to move the data file in the user tablespace from a certain path to another path

1. Non-system tablespaces for offline

This example moves the case tablespace (EXAMPLE tablespace) of oracle, changing it from

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

1. View the data file information of the table space to be changed

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

two。 Set the target tablespace to offline

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 of the tablespace

SQL > alter tablespace EXAMPLE

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

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

6. View the modified information of the tablespace to make sure 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 tablespace to be online

SQL > alter tablespace EXAMPLE online

8. View the final result of the tablespace

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 and USERS tablespaces.

II. System tablespace movement

This method requires the database to be in mount state.

1. Shut down the running database

SQL > shutdown immediate

two。 Start the database to mount state

SQL > startup mount

3. Move data files for system tablespaces (SYSTEM tablespaces)

SQL > host move D:\ ORADATA\ ORCL\ SYSTEM01.DBF D:\ ORACLE\ ORADATA\

4. Modify the data file path of the tablespace

SQL > alter database rename file'D:\ ORADATA\ ORCL\ SYSTEM01.DBF' to'D:\ ORACLE\ ORA

DATA\ SYSTEM01.DBF'

5. Start the database and open the instance

SQL > alter database open

6. View tablespace 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 tablespaces.

Thank you: I have made a summary after referring to a number of articles, and I would like to thank all the network sharers!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.