Transfer tablespace from one location to another

Asked By mash quizon 55 points N/A Posted on - 03/07/2012

Hi,

How can we transfer a tablespace from one location to another location. I need to transfer some in my environment.

Thank you.

• Status: Open
• Question Views: 1011
• Answer Count: 1
• Vote Up 0 Vote Down

• Answer Accepted: No
• Question Category: Oracle Database

Answered By Tunacao Caaron 0 points N/A #101213

Transfer tablespace from one location to another

 

In order to move the data file or rename it, follow this procedure:

 

1. You have to login to SQLPlus

2. With CONNECT/AS SYSDBA command, connect as SYS DBA

3. With SHUTDOWN command, shutdown the database instance 

4. The data file must be renamed or moved at the operating system level 

5. With STARTUP MOUNT command, you can start the Oracle database in mount state 

6. The name or location of the data file must be modified in Oracle data dictionary by issuing this command syntax:

ALTER DATABASE RENAME FILE '<fully qualified path to original data file name>'

TO '<new or original fully qualified path to new or original data file name>';

7. With ALTER DATABASE OPEN command, you can open Oracle Database instance.

 

There is another remedy that does not require a database instance to be shutdown when the data file that is changed or moved does not belong to the system table spaces or do not contain active or temporary rollback segments. Only a particular table space that holds the data file is taken offline.

 

1. Please login to SQLPlus.

2. With CONNECT / AS SYSDBA command, connect as SYS DBA.

3. The affected table space must be offline with ALTER TABLE SPACE <table space name> OFFLINE; command.

4. The name or location of data files must be modified in Oracle data dictionary using following command syntax:

ALTER TABLE SPACE <tablespace name> RENAME DATAFILE '<fully qualified path to original data file name>' TO'<new or original fully qualified path to new or original data file name>';

5. The table space must be brought online again with ALTER TABLE SPACE alter tablespace <tablespace name> ONLINE;command.

 

About Tunacao Caaron

Questions
0

Answers
42

Best Answers
19

• Vote Up 0 Vote Down

• Posted on - 03/07/2012
• Question Category: Oracle Database