Some Basic SQL*Plus Commands | Command-Line SQL*Plus

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.

Figure 2-5. The "No Help" dialog

figs/sqp2_0205.gif

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>

ls

bld_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.