Useful shortcuts for vi editor

Thursday 23 April 2015

How to transfer database file in Windows?

To do that please follow the instructions;

Tablespace: ALPER_DATA

Database file: data_01.dbf
DB file path: C:\Databasefiles
New database file name: data_new_01.dbf
New DB file path: D:\Databasefiles

Type these commands and press enter;

Example of offline transfer (database runs as non-archivelog mode)

Tablespace goes offline;
  • sqlplus / as sysdba 
  • alter tablespace ALPER_DATA offline;
  • exit
  • move C:\Databasefiles\data_01.dbf D:\Databasefiles\data_new_01.dbf
Datafile location info updates in database;
  • sqlplus / as sysdba
  • alter tablespace ALPER_DATA rename datafile 'C:\Databasefiles\data_01.dbf' to 'D:\Databasefiles\data_new_01.dbf';
  • alter tablespace ALPER_DATA online;
  • exit
Example of online transfer (database runs as archivelog mode)

Datafile goes offline;
  • sqlplus / as sysdba
  • alter database datafile 'C:\tablespaces\alper_01.dbf' offline;
  • exit
  • move C:\Databasefiles\data_01.dbf D:\Databasefiles\data_new_01.dbf
Datafile location info updates in database;
  • sqlplus / as sysdba
  • alter tablespace ALPER_DATA rename datafile 'C:\Databasefiles\data_01.dbf' to 'D:\Databasefiles\data_new_01.dbf';
  • recover datafile 'D:\Databasefiles\data_new_01.dbf'
  • alter database datafile 'D:\Databasefiles\data_new_01.dbf' online;
  • exit
Bye :)