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.
Articles Related
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.
Example
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
- C shell:
source /usr/local/bin/coraenv
When prompted, specify the SID for the database.
Windows
On Windows, you must set only the ORACLE_SID environment variable to select an instance before starting SQL*Plus.
SET ORACLE_SID=SAL1