Rename and/or move Tablespaces Datafiles

Hi everyone,

 

This article will describe how to rename and/or move Tablespaces Datafiles.

 

Put desired Tablespaces offline

SQL> alter tablespace tb1 offline normal;
SQL> alter tablespace tb2 offline normal;
SQL> alter tablespace tb3 offline normal;

 

OS side, move and rename Datafiles

# mv /previous_path/tb1.dbf /new_path/tb1_newname.dbf
# mv /previous_path/tb2.dbf /new_path/tb2_newname.dbf
# mv /previous_path/tb3.dbf /new_path/tb3_newname.dbf

 

Update file pointers in the Database control file

The following is done if you are acting on multiple Tablespaces

SQL> alter database rename file '/previous_path/tb1.dbf', '/previous_path/tb2.dbf', '/previous_path/tb3.dbf' to '/new_path/tb1_newname.dbf', '/new_path/tb2_newname.dbf', '/new_path/tb3_newname.dbf';

If only acting on one Tablespace, you can use this syntax

SQL> alter tablespace rename datafile '/previous_path/tb1.dbf', '/previous_path/tb2.dbf', '/previous_path/tb3.dbf' to '/new_path/tb1_newname.dbf', '/new_path/tb2_newname.dbf', '/new_path/tb3_newname.dbf';

 

Get Tablespaces back online

SQL> alter tablespace tb1 online;
SQL> alter tablespace tb2 online;
SQL> alter tablespace tb3 online;

 

Done.

Source : Oracle DBA Guide

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s