Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

2.3 Some Basic SQL*Plus Commands

Now that you know how to start SQL*Plus,
it's time to learn a few basic commands. This
section shows you how to exit SQL*Plus, how to change your database
password, how to get help, how to view a database table definition,
how to switch your database connection to another database, and more.

All SQL*Plus commands are case-insensitive; you may enter them using either lowercase or uppercase. In this book, commands are shown uppercase to make them stand out better in the text and examples. In practice, you're more likely to enter ad hoc commands in lowercase, and that's perfectly fine.

Filenames may or may not be case-sensitive, depending on your operating system. For example, under Windows, filenames are not case-sensitive, but under Unix, they are.

2.3.1 EXIT

A good place to start, because you've just seen how
to start SQL*Plus, is with the
EXIT
command.
EXIT
terminates your SQL*Plus session and closes the SQL*Plus window (GUI
version) or returns you to the operating system prompt. Used in its
simplest form, the EXIT command looks like this:

SQL> EXIT
Disconnected from Oracle Database 10g Enterprise Edition Release
10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
oracle@gennick02:~>

Some optional arguments to the EXIT command may be used to return
success or failure values to the operating system. These are useful
when running scripts in batch mode and are described fully in Chapter 11.

EXIT should really be thought of as two commands: COMMIT and EXIT. Any pending transaction will be committed, or made permanent, when you issue an EXIT command to leave SQL*Plus. Don't make the mistake of thinking that to EXIT is to ROLLBACK.

2.3.2 PASSWORD

Use the PASSWORD command to change your database password. You may
abbreviate the command to PASSWD.

The PASSWORD command was introduced beginning with SQL*Plus Version 8. In prior versions, you needed to use the ALTER USER command to change a password. To change other people's passwords, you need the ALTER USER system privilege.

Here is an example showing how the PASSWORD command is used:

SQL> PASSWORD
Changing password for GENNICK
Old password: *******
New password: *******
Retype new password: *******
Password changed If you are a DBA, you can change passwords for other users:

SQL> PASSWORD gennick
Changing password for gennick
New password:
. . .

If you are running a version of SQL*Plus prior to Version 8 (and I
surely hope you are not running anything that old), the PASSWORD
command will not be available. Instead, use the
ALTER USER command to change your password.
Here's how:

SQL> ALTER USER gennick IDENTIFIED BY secret;
User altered.

As you can see, you'll have to provide a username
even when you are changing your own password. You'll
have to live with your password's being displayed
visibly on the screen. The PASSWORD command, on the other hand, has
the advantage of not showing your new password.

2.3.3 HELP

Use the HELP command
to get help on SQL*Plus commands.

Prior to Oracle8 i Database, HELP also gave help on SQL and PL/SQL statements. In Oracle8 i , SQL and PL/SQL syntax became so complex that the SQL*Plus developers refocused the HELP system on only SQL*Plus commands.

Here's an example of how HELP INDEX (or HELP MENU
prior to Oracle8 i Database) can be used to get a
list of help topics:

SQL> HELP INDEX
Enter Help [topic] for help.
@ COPY PAUSE SHUTDOWN
@@ DEFINE PRINT SPOOL
/ DEL PROMPT SQLPLUS
ACCEPT DESCRIBE QUIT START
...

After identifying a topic of interest, you can get further help by
using that topic name as an argument to the HELP command. Here is the
information HELP returns about the DESCRIBE command:

SQL> HELP DESCRIBE
DESCRIBE
--------
Lists the column definitions for a table, view, or synonym,
or the specifications for a function or procedure.
DESC[RIBE] [schema.]object[@connect_identifier] Help is not available on all implementations. Early Windows versions
(in Oracle8 i Database and earlier) of SQL*Plus
don't implement the feature, and issuing the HELP
command will yield nothing more than the dialog shown in Figure 2-5.

SQL*Plus reads help text from a database table named HELP, owned by
the user SYSTEM. You will get a "HELP not
accessible" message if that table does not exist or
if SQL*Plus cannot select from it because of some other problem:

SQL> HELP
HELP not accessible.

Entering HELP without an argument will get you help on using HELP
itself.

SQL*Plus help text comes from the database and not from SQL*Plus. If you connect to a database using a version of SQL*Plus not matching the database version, there's a chance that any help text that you see may not match up with the capabilities of the version of SQL*Plus you are using.

2.3.4 DESCRIBE

The DESCRIBE

command
lists the
column definitions for a
database table. You can use it to view procedure, function, package,
and object definitions. If you have created and loaded the sample
tables described in Chapter 1, you can use the
DESCRIBE command to view their column definitions. The following
example uses DESCRIBE to list the columns in the
employee table:

