Selecting an Instance with Environment Variables
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.
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:
When prompted, specify the SID for the database.
On Windows, you must set only the ORACLE_SID environment variable to select an instance before starting SQL*Plus.