SQL Statements:

The primary way of communicating with the Oracle database is through SQL Statements.

Submitting Commands and SQL to the Database:

There are several ways to send SQL Statements to the Oracle database.

• By direct communication via the command-line interface of SQL*Plus
• Indirectly via one of the communication tools such as:

graphical user interface, such as Oracle Enterprise Manager

Database Express (EM Express) or Oracle Enterprise Manager Cloud Control or any other tools.

About SQL*Plus:

As we mentioned earlier, SQL*Plus is the main tool for communicating with the Oracle database, through which you can start and close the database, set and initialize database variables (set database initialization parameters), and also create and manage users in the database, as well as create and modify objects in the database and run SQL queries, etc.

But before sending commands and queries via SQL Statements, you must connect to the database via SQL*Plus, and you can connect in one of the following ways:

Locally: This is connecting to the database that runs on the same server through which SQL*Plus is used.

Remotely: This is connecting via the network so that the database runs on another server.

About Connecting to the Database with SQL*Plus:

It should be noted that each Instance has its own ID, also called System ID (SID), and because one server may contain more than one Instance, and each Instance has its own files and also has a SID, when connecting to the database Locally, the Instance to be connected to must be specified by adjusting the operating system environment variables, while for the Remotely connection, the server address must be specified and also the SID or Database Service Name must be specified.

In the case of connecting Locally or Remotely, the system environment variables must be adjusted to help the operating system find SQL*Plus.

Set Operating System Environment Variables:

It depends on the operating system, but you may need to set the operating system environment variables before running SQL*Plus, or at least check that they are set correctly.

For example, most operating systems require you to set some system variables (ORACLE_SID and ORACLE_HOME), but some operating systems may require you to set some other variables.

In UNIX or Linux: Set the operating system environment variables via commands.

As for Windows: Oracle Universal Installer (OUI) automatically sets the variables

(ORACLE_SID and ORACLE_HOME) to the registry.

If the database was not created during the installation of Oracle Software, the OUI does not set the ORACLE_SID to the registry, so the ORACLE_SID variable must be set to the system environment.

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

setenv ORACLE_SID orcl

setenv ORACLE_HOME /u01/app/oracle/product/database_release_number/dbhome_1

setenv LD_LIBRARY_PATH $ORACLE_HOME/lib:/usr/lib:/usr/dt/lib:/usr/openwin/lib:/usr/ccs/lib

Example 1-2 Setting Environment Variables in UNIX (Bash Shell)

export ORACLE_SID=orcl

export ORACLE_HOME=/u01/app/oracle/product/database_release_number/dbhome_1

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/usr/dt/lib:/usr/openwin/lib:/usr/ccs/lib

Example 1-3 Setting Environment Variables in Windows

SET ORACLE_SID=orawin2

Start SQL*Plus:

Do one of the following:

Ensure that the PATH environment variable contains ORACLE_HOME/bin.

Change directory to ORACLE_HOME/bin. Ensure that the PATH environment

variable contains a dot (“.”).

Enter the following command (case-sensitive on UNIX and Linux):

sqlplus /nolog

You can also run the sqlplus command by specifying its complete path:

ORACLE_HOME/bin/sqlplus /nolog

Submit the SQL*Plus CONNECT Command:

After running SQL*Plus in the previous step via the sqlplus /nolog command

You can now connect to the Oracle Database Instance as follows:

Connecting to a Local Database User:

For example, you can connect from within the server via the SYSTEM user and the Prompt will ask you to enter the SYSTEM user password.

connect system

Connecting to a Local Database User with SYSDBA Privilege:

You can also connect from within the server to Oracle Database Instance with the user SYS using the SYSDBA privilege, and the Prompt will ask you to enter the SYS user password. It should also be noted that connecting to the database with the user SYS must be through the SYSDBA privilege.

connect sys as sysdba

Connecting to a Local Database User with SYSBACKUP Privilege:

You can also connect from within the server to Oracle Database Instance with the user SYSBACKUP using the SYSBACKUP privilege, and the Prompt will ask you to enter the SYSBACKUP user password. It should also be noted that connecting to the database with the user SYSBACKUP must be through the SYSBACKUP privilege.

Connecting Locally with SYSDBA Privilege with Operating System Authentication:

You can also connect to Oracle Database Instance via SYSDBA privilege, but verification is done via the operating system. In this case, you do not need to enter the password.

connect / as sysdba

Connecting with Easy Connect Syntax:

To connect to the database remotely via Easy Connect, you need to know a number of information:

Host Name or IP Address: The address or name of the server on which the databases operate.

Port: The port through which the listener receives connection requests to the database.

Oracle Service Name or SID: The name of the Instance or Service you want to connect to.

Example of connecting via Easy Connect:

connect Username@"Host:Port/Service_Name"

If the listener receives connection requests on the default port, you can delete the port from the connection string.