SQL*Plus also comes with a command line interface. This is the interface you use when working from the command line. To start SQL*Plus at the command line you must do the following:

1. If connecting to a local database you must set the ORACLE_SID environment parameter to the name of your database. Here is an example of doing this in Windows, followed by an example of setting the ORACLE_SID in UNIX (as well as Oracle Home and path information so Unix can find the binaries):

Windows:

C:>Set ORACLE_SID=booktst

UNIX/Linux:

2. Next, we start SQL*Plus with the sqlplus command. When starting SQL plus include the user name that you wish to connect to. Here are examples of the use of this command:

Note in the example above that when you start SQL*Plus, you must provide the name of the user account. In this case, we logged into the account john_dba.  Next, SQL*Plus will prompt you for the password to this account. If you enter the correct password (you will not be able to see the password when you enter it in) then Oracle will pass you to the SQL prompt (SQL>).

You can also include the password when you call SQL*Plus, but this means that your password will not be hidden, and this can have serious security implications. Here is an example of using the user account and the password to log into SQL*Plus:

/u01/app> sqlplus john_dba/my_password

If you do this in a UNIX/Linux environment, the command ?ps ?ef? will display your password for anyone to see.

To perform specific DBA activities on the database (such as backing up the database or shutting it down), you will need to log in as a special type of user. This is because these operations require a special set of administrative privileges called the SYSDBA and SYSOPER privileges.

To activate these privileges, your user ID must be allowed to access these privileges (we will discuss setting up such an account later in this book) and you must use a special connection string to connect to the database to activate these privileges. If you want to connect using the SYSDBA privileges (these are super DBA privileges) then use the following syntax:

C:\>sqlplus "sys as sysdba"

You can also include the password if you prefer:

C:\>sqlplus "sys/my_password as sysdba"

The same method of connecting also works with the SYSOPER privilege. You simply replace sysdba with sysoper in the connection string. One final thing to notice is that the connection string is enclosed in quotes. This is required because there is white space (blanks) inside the connection string.

Oracle SQL*Plus Concepts

DBA's and developers are the most common users of SQL*Plus, and database management is frequently performed from SQL*Plus. SQL stands for Structured Query Language. It is the language that you use to communicate with Oracle, and it is the language you will use in SQL*Plus. You will see lots of examples of SQL in this book, and we will discuss them in detail when we come to them, so don't worry about the specifics of the language just now.

The following activities are examples of the things that a DBA will do from SQL*Plus:

* Create, Drop and alter database objects

* Startup and shutdown the database

* Create, Drop and alter database users

* Manage database security

* Manage database backup and recovery operations

The SQL *Plus prompt is also used to access the data within the Oracle database. DBA's, developers and even end-users will use SQL*Plus to execute database queries using the SQL language.

This is an excerpt from the bestselling "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam (Oracle ACE and Oracle Certified Master).  It?s only $19.95 when you buy it directly from the publisher here.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.