SQL Plus - How to connect to a particular oracle database instance ?

> Procedural Languages > (SQL Plus|SqlCl) (Oracle Database Console)

1 - Selecting an Instance with Environment Variables

Before you attempt to use SQL*Plus to connect locally to an Oracle instance, you must ensure that environment variables are set properly.

3 - Steps

When multiple database instances exist on one server, or when an Automatic Storage Management (ASM) instance exists on the same server as one or more database instances, the environment variables determine which instance SQL*Plus connects to. (This is also true when there is only one Oracle instance on a server.)

For example, each Oracle instance (database or ASM) has a unique system identifier (SID). To connect to an instance, you must at a minimum set the ORACLE_SID environment variable to the SID of that instance. Depending on the operating system, you may need to set other environment variables to properly change from one instance to another.

This discussion applies only when you make a local connection—that is, when you initiate a SQL*Plus connection from the same machine on which the target instance resides, without specifying an Oracle Net Services connect identifier. When you make a connection through Oracle Net Services, either with SQL*Plus on the local or a remote machine, or with Enterprise Manager, the environment is automatically set for you.

Advertising

4 - Example

4.1 - Linux

Most UNIX installations come with two scripts, oraenv and coraenv, that can be used to easily set these environment variables.

  • Bourne, Bash, or Korn shell:
$ . /usr/local/bin/oraenv
source /usr/local/bin/coraenv

When prompted, specify the SID for the database.

4.2 - Windows

On Windows, you must set only the ORACLE_SID environment variable to select an instance before starting SQL*Plus.

SET ORACLE_SID=SAL1
lang/sqlplus/connect_instance.txt · Last modified: 2018/09/16 12:42 by gerardnico