1

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

Improve this question
1
  • What is the output of --> echo $ORACLE_HOME ? What command did you use to export the variable? Remembering it cannot containt trailing slashes on path specification or " " around it.
    – GuilhermeMesquitaX
    Aug 4, 2015 at 18:48

2 Answers 2

Reset to default
3

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 :-)

Improve this answer
7
  • not sqlplus needs to know where to find the sqlplus executable but the shell has to know it.
    – miracle173
    Aug 4, 2015 at 20:03
  • You are correct - fast typing brings its own problems - rephrasing.
    – Vérace
    Aug 4, 2015 at 20:10
  • @Vérace - i do not really understand. the current shell already know where is the sqlplus executable and is able to locate it, else how would it show the error ? is the sqlplus running under the current shell or a new child shell ?
    – Noob
    Aug 5, 2015 at 3:45
  • You type "sqlplus" at the prompt and hit return. At that moment a copy of your bash shell is spawned and it tries to run sqlplus. If it can't find sqlplus (which it can't because you haven't exported $ORACLE_HOME) it returns an error to the parent - which is what you see ("Error 6 initializing SQL*Plus" &c.). To answer: sqlplus is running under the new child shell. Google this and you'll get more detailled explanations. But, basically, to run any command from a shell, a child is created (spawned) and the command runs in this child. That's how things can run in the background.
    – Vérace
    Aug 5, 2015 at 3:52
  • @Vérace i do believe when i type sqlplus, the current bash will try to look for the program immediately in the $PATH, else i would get bash: sqlplus: command not found instead.
    – Noob
    Aug 5, 2015 at 16:22
0

You can set the default SID and your Oracle home in your .bash_profile file.

ORACLE_SID=ORCL; export ORACLE_SID
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME

Save the change, then you can source the file without logging out.

source .bash_profile

If you have more than one database on the server, you may not want to put the SID in your .bash_profile. You could use ORAENV after logging in as the Oracle user.

source oraenv
ORACLE_SID = [ORCL] ?

In fact, using ORAENV is the preferred method, as this will set your ORACLE_HOME environment variable.

The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.