使用SQL*Plus连接数据库

About SQL*Plus

SQL*Plus is the primary command-line interface to your Oracle database. You use SQL*Plus to start up and shut down the database, set database initialization parameters, create and manage users, create and alter database objects (such as tables and indexes), insert and update data, run SQL queries, and more.

Before you can submit SQL statements and commands, you must connect to the database. With SQL*Plus, you can connect locally or remotely. Connecting locally means connecting to an Oracle database running on the same computer on which you are running SQL*Plus. Connecting remotely means connecting over a network to an Oracle database that is running on a remote computer. Such a database is referred to as a remote database. The SQL*Plus executable on the local computer is provided by a full Oracle Database installation, an Oracle Client installation, or an Instant Client installation.

See Also:

SQL*Plus User’s Guide and Reference

Connecting to the Database with SQL*Plus

Oracle Database includes the following components:

• The Oracle Database instance, which is a collection of processes and memory

• A set of disk files that contain user data and system data

When you connect with SQL*Plus, you are connecting to the Oracle instance. Each instance has an instance ID, also known as a system ID (SID). Because there can be multiple Oracle instances on a host computer, each with its own set of data files, you must identify the instance to which you want to connect. For a local connection, you identify the instance by setting operating system environment variables. For a remote connection, you identify the instance by specifying a network address and a database service name. For both local and remote connections, you must set environment variables to help the operating system find the SQL*Plus executable and to provide the executable with a path to its support files and scripts. To connect to an Oracle instance with SQL*Plus, therefore, you must complete the following steps:

Step 1: Open a Command Window

Step 2: Set Operating System Environment Variables

根据你的平台，您可能必须在启动SQL * Plus之前设置环境变量，或至少验证设置是否正确

例如，在大多数平台上，必须设置ORACLE_SID和ORACLE_HOME。另外，建议将PATH环境变量设置为包含ORACLE_HOME / bin目录。有些平台可能需要额外的环境变量：

• 在UNIX和Linux平台上，您必须通过输入操作系统命令来设置环境变量。
• 在Windows平台上，Oracle Universal Installer（OUI）会自动将值分配给Windows注册表中的ORACLE_HOME和ORACLE_SID。

对于所有平台，当在具有不同Oracle主目录的实例之间切换时，必须更改ORACLE_HOME环境变量。如果多个实例共享同一个Oracle主目录，则在切换实例时必须仅更改ORACLE_SID。

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

Example 1-1 Setting Environment Variables in UNIX (C Shell)

1 SET ORACLE_SID=orawin2

Example 1-2 Setting Environment Variables in Windows

Example 1-2假定在注册表中设置了ORACLE_HOME和ORACLE_SID，但你希望覆盖ORACLE_SID的注册表值以连接到其他实例。 在Windows上，您在命令提示符窗口中设置的环境变量值会覆盖注册表中的值。

Step 3: Start SQL*Plus

 1 sqlplus /nolog 

Step 4: Submit the SQL*Plus CONNECT Statement

the CONNECT statement is as follows:

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

The syntax of logon is as follows:

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

下表介绍了CONNECT语句的语法：

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

参考资料

https://docs.oracle.com/cd/E11882_01/server.112/e25494/dba.htm#ADMIN12472