In Oracle installation guides and scripts, you will often come across the term ORACLE_HOME. In fact, we used it on this site a couple of posts back, in the script to install SCOTT schema in Oracle.
This post will cover:
- the meaning and purpose of ORACLE_HOME
- how to check if ORACLE_HOME is set
- how to set ORACLE_HOME
- how to unset, change or delete ORACLE_HOME
What is ORACLE_HOME?
ORACLE_HOME is a directory in the file system where the Oracle software is installed. The path to this directory is stored in an environment variable.
The value of ORACLE_HOME will vary according to where you have installed Oracle. You could have installed in under C:\ or C:\Softwares or under D:\.
However, the file structure under ORACLE_HOME is fixed for any installation. For example, the location of sqlplus.exe is always at ORACLE_HOME\bin, and tnsnames.ora is always to be found at ORACLE_HOME\network\admin.
This is why installation guides and scripts use ORACLE_HOME when they mention files under the Oracle installation, as it provides a relative path to the actual location of the file independent of where Oracle is installed on your machine.
How to check if ORACLE_HOME is set
The Oracle installation might not set ORACLE_HOME automatically, in which case you have to set it yourself.
First – how to find out if ORACLE_HOME is set or not?
On Windows: On command prompt, type D:\>echo %ORACLE_HOME%. If this gives you the directory path, as in the code snippet below, then that means ORACLE_HOME is set.
D:\>echo %ORACLE_HOME% C:\oraclexe\app\oracle\product\10.2.0\server
If ORACLE_HOME is not set, the output will simply give back %ORACLE_HOME%, as below. This means you need to set the value, which we’ll see how to in the next section.
D:\>echo %ORACLE_HOME% %ORACLE_HOME%
On Unix/Linux: type
env | grep ORACLE_HOME
. As with Windows, the output will indicate if ORACLE_HOME is set already or needs to be set by you.
TIP: If you run a script that uses ORACLE_HOME but the ORACLE_HOME is not defined or incorrectly set, the output will give the error as below:
SQL> @%ORACLE_HOME%\RDBMS\ADMIN\SCOTT.sql SP2-0310: unable to open file "%ORACLE_HOME%\RDBMS\ADMIN\SCOTT.sql"
That’s possibly another indication that your ORACLE_HOME might need to be fixed.
How to set ORACLE_HOME
For the current runtime session, you can set ORACLE_HOME with a single command:
D:\>set ORACLE_HOME=C:\oraclexe\app\oracle\product\10.2.0\server D:\>echo %ORACLE_HOME% C:\oraclexe\app\oracle\product\10.2.0\server D:\>
This value will be wiped off when you close the current command line session. To set it as a global environment variable in Windows:
- Go to Control Panel -> System -> Advanced.
- Click on button “Environment Variables”. This will open a window with two sets of variables – User and System. User variables are visible to your login only, while system variables are visible to anyone else who uses the system.
- Choose “New” to create ORACLE_HOME variable as either User or System variable, depending on how you want its visibility.
- Set its value to the Oracle directory.
- Click OK to save.
- Verify through a new command line session that the value has been set correctly.
How to unset/change/delete ORACLE_HOME
You might want to edit or delete ORACLE_HOME when there are multiple Oracle installs machine or if Oracle installation has been moved from one location to another.
If you only want to override the value in one runtime session without affecting ORACLE_HOME permanently, just set it on command line as you would for a new variable.
To change it globally on Windows, follow the same steps as for setting a new variable. But click here the “Edit” or “Delete” button, in place of the “New”.