SQL> DESCRIBE employee
Name Null? Type
----------------------------------------- -------- ---------------
EMPLOYEE_ID NOT NULL NUMBER
EMPLOYEE_NAME VARCHAR2(40)
EMPLOYEE_HIRE_DATE DATE
EMPLOYEE_TERMINATION_DATE DATE
EMPLOYEE_BILLING_RATE NUMBER(5,2) As you can see, the command lists three things for each column in the
table:

The column's name The column's datatype, and length if applicable Whether the column is allowed to be null
See Chapter 10 for more information about
DESCRIBE, including examples of its use against procedures,
functions, packages, synonyms, and object types.

2.3.5 CONNECT

Use CONNECT
to
log into your database as a different user or to
log into a completely different database. This command is useful if
you develop against, or manage, more than one database because you
can quickly switch between them when you need to. A developer or DBA
can commonly have multiple usernames on one database, with each being
used for a different purpose. A DBA might log in as SYSTEM to create
users and manage tablespaces but might choose to log in with a less
privileged username when running reports.

The simplest way to use the CONNECT command is to use it by itself,
with no arguments, as shown here:

SQL> CONNECT
Enter user-name: gennick
Enter password:
Connected.
SQL>

In this example, SQL*Plus prompted for a username and a password.
SQL*Plus did not prompt for a connect string (and
won't), so using this method allows you only to
connect to your default database.

Another form of the CONNECT command allows you to specify the
username, password, and connect string all on one line:

SQL> CONNECT gennick/secret
Connected.
SQL>

If you are security conscious (you should be) and happen to have
someone looking over your shoulder, you may omit the password and let
SQL*Plus prompt you for it. The advantage here is that the password
won't be echoed to the display:

SQL> CONNECT gennick
Enter password:
Connected.
SQL>

The Windows version of SQL*Plus will echo asterisks to the display
when you type your password. Command-line versions of SQL*Plus echo
nothing at all.

In at least one version of SQL*Plus, Version 8.0.4, there is a bug that prevents the CONNECT username technique from working. You can enter CONNECT with the username as an argument, then enter the password when prompted, but SQL*Plus won't pass the correct information to the database.

As you might expect, you can pass a connect string to CONNECT.
You'll need to do that any time you connect (or
reconnect) to a database other than your local, default database:

SQL> CONNECT gennick@db01
Enter password:
Connected.
SQL>

Go ahead and try the CONNECT command a few times, trying the
variations shown above. If you have only one username you can use,
try reconnecting as yourself just to get the hang
of
it.

2.3.6 DISCONNECT

The
DISCONNECT
command is one I rarely use.
It's the analog of the CONNECT command, and
disconnects you from the Oracle database while leaving you in
SQL*Plus. Here's an example:

SQL> DISCONNECT
Disconnected from Oracle Database 10g Enterprise Edition Release
10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>

Any pending transactions are committed before you are disconnected
from Oracle. At this point you have three choices:

Reconnect to Oracle using the CONNECT command.

Exit SQL*Plus.

Execute SQL*Plus commands that do not require a database connection.
The SET command, for example, does not require you to be connected.

DISCONNECT is useful if you want to leave a SQL*Plus session open for
a long period of time but do not wish to tie up a database
connection.

2.3.7 HOST

The HOST
command
lets you
temporarily drop out of SQL*Plus to your operating system command
prompt but without disconnecting your database connection:

SQL> HOST
oracle@gennick02:~/sqlplus/ExampleData> ls
bld_db.lst bld_db1y.sql bld_db2y.sql bld_tab.sql
bld_db1n.sql bld_db2n.sql bld_ins.sql build_db.sql
oracle@gennick02:~/sqlplus/ExampleData> mv build_db.sql bld_db.sql
oracle@gennick02:~/sqlplus/ExampleData> exit
exit
SQL>

I've found HOST to be a very
handy command. Many times, I've begun a SQL*Plus
session only to realize I needed to execute one or more operating
system commands. Rather than exit SQL*Plus and lose my connection and
any settings I've made and rather than open a second
command-prompt window, I can use the HOST command to drop out of
SQL*Plus, execute whatever commands I need, and then exit my
operating system shell (usually via an exit command) to return to
where I was in SQL*Plus.

As a shorthand for typing HOST, you can type a dollar sign ($) under Windows, or an exclamation point (!) under Unix/Linux:

SQL> !oracle@gennick02:~/sqlplus/ExampleData> lsbld_db.lst
bld_db1n.sql bld_db2n.sql bld_ins.sql
bld_db.sql bld_db1y.sql bld_db2y.sql bld_tab.sql
oracle@gennick02:~/sqlplus/ExampleData>

The dollar sign also happens to be the shortcut character under VMS.