Connecting to a Remote Database

Many large installations run Oracle on more than one computer. Such computers are often connected in a network, which permits programs on different computers to exchange data rapidly and efficiently. Networked computers can be physically near each other, or can be separated by large distances and connected by telecommunication links.

Databases on other computers or databases on your host computer other than your default database are called remote databases. You can access remote databases if the desired database has Oracle Net and both databases have compatible network drivers.

You can connect to a remote database in one of two ways:

• From within SQL*Plus, using the CONNECT command.

• As you start SQL*Plus, using the SQLPLUS command.

Connecting to a Remote Database from within SQL*Plus

To connect to a remote database using CONNECT, include a Oracle Net database specification in the CONNECT command in one of the following forms (the username and password you enter must be valid for the database to which you wish to connect):

• CONNECT HR@connect_identifier

• CONNECT HR/HR@connect_identifier

SQL*Plus prompts you for a password as needed, and connects you to the specified database.

Like any database connection, if you log on or connect as a user whose account has expired, SQL*Plus prompts you to change your password before you can connect. If an account is locked, a message is displayed and connection as this user is not permitted until the account is unlocked by your DBA.

When you connect to a remote database in this manner, you can use the complete range of SQL and SQL*Plus commands and PL/SQL blocks on the database.

The exact string you enter for the service name depends upon the Oracle Net protocol your computer uses. For more information, see CONNECT in Chapter 8 and the Oracle Net guide appropriate for your protocol, or contact your DBA.

Connecting to a Remote Database as You Start SQL*Plus

To connect to a remote database when you start SQL*Plus, include the Oracle Net service name in your SQLPLUS command in one of the following forms:

• SQLPLUS HR@connect_identifier

• SQLPLUS HR/HR@connect_identifier

You must use a username and password valid for the remote database and substitute the appropriate service name for the remote database. SQL*Plus prompts you for username and password as needed, starts SQL*Plus, and connects you to the specified database. This is the database used until you CONNECT to another database, DISCONNECT, or leave SQL*Plus.

Like any database connection, if you log on or connect as a user whose account has expired, SQL*Plus prompts you to change your password before you can connect. If an account is locked, a message is displayed and connection as this user is not permitted until the account is unlocked by your DBA.

Once again, you can manipulate tables in the remote database directly after you connect in this manner.

Note: Do not confuse the @ symbol of the connect identifier with the @ command used to run a command file.