The Structured Query Language (SQL) is the
lingua franca of all relational database management systems (RDBMS).
The American National Standards Institute (ANSI) accepted SQL as the
standard relational database language in October 1986 (ANSI
X3.135-1986). SQL has also been accepted by the International
Standards Organization (ISO standard 9075) and by the U.S.
government in the Federal Information Processing Standard FIPS-127.
A security enhancement has been added and is covered in ANSI SQL
Addendum I and issued as X3.135-1989 and 9075-1989. SQL92 is the
most recent standard, and Oracle's implementation is
entry-level-compliant and has numerous extensions that make it one
of the best SQL implementations available. Oracle9i SQL is
documented in Oracle9i SQL Reference, Release 1(9.0.1), Part No.
A90125-01, June 2001, Oracle Corporation. The latest SQL standard,
simply titled SQL3, was supposed to be approved by 1999;however, not
much is being said about it since its initial release in 1999, so it
would appear to have stalled. Fortunately, much of the Oracle object
features are supposedly SQL3- (SQL99-; described below) and ODMG-standard-compliant.
The latest SQL standard, published by ANSI
and ISO, is often called SQL99. The formal names of the new standard
ANSI X3.135-1999, ?Database Language SQL,?
Parts 1 ("Framework"), 2 ("Foundation"), 3 ("Bindings")
ISO/IEC 9075:1999, ?Database Language SQL,?
Parts 1 ("Framework"), 2 ("Foundation"), 3 ("Bindings")
The Oracle9i server, Oracle precompilers for
C/C++ release 8.1, Oracle Precompiler for COBOL, release 8.1,
and SQL*Module for ADA release 8.0.4 provide full or partial
conformance with the ANSI and ISO standards. The Oracle SQL
Reference Manual, Release 1 (9.0.1) provides charts in Appendix B
that show which sections of the database are in compliance, and more
important, which are not.
NIST testing of database compliance has been
suspended, so we have to take the vendors? word (Oracle) about
compliance for SQL3.
SQL is considered to be a nonprocedural
language, because of the way it processes sets of records and
provides automatic data access, or navigation. SQL also uses query
optimization; that is, the RDBMS kernel determines the optimum
method to reach the desired data so you don't have to. Under
Oracle8, Oracle8i, and Oracle9i, a cost-based or a rules-based
approach can be used. SQL is designed to be simple to learn and use.
Despite this simplicity, SQL provides a rich command set under which
virtually any combination of data in a database can be retrieved and
In SQL the major statement is SELECT. SELECT
allows the retrieval of data from the database. Multiple SELECTs can
be nested into a single statement, and, using NET8, SELECTs can even
span databases. SQL also allows insertion, update, and deletion of
data, as well as creation, modification, and deletion of database
objects such as tables, sequences, indexes, and tablespaces. SQL
provides for database security and consistency. Unlike other systems
where entirely different command sets govern the different areas of
database activities, SQL combines these functions into an integrated
command set. All of the SQL for Oracle is shown in the link to the
Oracle SQL manual on the Wiley Web site; refer to the appropriate
reference manual (Oracle9i SQL Reference, Release 1 (9.0.1), Part
No. A90125-01, June 2001, Oracle Corp. as of this writing) for more
details on command usage.
SQL is also portable. If two RDBMSs are
ANSI-compliant, then an SQL statement that works in one will work in
the other, assuming the data structures are the same. This only
applies to standard SQL commands; naturally, system-specific
extensions won't transport to other systems. A good example of this
is the Oracle STORAGE clause.
If the DBA is not familiar with SQL, it is
suggested that he or she look through the SQL Language Reference
Manual and become familiar with the basic commands. The SQL language
is utilized through SQL*Plus, Oracle Administrator Toolbar SQL
Worksheet, or SVRMGR.
The link to the SQL manual on the Wiley Web
page shows the formats for all SQL commands referenced in this book.
For more detailed explanations, the DBA should refer to the SQL
Language Reference Manual for the release of the Oracle database
under which he or she is operating.
SQL*Plus: An Oracle Extension to Standard
The SQL*Plus program allows users to access
SQL and the Oracle SQL extensions. This combination of commands,
SQL, and the Oracle SQL extensions enable users and the DBA to
access the database via standard SQL and to format input and output
using the SQL*Plus extensions to the language. In Oracle9i, the
Server Manager (SVRMGR) program has been completely eliminated in
favor of an extended SQL*Plus implementation. The most current
documentation as of this writing is SQL*Plus User's Guide and
Reference, Release 1 (9.0.1), PART# A88827-01, June, 2001, Oracle
SQL*Plus has a command buffer that stores
the current command and allows the user to edit the command using
the native system editor or via command-line input. SQL*Plus also
allows the user to store, retrieve, and execute SQL/SQL*Plus command
scripts, as well as PL/SQL command scripts. It allows use of
abbreviated commands and calls to the host system command language.
In SQL*Plus, local variables can be assigned as well as providing
control of system variables used for format and display
control. However, though the new SQL*Worksheet handles standard SQL
well, it will not handle the formatting commands from the SQL*Plus
SQL*Plus can also be used to dynamically
create command scripts that can then be used to perform en masse
changes to users, tables, or other Oracle objects. This is a very
powerful feature that will be used in many of the example scripts.
If DBAs are not familiar with SQL*Plus, it is suggested they review
the SQL*Plus reference guide to acquaint themselves with the
SQL*Plus can be accessed once the user has
set the appropriate system variables and path, which is done using
the ?.oraenv? command on UNIX and Linux; in some environments,
however, the ORACLE_SID variable may have to be set to point to your
database. On NT, the user must either set some environmental
variables or use connect aliases (for everything but the
prespecified password, I usually set up an SQLPLUS icon for each
instance I need to connect to). The format for invoking SQL*Plus is
Since an OPS$ account allows the user to get
into the Oracle system without specifying a password if he or she is
logging in from his or her normal account, the use of OPS$ accounts
should be restricted to ?captive?-type users, that is, users who can
only access the system via an appropriate secure menu system. Under
Oracle8, 8i, and 9i, the OPS$ format is the default, but the system
manager can assign whatever prefix he or she desires by use of the
OS_AUTHENT_PREFIX parameter in the INIT.ORA file.
SVRMGR: A Database Administrator's
As its name implies, SVRMGR (Server Manager)
is designed for the DBA and only for the DBA. The SVRMGR program
provides access to the database internals and to all database
objects. To use SVRMGR, the user must belong to the DBA group under
UNIX or Linux, or have the proper group specification in NT or W2K.
The SVRMGR product has both a line command version and an X Windows
implementation (SVRMGRL and SVRMGRM) on most operating systems where
it is appropriate, such as UNIX or NT.
In Oracle7, the use of SVRMGR is covered in
the Oracle7 Server Utilities User's Guide ; in later releases, it is
covered in the Server Manager's User Guide.
Changes under Oracle9i
In Oracle9i Server Manager is no longer