I had a test server with 60Gb Hard disk drive. Database was updated thoroughly and space run out quickly. What i did was to install a second hard disk drive and create there another folder to store my moved datafile.
So in order to move c:\mydb\users.dbf to e:\mydb\users.dbf here are the steps to do it safely :
- open sql worksheet and connect to the database as sysdba with the sys account,
- make sure that all users are out of the database and that no pending transactions are taking place, then run
shutdown immediate;
- execute
start mount;
- copy-paste the datafile you wish to move, to the new location,
- execute
ALTER DATABASE RENAME FILE 'C:\MYDB\USERS.DBF' TO 'E:\MYDB\USERS.DBF';
- then execute
ALTER DATABASE OPEN;
...and you're done.
Easy, isn't it? :)Labels: alter, database, datafile, move, oracle, rename |
Author's note : Thank you all for your supporting letters. You have been a motivation to this web spot's posting. I wish you all the
very best for you and your families for the new year.
You don't have to take database offline to change the datafile name. Just take the specific tablespace offline, rename the file (os and dict) then online the tablespace! Done!!!