Use SQL * Plus to connect to the database and sqlplus to connect to the database

Use SQL * Plus to connect to the database and sqlplus to connect to the database

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 partitions des 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

Depending on your platform, you may have to set environment variables before starting SQL * Plus, or at least verify that the settings are correct.

For example, you must set ORACLE_SID and ORACLE_HOME on most platforms. In addition, we recommend that you set the PATH environment variable to include the ORACLE_HOME/bin directory. Some platforms may require additional environment variables:

• On UNIX and Linux platforms, you must enter an operating system command to set environment variables.
• On Windows, Oracle Universal Installer (OUI) Automatically assigns the value to ORACLE_HOME and ORACLE_SID in the Windows registry.

For all platforms, you must change the ORACLE_HOME environment variable when switching between instances with different Oracle Home Directories. If multiple instances share the same Oracle Home Directory, you must change only ORACLE_SID when switching the instance.

1 setenv ORACLE_SID orcl2 setenv ORACLE_HOME/u01/app/oracle/product/11.2.0/db_13 setenv LD_LIBRARY_PATH $ ORACLE_HOME/lib:/usr/dt/lib: /usr/openwin/lib:/usr/ccs/libExample 1-1 Setting Environment Variables in UNIX (C Shell) 1 SET ORACLE_SID = orawin2Example 1-2 Setting Environment Variables in Windows

Example 1-2 assume that ORACLE_HOME and ORACLE_SID are set in the registry, but you want to overwrite the registry value of ORACLE_SID to connect to other instances. On Windows, the environment variable value you set in the Command Prompt window overwrites the value in the registry.

 

Step 3: Start SQL * Plus

1 sqlplus/nolog

Step 4: Submit the SQL * Plus CONNECT Statement

TheCONNECTStatement is as follows:

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

 

The syntaxlogonIs as follows:

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

The following table describes the CONNECT statement Syntax:

/	Call the external authentication of the connection request. This type of authentication does not use the database password. The most common form of external authentication is operating system authentication. The user account of a specific host logs on to the host operating system for authentication. External authentication can also pass Oracle wallet or network service. See Oracle Database Security Guide
AS {SYSOPER | SYSDBA}	Indicates that the database user is using the SYSOPER or sysdba system privileges for connection. Only Some predefined administrative users or users who have already added the password file can use these permissions to connect. See: Administrative Privileges Using Operating System Authentication
Username	A valid database username. The database matches the user name with the data dictionary and prompts you to enter the User Password To verify the connection request
Connect_identifier (1)	An Oracle Net connection identifier used for remote connection. The exact syntax depends on the Oracle Net configuration. If omitted, SQL * Plus tries to connect to the local instance. A common connection identifier is the name of a network service. This is the alias of the Oracle Net connection Descriptor (network address and Database Service name. The alias is usually in the local The tnsnames. ora file on the local computer is parsed, but other methods can be used for parsing.
connect_identifier (2)	As an alternative, the connection identifier can use the simple connection syntax. Easy Connection provides out-of-the-box services for remote databases Instead of configuring Oracle Net Services on the client (local computer. Connection The simple connection syntax for the access identifier is as follows (double quotation marks must be included ): host[:port][/service_name][:server][/instance_name]   Server is a type of service processing program. Acceptable values are dedicated, shared, and merged. If omitted, The default type of server is selected by the listener: Shared Server (IF configured); otherwise, dedicated Server See Oracle Database Net Services Administrator's Guide
edition={edition_name|DATABASE_DEFAULT}	Specifies the version of the new database session. If you specify a version, it must exist and you must have Privilege. If this clause is not specified, the session uses the default database version. See Oracle Database Advanced Application Developer's Guide

 

References

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