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 helpfulgreeting 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... |
DO NOT ask technical questions here, that's what my forum is for!
These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!