how to connect to an oracle database from unix

I am trying to connect to an oracle database from my unix machine. I am new to script writing in general. I know how to browse round unix and have written basic scripts ( read / display ) and execute them with bash command. Also I know how to view the variables in unix ( user and system). Could you tell me what i need to do to connect to an oracle database? do I use the sqlplus command? are there any variables I have to set before that?

回答1:

Could you tell me what i need to do to connect to an oracle database? do I use the sqlplus command?

Well, yes of course, you need to use SQL*Plus. However, before that, you need to make sure of few things:

export ORACLE_HOME variable

For example,

export ORACLE_HOME=/u01/app/oracle/product/11.2.0

export PATH variable

For example,

export PATH=$PATH:$ORACLE_HOME/bin

export SID

For example,

export ORACLE_SID="your database service name"

Make sure you have the tnsnames.ora configured properly
Make sure you have the listener up and running and is listening to the correct port.

You should be able to connect to the database as:

sqlplus username/password@sid

回答2:

Set ORACLE_HOME & ORACLE_SID environment variable. Then use sqlplus username@ORACLE_SID

回答3:

Make sure you've exported all the necessary Oracle variable in your unix user path environment as below:

ORACLE_BASE=/home/oracle/app; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/oracle/product/11.2.0/dbhome_1/; export ORACLE_HOME
ORACLE_SID=orcl; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$HOME/bin:$PATH; export PATH

And Make sure tnsnames.ora file is configured properly and listener should be up and running as below.

[oracle@OLE1 admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ole1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

[oracle@OLE1 ~]$ tnsping orcl
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 12-JUL-2017 23:12:35
Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:
/home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ole1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (20 msec)
[oracle@OLE1 ~]$ 

[oracle@OLE1 ~]$ cat /etc/hosts
127.0.0.1   localhost
::1         localhost
192.168.244.128 ole1
[oracle@OLE1 ~]$

Now you've multiple ways to connect database from unix command prompt.

[oracle@OLE1 ~]$ sqlplus scott/tiger

[oracle@OLE1 ~]$ sqlplus scott/tiger@orcl

[oracle@OLE1 ~]$ sqlplus scott/tiger@192.168.244.128:1521/orcl

[oracle@OLE1 ~]$ sqlplus scott/tiger@//192.168.244.128:1521/orcl

[oracle@OLE1 ~]$ sqlplus "scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ole1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))"

[oracle@OLE1 ~]$ sqlplus scott/tiger
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 12 23:29:30 2017
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@OLE1 ~]$ sqlplus scott/tiger@orcl
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 12 23:30:00 2017
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected
[oracle@OLE1 ~]$ 

[oracle@OLE1 ~]$ sqlplus scott/tiger@192.168.244.128:1521/orcl
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 12 23:30:00 2017
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected
[oracle@OLE1 ~]$ 


[oracle@OLE1 ~]$ sqlplus scott/tiger@//192.168.244.128:1521/orcl
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 12 23:30:00 2017
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Connected
[oracle@OLE1 ~]$ 
[oracle@OLE1 ~]$ sqlplus "scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ole1)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))"

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 13 12:30:23 2017

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 
SQL> show user
USER is "SCOTT"
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@OLE1 ~]$