Home » RDBMS Server » Server Administration » Moving data files. (Oracle 10g)
Moving data files. [message #323290] Wed, 28 May 2008 01:41 Go to next message
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member

I would like to move the data files from one mount point to other for the space management.

I want to move data files on the producation 24*7 environment.
Below i have mentioned 2 ways to move the datafile.
My questions are.

1. The techniques which i have specified, are those right?
2. If yes then which technique will be suitable for my production database.
3. Will performance impact if any?
4. Any suggestions.

Moving Datafiles while the Instance is Mounted
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> !mv /u04/TESTDB/oracle/oradata/ora01/TESTDB_B_IND/TESTDB_B_IND_01.dbf /u06/TESTDB/oracle/oradata/ora01/TESTDB_B_IND/TESTDB_B_IND_01.dbf
SQL> startup mount
ORACLE instance started.
Total System Global Area 1090519040 bytes
Fixed Size                  1218920 bytes
Variable Size             134219416 bytes
Database Buffers          939524096 bytes
Redo Buffers               15556608 bytes
Database mounted.

SQL> alter database rename file '/u04/TESTDB/oracle/oradata/ora01/TESTDB_B_IND/TESTDB_B_IND_01.dbf' to '/u06/TESTDB/oracle/oradata/ora01/TESTDB_B_IND/TESTDB_B_IND_01.dbf';
Database altered.
SQL> alter database open;
Database altered.

Moving Datafiles while the Instance is Open

SQL> alter tablespace TESTDB_B_IND offline;
Tablespace altered.
SQL> !mv /u06/TESTDB/oracle/oradata/ora01/TESTDB_B_IND/TESTDB_B_IND_01.dbf /u04/TESTDB/oracle/oradata/ora01/TESTDB_B_IND/TESTDB_B_IND_01.dbf
SQL> alter tablespace TESTDB_B_IND rename datafile
  2  '/u06/TESTDB/oracle/oradata/ora01/TESTDB_B_IND/TESTDB_B_IND_01.dbf' to '/u04/TESTDB/oracle/oradata/ora01/TESTDB_B_IND/TESTDB_B_IND_01.dbf';
Tablespace altered.
SQL>  alter tablespace TESTDB_B_IND online;
Tablespace altered.


[Updated on: Wed, 28 May 2008 01:42]

Report message to a moderator

Re: Moving data files. [message #323297 is a reply to message #323290] Wed, 28 May 2008 02:07 Go to previous messageGo to next message
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

You decide which you want. You want your database down or make it up and do operation.

I don't think any performance impact that can happen.
Re: Moving data files. [message #323586 is a reply to message #323297] Wed, 28 May 2008 22:55 Go to previous message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member


As it is your 24X7 production database, i think second technique would be more appropriate for you.

Previous Topic: Oracle Locale Builder : NLB Generation Error
Next Topic: extent size
Goto Forum:

Current Time: Wed Oct 26 18:32:32 CDT 2016

Total time taken to generate the page: 0.11241 seconds