To change the location of all the datafiles

I am imitating a database in one server to another server.My previous data files are in E Drive.Since new server doesn't have a E drive,I need to change the location of the database files in the control file.Please give me an advice..

re-create the control file with the correct location

Use ALTER DATABASE RENAME FILE 'old_name' TO 'new_name';

Start the instance to mount state and rename all files you moved
including datafiles, online log files and tempfiles.
The old names you get from controfile with these queries:

select name from v$datafile;
select member from v$logfile;
select name from v$tempfile;

You also need to adjust pfile/spfile if the controlfiles moved.

Quote:

---

Originally Posted by ales

Use ALTER DATABASE RENAME FILE 'old_name' TO 'new_name';

Start the instance to mount state and rename all files you moved
including datafiles, online log files and tempfiles.
The old names you get from controfile with these queries:

select name from v$datafile;
select member from v$logfile;
select name from v$tempfile;

You also need to adjust pfile/spfile if the controlfiles moved.

---

the problem with that is havign run a command many times or doing some code to loop round all the files

doing a global replace in a control file is much easier

Quote:

---

Originally Posted by davey23uk

the problem with that is havign run a command many times or doing some code to loop round all the files

doing a global replace in a control file is much easier

---

Yes, I admit I proposed an old-school approach :-)
With a bit of scripting it's not much work.

Something like this might work. I guess you run the query on the source database, and then startup nomount on the target database and apply the output. You can also recreate the control file by 'alter database backup control file to trace;' and then edit that file so that you can create new control files on the target system.

Code:

---

select 'ALTER DATABASE RENAME FILE ' || name
      || ' TO ' ||
      REPLACE(name, 'E:\', 'D:\') ||';'
  from ( select name
          from v$datafile
          union all
        select member name
          from v$logfile
          union all
        select name
          from v$tempfile )
 order by name;

---