Setting Environment Variables for Oracle Database in Windows/Linux

Setting up Environment Variables is important in order to access Oracle Database. It helps you prepare your environment so you can access the database with least efforts and start working as soon as possible. Usually with installations on your personal computers, you'll barely face this issue. But when working in a company, you might came across a problem as shown below,

[oracle@as ~]$ sqlplus
Error 6 initializing SQL*Plus
SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

Now the last line specifies the required action to do but if you are new to this, it can make you sweat a bit.

So let's understand the most important environment variables for any Oracle Database environment and their purpose.

ORACLE_HOME: The location of the Oracle home directory
ORACLE_SID: The Oracle SID
PATH: The path environment variable, which should include the ORACLE_HOME/bin directory
TNS_ADMIN: The location of the tnsnames.ora file, which is usually in the <ORACLE_HOME>/network/admin directory

How to set these environment variables?

1) Setting environment variables in runtime i.e. when you are already on terminal or command line.

On Windows:

set ORACLE_HOME = C:\app\oracle\product\19.3.0\dbhome_1
set ORACLE_SID = cdb1
set PATH = %PATH%;C:\app\oracle\product\19.3.0\dbhome_1\bin
set TNS_ADMIN = C:\app\oracle\product\19.3.0\dbhome_1\network\admin

On Linux:

export ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1
export ORACLE_SID = cdb1
export PATH = $PATH;/u01/app/oracle/product/19.3.0/dbhome_1/bin
export TNS_ADMIN = /u01/app/oracle/product/19.3.0/dbhome_1/network/admin

Note: These setting will only remain till you are not closing your current terminal.

2) Configure Environment Variables via System Settings.

On Windows, Open System > Advanced System Settings > Environment Variables

Add the above parameters in "System Variables", if you want to set these for all connecting users to the server OR add in "User Variables", if you want to set the environment for just you.

On Linux,

cat /home/oracle/.bash_profile

export ORACLE_HOME = /u01/app/oracle/product/19.3.0/dbhome_1
export ORACLE_SID = cdb1
export PATH = $PATH;/u01/app/oracle/product/19.3.0/dbhome_1/bin
export TNS_ADMIN = /u01/app/oracle/product/19.3.0/dbhome_1/network/admin

Ensure you have entries like above in your BASH profile

OR

Configure your database in ORADATA file.

cat /etc/oratab

CDB1:/u01/app/oracle/product/19.3.0/dbhome_1:N

[oracle@practice ~]$ . oraenv

ORACLE_SID = [oracle] ? cdb1

The Oracle base has been set to /u01/app/oracle

Post this you can connect to your database easily.

I hope this helps !!