Home > Oracle > Oracle Database Running Out of Space ?

Oracle Database Running Out of Space ?

Are you running out of space in the file system where database files (data files) are installed?

Try out this tip in order to move some of your data files from one drive to another and update the datafile location in your database.

The operating system: Oracle Enterprise Linux
The Database: Oracle Database 10g Enterprise Edition Release

In our environment we have installed our database’s files (data files) in /oracle/oradata/mydb location. The drive is getting up to 99% of utilization. Now we will move the system01.dbf from the above mentioned location to a new location in /oracle/hdb1/oradata/mydb location. /oracle/hdb1 is mounted from /dev/hdb1. The drive is nearly empty, that’s why I chose it.
Now for the real moving part, we will perform the following steps:
Login to SQL* Plus and shutdown the database
Logout from SQL* Plus and move the files from the source directory to destination directory.
Login to SQL* Plus as /nolog
Connect as SYSDBA to the database to an idle instance.
Issue startup mount command to bring up the database.
Issue ALTER DATABASE RENAME command to rename the data files from the source directory to destination directory.
Now finally open the database.
The above mentioned are the brief steps by which we can achieve our goal. Now to demonstrate see the following commands as well so that it serves as a live example:

Step 1
oracle@astrn10: sqlplus /nolog
SQL> conn /as sysdba
SQL> shutdown immediate;
Troubleshooting: If you are not able to get the SQL prompt, check your ORACLE_HOME, and ORACLE_SID.
Step 2
SQL> exit;
oracle@astrn10: mv /oracle/oradata/mydb/system01.dbf /oracle/hdb1/oradata/mydb/
Now check whether the file have been moved or not by issuing the following command:
oracle@astrn10: ls /oracle/hdb1/oradata/mydb/
total 429924352
-rw-r----- 1 oracle oinstall 429924352 Feb 12 11:00 system01.dbf
Now we are ready for the next step.
Step 3
oracle@astrn10: sqlplus /nolog
Step 4
SQL> conn /as sysdba
Connected to idle instance.
Step 5
SQL> startup mount;
Database mounted.
Step 6
SQL> alter database rename file '/oracle/oradata/mydb/system01.dbf' to '/oracle/hdb1/oradata/mydb/system01.dbf';
Database altered.
Step 7
SQL> alter database open;
Database opened.

That’s all. We are done with our agenda for moving data files from one drive to another. If this where Windows/any other operating system, then copy files as per your operating system commands/interface in Step 2.
In order to copy more files (in this demonstration we have moved only one file), then repeat Step #2 and Step # 6 for each file.
#End of tip


Running out of space? Want to move Oracle Datafiles? – Ask Anantha


  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: