Managing Databases with Sql*plus

There are several ways to connect and use an Oracle database, but you typically use the Oracle Sql*plus interface and a set of Sql*plus commands to connect to and use the Oracle database, Sql*plus provides access to the Oracle database interface.

This interface is a useful tool for Oracle DBA, and the main reasons are as follows:

1. You can use it to run SQL queries and PL/SQL code blocks and receive result information

2. You can publish DBA commands and work automatically

3. You can start and close the database

4, a convenient way to create a database management report

Environment variables

Before you can invoke Sql*plus, you must first correctly set up your Oracle environment, including:

Oracle_sid

Oracle_home

Ld_libbary_path

Sometimes you must also set the Nls_lang and ORA_NLS11 environment variables.

Connect Oracle using the Sql*plus Instant client software:

With this software, you can remotely connect to any Oracle database running on any operating system platform by simply giving the remote database name with the Oracle network connection identifier. The only requirement to connect to a remote database is to specify the remote database in the Tnsnames.ora file. This is why the ORACLE_HOME environment variable must be specified for the SQL * Plus instant client.

There is also a connection method that does not require the use of the Tnsnames.ora file on the client server. This method is called the Easy Connect method. Using a simple connection identifier, you can connect as a user OE to a database running on a server named MyServer, using the following commands:

Sqlplus Oe/[email Protected]//myserver.mydoamin:1521/testdb

To start a SQL *plus session from the command line

You can use the ORACLE_SID environment variable to set the default database.

ORACLE_SID=ORCL Export Oracle_sid

When Oracle_sid is set up, you can log in to the database using Sqlplus Username/password

In order to connect to a specified database instead of the default database, you must enter the following on the operating system command line:

$sqlplus [Email Protected]_identifier

If you have SYSDBA or (sysoper) permissions, you can log on to Sql*plus as follows:

$sqlplus Sys/oracle as SYSDBA

The AS clause allows users with SYSDBA or Sysoper system permissions to connect Sql*plus

If you have created an operating system authenticated user account in the database, you can connect using (/).

$SQLPLQ/

Theoretically, you can connect to the database with the full connection identifier syntax, but you can connect to the database in a simpler way by using the network service name defined in the network file Tnsnames.ora, as follows:

$sqlplus Scott/[email protected]

You can also use the Easy Connect method, such as:

$[//]host[:p Ort][/[servie_name]]

Connect using the Connect command

In SQL * Plus, you can use the Connect command to connect as a different user, and after you connect a database using the Connect command, you also have the ability to log on to a different database.

Connect newuser/newser_passwd

You can provide a connection identifier in SQL * Plus as a component of the Connect command to another database.

Connect Scott/[email protected]

Before you connect to another database, determine the connection information for the remote database in the Tnsnamers.ora file.

You can use the Connect command in SQL * Plus and the/as sysdba and/as sysoper syntax, as follows:

CONNECT Sys/[email protected] as Sysdba

Connect/as SYSDBA

CONNECT Username/password as SYSDBA

Connect/as Sysoper

CONNECT Usernmae/password as Sysoper

In starting a new Sql*plus session, use the Sqlplus command with the/nolog option to start the sql*plus session without connecting to the database:

Sqlplus/nolog

Managing Databases with Sql*plus