How to COnnect to Oracle database from UNIX Box

Shell Programming and Scripting
1

I am trying to connect to Oracle Databse from UNIX box using sqlplus command. But i get sqplus not found message.

$ sqlplus
sqlplus: not found

I have searched in forums and all are suggesting to set environment varaibles...can somebody tell me what variables to set and how to set.and the values that need to be assigned to those environment variables?

$ echo $ORACLE_HOME
/usr/oracle
$ echo $PATH
/usr/oracle/bin:/usr/bin:

This is what I get when I dispay the vlaues stored in these environment variables....

2

Just go to

cd $ORACLE_HOME/bin

there you can find the sqlplus u directly execute from there.

Or include the above path

($ORACLE_HOME/bin)

in the path variable.

3

$ cd $ORACLE_HOME/bin
/usr/oracle/bin: does not exist

This is what I get when I try to do as u suggested....

anyother way???

4

ok do the following

 
cd $ORACLE_HOME

i hope above path exists.

There you search for the "sqlplus" executable.

 
find . -name "sqlplus" -print
5

$ cd /usr/oracle
/usr/oracle: does not exist

Prev the .profile has only following contents
# This is the default standard profile provided to a user.
# They are expected to edit it to meet their own needs.
MAIL=/usr/mail/${LOGNAME:?}

After going through forums I have put following in it
# This is the default standard profile provided to a user.
# They are expected to edit it to meet their own needs.
PATH=/usr/oracle/bin:$PATH
export PATH
ORACLE_HOME=/usr/oracle
export ORACLE_HOME
MAIL=/usr/mail/${LOGNAME:?}

Is there anything i need to change here??

6

are you sure that ORACLE is installed in your system ?..

if so search for the sqlplus from root

find / -name "sqlplus" -print 2>/dev/null
7

/opt/PSscripts/clone/scripts/.0.6_BAK/bin/sqlplus
/opt/PSscripts/clone/scripts/.0.6_BAK/sqlplus
/dba58/d001/app/oracle/product/816/bin/sqlplus
/dba58/d001/app/oracle/product/816/sqlplus
/dba58/d001/app/oracle/product/817/bin/sqlplus
/dba58/d001/app/oracle/product/817/sqlplus
/dba58/d001/app/oracle/product/10gAgent/agent10g/sqlplus
/dba58/d034/app/oracle/product/920/bin/sqlplus
/dba58/d034/app/oracle/product/920/sqlplus
/dba58/d036/infa6qa/powertst/Scripts/AEROE01/sqlplus
/dba58/d037/staging/sqlplus
/dba58/d045/oracle9i/9idump/9ipatch/Disk1/orahomebkp/920/inventory/filemap/sqlplus
/dba58/d045/oracle9i/9idump/9ipatch/Disk1/orahomebkp/920/bin/sqlplus
/dba58/d045/oracle9i/9idump/9ipatch/Disk1/orahomebkp/920/sqlplus

I still see some more results coming as a result executing the find / -name "sqlplus" -print 2>/dev/null

8

Good..

we are not sure which one is the DB we are looking for

so u can manually try any of the following.

 
/dba58/d001/app/oracle/product/817/bin/sqlplus

or

 
/dba58/d001/app/oracle/product/816/bin/sqlplus
9

you can try the below script where it will connect to an oracle database and execute the query
sqlplus username/password@DB <<EOF
@/dir/home/sqlfilename.sql;
exit;
EOF

10

I know the database instance which I am trying to connect to.
Is there a way available which I can use to exactly findout the location of sqlplus which i shld use to connect

11

$ /dba58/d037/staging/sqlplus
/dba58/d037/staging/sqlplus: cannot execute
$ /dba58/d034/app/oracle/product/920/bin/sqlplus
Error 6 initializing SQL*Plus
Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

12

Usualy oracle_home/bin but in ur case it does not exist.

As of my knowledge u can't find based on instance name which "sqlplus" to use.

try manually all sqlplus exe's which are under bin directory.

13

You should really ask the database administrator.

Otherwise you can try to guess it in the following way:

You found:

  • /.../oracle/product/817/bin/sqlplus
  • /.../oracle/product/920/bin/sqlplus
  • /.../oracle/product/10gAgent/agent10g/sqlplus

this is an indication that three Oracle clients (and perhaps servers) are installed:

  • version 8.17
  • version 9.20
  • version 10

You should use a client with a version greater or equal to the version of the server instance you are going to connect. Even better: match the client version to the server version.
As a start, try to connect with sqlplus 9.20 then learn the server version (you should see a greeting banner).

What do you need:
export ORACLE_HOME=/dba58/d034/app/oracle/product/920
export PATH=PATH:$ORACLE_HOME/bin

Check if the file $ORACLE_HOME/network/admin/tnsnames.ora exists.

Then:
sqlplus user/password@dbname