Linux – ORACLE_HOME / ORACLE_SID variable – why do we need to export them

centoslinuxoracleoracle-11g-r2scripting

I am not sure if this is a bash / shell question or oracle question.
I am on linux and oracle 11g.

I have always export my ORACLE_HOME and ORACLE_SID variable and e.g. using invoking sqlplus is no issue.

I thought have thought that sqlplus is able to be invoked because it is in my PATH variable with $ORACLE_HOME/bin added to $PATH.

=============================================

Today, not sure why, i decided to unset ORACLE_HOME and ORACLE_SID and just declare them as local variable to my current shell. My PATH is still the same. (contain the full path to oracle_home/bin)

ORACLE_HOME=/path/to/oracle_home
ORACLE_SID=orcl

and I invoke sqlplus from the shell.

==============================================

The sqlplus program is able to be located, but it is not able to run saying that i need to set my ORACLE_HOME

[oracle@SJOAM ~]$ 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
[oracle@SJOAM ~]$

===============================================

My question is

1) why is sqlplus unable to know the ORACLE_HOME and ORACLE_SID since it is run from the current shell ?

is it because it is invoked as a child process and thus cannot access the parent process (bash) local variable ?

Regards,
Noob

Best Answer

This is really a Unix/Linux question, but it does relate to Oracle, so I'll give an answer.

In Unix, the system makes great use of what it calls "spawning" (or "giving birth"). When you run a command, a new process is created which takes all of its information from the parent. Essentially, it's a copy of the parent - it's a new bash shell, which then sets about performing the task given to it by its parent.

When that child process completes, it returns a code to the parent (success or failure) - AFAICR, - 0 is success, failure is anything else.

So, the new bash process spawned by your shell (aka "the child") takes its variables from your original (i.e. the spawning or parent) bash shell. If you don't tell the new SQLPlus shell where to find the SQLPlus executable, then it will throw an error.

Unusually for Linux/Unix and Oracle, the error message is actually informative. This is not always the case! :-). If you want a good laugh about Unix, read this. In fairness to Linux/Unix, they've come a long way since that document was written, but it's still worth a read.

So, unless you "export" - i.e. feed the values of your environment to all of the child processes - your system will misbehave.

Take a look at Andrew Tanenbaum's book - or any good text on modern Unixen (or *nix). There's plenty of info out there - just Google Unix and processes - you'll get a truckload of stuff.

@StringerBell makes a good point. In one job I worked in, we had 4 test instances on the one machine. We'd log in and then go to our desired instance and run oraenv.blue, oraenv.red... There are pros and cons - it was easy enough to forget which instance one was on - it's maybe a good idea to change the prompt as well - to give DBAs a fighting chance :-)

Related Question