Using SQL*Plus and Oracle Enterprise Manager

There are many ways to connect and work with Oracle databases, but most often the interface and SQL*Plus command set offered in Oracle are used for this purpose. The SQL*Plus interface, in fact, opens a window to an Oracle database and is therefore very widely used by Oracle developers to create SQL and PL/SQL program units. For Oracle database administrators, this interface is also a very valuable tool for the following reasons.

• It allows us to execute queries in SQL language and blocks of code in PL/SQL language (which is an advanced procedural version of SQL language offered in Oracle) and get results.
• It allows us to execute commands related to database administration and automate them.
• It allows you to start and finish the database operation.
• It provides a convenient way to create reports on database administration.

In this article, I will start talking about how to use SQL*Plus to perform typical Oracle database administration tasks, about important SQL*Plus commands, and, in brief, how to create reports using SQL*Plus. You may not need to use the SQL*Plus interface to create most reports, but knowing how its numerous tools for report generation work is not a problem at all.

Start the SQL*Plus session

The SQL*Plus interface is a utility that is most often used to connect and work with Oracle databases. It is supplied as part of the Oracle Database 11g server software as well as the Oracle Client software and the new Oracle Instant Client software.

After installing SQL*Plus on a server or client machine, the process of connecting to the server or client and starting an SQL*Plus session looks very simple. Because each SQL*Plus session involves establishing a database connection (unless the /NOLOG parameter is applied), all that is required to start SQL*Plus and connect to the database is a valid username and password.

Configuring the Environment

Before calling SQL*Plus, you need to correctly configure the Oracle environment, in particular, you need to set values for environment variables such as ORACLE_SID, ORACLE_HOME, and LD_LIBRARY_PATH. In addition, sometimes it is necessary to set values for such environment variables as NLS_LANG and ORA_NLS11.

If the correct values are not set for the required environment variables, an error will occur. For example, if you do not set the correct value for ORACLE_HOME before starting SQL*Plus, the following error will occur:

$ sqlplus
Error 6 initializing SQL*Plus
Message file sp1.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
Error 6 on SQL*Plus initialization
No sp1.msb file could be found.
SP2-0750: It may be necessary to specify a directory in ORACLE_HOME where
Oracle software installed

In case of the above error it is enough to set the value for the environment variable ORACLE_HOME:

$ export ORACLE_HOME= /u01/app/oracle/product/11.1.0/db_1

SQL*Plus Instant Client software

To use SQL*Plus to install the entire server software Oracle Database is not necessary. If you need to communicate via the SQL*Plus interface with a database that is located on another server, the SQL*Plus Instant Client software is enough.

With this software, you can connect to any Oracle database running on any operating system remotely by specifying its name and applying the Oracle network connection identifier. The only requirement to connect to a remote database in such a way is to specify the name of the remote database in the file tnsnames.ora.

That is why for SQL*Plus Instant Client it is required to specify the ORACLE_HOME environment variable. There is also a method that does not require the application of the tnsnames.ora file on the client-server. It is called the easy connect method. Below is an example showing how to use a simple connection ID to establish a connection on behalf of an OE user to the testdb database located on the server:

$ sqlplus oe/oe@//myserver.mydomain:1521/testdb

In this example, 1521 is the port used by the listener to receive connection requests.

Starting an SQL*Plus session from the command line

Before connecting to an SQL*Plus session, you must first correctly configure the environment and specify which database on the server should be connected by default. This is done with the environment variable ORACLE_SID.

For example:

$ ORACLE_SID=orcl.
$ export ORACLE_SID

After specifying the database to be used by default (in the example above it is orcl) in the ORACLE_SID environment variable, you can access SQL*Plus from the command line invitation by simply entering the command sqlplus without a username and password. SQL*Plus will prompt you for a username and password. If a username is provided along with a command (e.g.: sqlplus salapati), SQL*Plus will prompt you to enter only the password. The database administrator shall log in on behalf of one of the administrative accounts.

Please, note! On UNIX servers, the entry shall always be in lower case. In Windows, the interface is not case sensitive. Except for this little detail, in all other respects, the command interface SQL*Plus works the same on Windows and all versions of UNIX and Linux platforms.

Of course, you can also enter a username and password directly when you call SQL*Plus, but then the password will be visible to others when you enter it. An example is given below:

$ sqlplus salapati/sammyy1
SQL>

Invitation to SQL (SQL>) means that connection to SQL*Plus is initiated and you can start typing SQL, PL/SQL and SQL*Plus commands and operators.

In order to connect to a different database than the default one, the following command shall be used:

$ sqlplus user_name@connection_identifier

Certain operations, such as starting and stopping work, are allowed to be performed only if you connect to SQL*Plus with SYSDBA or SYSOPER privileges. If you have SYSDBA (or SYSOPER) privileges, you can connect to SQL*Plus as follows:

$ sqlplus sys/sammyy1 AS SYSDBA
SQL> SHO USER
USER is "SYS"
SQL>

The AS design allows privileged connections to users who have been granted SYSDBA or SYSOPER system privileges.

