Oracle Internals from Basic to Advanced....: Connecting to the Database with SQL*Plus

I have seen some many posts in forums regarding the connecting to oracle database from client. Let us talk about the basics – how to connect to Oracle Database from beginner point of view.

Before connecting to Oracle DB there exist some pre-requisite need to be carry out based on the environments you deal with.

If you are working on Linux or Unix environment then follow the below environment settings before connect to Oracle DB

Unix or Linux :-

setenv ORACLE_SID orcl
setenv ORACLE_HOME /u01/app/oracle/product/11.2.0/db_1
setenv LD_LIBRARY_PATH $ORACLE_HOME/lib:/usr/lib:/usr/dt/lib:/usr/openwin/lib:/usr/ccs/lib

Windows:-

SET ORACLE_SID=orawin2

Note- ORACLE_HOME and ORACLE_SID are set in the registry but that you want to override the registry value of ORACLE_SID to connect to a different instance

Basic syntax of connect string follows as below

CONN[ECT] [logon] [AS {SYSOPER | SYSDBA}]

The syntax of logon is as follows:

{username | /}[@connect_identifier] [edition={edition_name | DATABASE_DEFAULT}]

username – valid username, database authenticates the connection request by matching username against the data dictionary and prompting for a user password.

connect_identifier – It’s an “net service name” (an alias for an Oracle Net connect descriptor ). The alias is typically resolved in the tnsnames.ora file on the local computer.

An alternative exists for the connect_identifier

"host[:port][/service_name][:server][/instance_name]"

host - host name or IP address of the computer hosting the remote database

port - TCP port on which the Oracle Net listener on host listens for database connections. 1521 – is the default port where it listens for – if we skip it listen on default port.

service_name - DB_NAME.DB_DOMAIN – in case if the DB_DOMAIN is null then DB_NAME needs to be supplied. It can be omitted if the Net Services listener configuration on the remote host designates a default service.

server – It’s type of connection we are getting connected to DB. Acceptable values are dedicated, shared, and pooled.

instance_name - instance to which to connect.

edition={edition_name | DATABASE_DEFAULT}

Specifies the edition in which the new database session starts. If you specify an edition, it must exist and you must have the USE privilege on it. If this clause is not specified, the database default edition is used for the session.

let’s check some of the examples:-

connect system

connect sys as sysdba

The above connection is made on the local system – residing on the server. when we executes the above connect statements it prompts for password.

connect /

connect / as sysdba

The above connects locally with operating system authentication.

Example for “Easy Connect Syntax”

connect salesadmin@"dbhost.example.com/sales.example.com"

connect salesadmin@"dbhost.example.com/sales.example.com:dedicated"

connect salesadmin@"dbhost.example.com:1522/sales.example.com"

connect salesadmin@"dbhost.example.com:1522/sales.example.com:dedicated"