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.
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.
Â
