Forums | Aggregator | Books | Industry News | Firefox Plugins | Social | Links

Comments

Renaming or Moving Oracle Files - An explanation of how assorted Oracle files can be renamed or moved to a new location.



koko said...

A bit incomplete.
What if I have a DB with 200 datafiles and I want to rename half of them?
Issuing the command "ALTER..." 100 is not very practical

Tim... said...

I don't see why this is a problem. You have the v$ views, which can be queried to build a list of ALTER statements.

Apart from that the only alternative as far as I can see is to backup the controlfile to trace and recreate the controlfile, which still involves amending 100 file references. I don't see how this differs drastically from the approaches presented here.

I might add this method also when I get time.

Cheers

Tim...

Raghu said...

This was very helpful for me.

Gaby said...

Just to say hellow!

Michael said...

The instructions worked like a treat first time.
* * * * *

Ingo said...

It is very well explained how to do this.
Of course, with a bit of scripting (I just would like to mention PERL) you souldn''t have any problems to rename 100, 1000 or 100,000 datafiles.

Freddy said...

Very usefull. Thanks.

Just a remark, nothing about the temporary tablespace datafiles, when you want to move all datafiles on a another drive.

After completing these steps, i've created a permanent temporary "by default" tablespace with well-placed datafile(s) and after i've dropped the old one (tablespace).


ivan said...

This is a very helpful article.

martin said...

great help for me

murtuja said...

In System tablespace,There is only one .DBF file .I want relocate this file to another location.How to do this?

Tim... said...

Follow the instructions in the datafiles section. Looks pretty clear to me!

Cheers

Tim...

PS. Questions on the forum!

Didik Bhudi Gunawan said...

nice tutorial

Thirsty Rock said...

After unsuccessfully scouring the net for a solution to the infamous ORA-00205 "error in identifying ..." this article saved my day. Renaming data/log files as described was the only way out of the trouble I got into from changing hard disks and re-installing Oracle9i.

Great work!

Alex said...

No need to shutdown the DB...

- Get the tablespace containing the datafile offline
- mv the file, on the OS side
- alter... rename
- Get the tbs online.

Caroline said...

Clear and useful - many thanks

Daniel said...

excellent! thank you!

PaulSmyth said...

Just a comment about the redo log relocation.

Its best to create additional redo logs in the new location and then offline/drop the originals.

Ulan said...

It's great howto! I love it/

ionos77 said...

i used this commands to transfer the database on a different machine with different drive structure and worked fine!
The only thing that must done (and its not writed here) is with the temp datafile. You cannot as i understand to transfer-rename tempfile because its the only tablespace used as temp.So i create a new temp with different name as default temporary and then rename the old one to the different drive letter.then i switched again as default tablespace the old one.

MT said...

Thank you very much! it was very helpful
greeting from italy
MT

Anmatr said...

I'm using XE database on win.
Initially the move datafile part did not work, I got errors
ORA-12514: TNS: Listener kann in Connect-Deskriptor angegebenen Service aktuell nicht auflösen
and then
ORA-24324: Dienst-Handle nicht initialisiert
ORA-01041: Interner Fehler: hostdef-Erweiterung ist nicht vorhanden

But found the problem:
After the host move command a reconnect is important:

connect / as sysdba;

With that it worked perfect!

Thank you for this instructions.

BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Danny said...

Very nice!!!

Yechiel said...

Thank you.
I had to move several data files and googeled to refresh my memory. I found this and it was a great help.

Mayank said...

Amazing.. works like a charm...

Greetings from India...

Bob said...

A great "how to" guide. Worked first time!!!

KING said...

EXCELLENT

Anjy said...

A simple and clear set of instructions.. Many thanks.

MikeO said...

In a DataGuard setup, this method will work for Primary, but will fail on standby if STANDBY_FILE_MANAGEMENT=auto (ORA-01275). Simply change it to "manual", ALTER DATABASE RENAME FILE..., and then set it back to "auto".

Tim... said...

Hi.

Yes. When I wrote the article I was focusing very much on an individual installation. I've added a quick note to the bottom of the article about this.

Thanks for the feedback.

Cheers

Tim...

Frank said...

That was beautifully clear. Thanks.
Prepared Statement Error: Table './oraclebasecms/cms_page_comment_uuids' is marked as crashed and should be repaired