If an authenticated operating system user account has been created in the database (formerly called OPS$ name; see Chapter 12), the connection can be established by simply slash (/) as shown below:

$ sqlplus /
SQL> SHO USER
USER is "OPS$ORACLE"
SQL>

You can also connect through the operating system authentication method by including the owner of Oracle software in the Database Administrators group (DBA):

$ sqlplus / AS SYSDBA
SQL> SHO USER
USER is "SYS"
SQL>

Please note that in all previous examples the database name was not specified when connecting via SLQ*Plus. This is explained by the fact that the connection was set to the default instance, i.e. the database indicated by the ORACLE_SID environment variable.

The database name need not be specified when using SQL*Plus to connect to the default database. To connect to another database accessible via the network, the connection identifier (network service name) shall be used.

For the note! The instance name, database name and service name may be the same or different.

From a theoretical point of view, you can connect to a database using the full syntax of the connection identifier, as shown in the following example, where the entire address is used to connect to the orcl database:

$ sqlplus salapati/sammyy1@(DESCRIPTION =
(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)
(CONNECT_DATA= (SERVICE_NAME=orcl.mycompany.com))

However, by using the network service name defined in the network file tnsnames.ora, you can connect to the database in a simpler way:

$ sqlplus salapati/sammyy1@orcl

In addition, a simple connection method may be used to connect to the database. The syntax of the simple connection method looks like this:

$ [//]host[:port][/[service name]]

For example, here is how to connect to the orcl database using this method:

$ sqlplus hr/hr_passwd@sales-server:1521/orcl.mycompany.com

Note that in case of a simple connection method, you do not need a network file (tnsnames.ora).

Whichever of the above methods is used, the SQL*Plus session will eventually be successfully installed either with the default database or with the one specified in the connection identifier.

Setting up a connection using the CONNECT command

SQL*Plus supports the CONNECT command, which allows you to connect on behalf of another user after logging into SQL*Plus. The following is an example of using the CONNECT command to make a connection on behalf of another user:

SQL> CONNECT new_user/password.
Connected.
SQL>

The following example shows how to connect to another database in SQL*Plus by providing the connection identifier as part of the CONNECT command:

SQL> CONNECT salapati/sammyy1@orcl
Connected.
SQL>

Before connecting to another database, make sure that the tnsnames.ora file contains the necessary information about connecting to the remote database.

The CONNECT command can be used in SQL*Plus together with / AS SYSDBA and / AS SYSOPER syntax as shown below:

CONNECT sys/sammy1@prod1 as sysdba
CONNECT / AS SYSDBA
CONNECT user/password AS SYSDBA
CONNECT / AS SYSOPER
CONNECT user/password AS SYSOPER

Start the SQL*Plus session without establishing a connection to the database using the /NOLOG parameter.
The SQL*Plus session can also be started without establishing a database connection by specifying the /NOLOG parameter together with the command sqlplus.

This may be necessary, for example, when starting a database or simply to use the commands available in SQL*Plus to write or edit scripts. After starting the SQL*Plus session, the CONNECT command can always be used to connect to the database.

Below is an example of using the /NOLOG parameter:

$ sqlplus /NOLOG
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jan 2 18:35:25 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL> SHO USER
USER is "
"
SQL> SHO SGA
SP2-0640: Not connected
SQL> CONNECT salapati/sammyy1
Connected.
SQL>
Connecting to SQL*Plus via Windows GUI

If you use the SQL*Plus GUI on your Windows machine, just click the SQL*Plus icon to start the SQL*Plus session and you will be prompted to enter your user name. Provided that the connection to the database is established through the corresponding entities in the tnsnames.ora file, you can start working with the SQL*Plus interface after entering the user name.

You can work with the SQL*Plus utility in either manual or scripted noninteractive mode. It goes without saying that vulnerable administrative tasks, such as database recovery, are better performed in the interactive mode. As for routine SQL processing operations, it is better to automate their execution with the help of scripts. In both cases, the commands themselves will look the same – the only difference will be the mode in which they are executed.

The syntax of the SQL*Plus connection command is shown below:

CONN[ECT] [{ registration_name | / } [AS {SYSOPER | SYSDBA | SYSASM}]].

Just for the record! In Oracle Database 11g, the SQLPLUS command supports the new argument -F, allowing SQL*Plus to receive FAN events (Fast Application Notification) from the RAC database.

Connecting on behalf of a user with SYSOPER, SYSDBA or SYSASM privileges is necessary to perform privileged operations such as database shutdown and startup or database backup or restore. SYSAM privilege is new in Oracle Database 11g and is designed to separate the usual database administration operations from the Automatic Storage Management (ASM).

Working in SQL*Plus

After connecting to the SQL*Plus interface, you can start typing any SQL*Plus, SQL or PL/SQL commands in it. As will be explained later in this chapter, SQL statements end either with a semicolon (;) or with a slash (/) and PL/SQL code blocks end only with a slash (/). The output can either be viewed on the screen or written to a file if you wish. SQL*Plus commands always end with a newline character. When entering an SQL*Plus command, the SQL*Plus client program parses it and if it is an SQL or PL/SQL statement, sends it to the database server for processing.