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.
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.
Use the PASSWORD command to change your database password. You may abbreviate the command to PASSWD.
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.
Use the HELP command to get help on 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
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.
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:
See Chapter 10 for more information about DESCRIBE, including examples of its use against procedures, functions, packages, synonyms, and object types.
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.
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.
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:
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.
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.
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>
Introduction to SQL*Plus
A Lightning SQL Tutorial
Generating Reports with SQL*Plus
Creating HTML Reports
Writing SQL*Plus Scripts
Extracting and Loading Data
Exploring Your Database
Tuning and Timing
The Product User Profile
Customizing Your SQL*Plus Environment
Appendix A. SQL*Plus Command Reference
Appendix B. SQL*Plus Format Elements
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
Authors: Jonathan Gennick
Similar book on Amazon