使用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

1 SET ORACLE_SID=orawin2

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