Using SQL*Plus and Oracle Enterprise Manager.

Preamble

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 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 unique 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, necessary 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 accounts, but knowing how its numerous tools for report generation work is not a problem.

Start the SQL*Plus session

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

After installing SQL*Plus on a server or client machine, 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 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 configure the Oracle environment correctly. 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.

An error will occur if the correct values are not set for the required environment variables. For example, if you do not set the right deal 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

It could find no sp1.msb file.

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

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

With this software, you can connect to any Oracle database 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 determine 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 process. 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 bound by default. It 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 start 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.

To connect to a different database than the default one, shall use the following command:

$ sqlplus user_name@connection_identifier

Certain operations, such as starting and stopping work, can only be performed 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 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 the database name was not specified in all previous examples when connecting via SLQ*Plus. It 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 full 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, using the network service name defined in the network file tnsnames.ora, you can connect to the database more simply:

$ sqlplus salapati/sammyy1@orcl

In addition, may use a simple connection method 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, will successfully install the SQL*Plus session 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.

You can also start the SQL*Plus session without establishing a database connection by specifying the /NOLOG parameter together with the command sqlplus.

It may be necessary, for example, when starting a database or to use the commands available in SQL*Plus to write or edit scripts. After beginning, the SQL*Plus session can always use the CONNECT command 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, click the SQL*Plus icon to start the SQL*Plus session, and you will be prompted to enter your user name. 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. Vulnerable administrative tasks, such as database recovery, are better performed interactively. 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 backup or restore. SYSAM privilege is new in Oracle Database 11g and is designed to separate the routine 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 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 order, 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.

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.​