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

1.3 Why Master SQL*Plus?

SQL*Plus is a universal constant in the
Oracle world. Every installation I have ever seen has this tool
installed. For that reason alone, it is worth learning. This is
especially true if you are a consultant and move around a lot. The
last thing you want is to be at a client site needing to look up
something mundane, such as an index definition, and not be able to do
it because you're not familiar with the
client's tools. SQL*Plus is always there.

If you are a DBA, SQL*Plus is undoubtedly a tool you already use on a
daily basis. Anything you use that often is worth learning and
learning well. You probably use SQL*Plus to query
Oracle's data dictionary tables in order to
understand the structure of your database. SQL*Plus can be used to
automate that task. Sometimes it's difficult to
remember the specific data dictionary tables you need to join
together in order to get the information you want. With SQL*Plus, you
can figure this out once and encapsulate that query into a script.
Next time you need the same information, you won't
have all the stress of remembering how to get it, and you
won't have to waste time rereading the manuals in
order to relearn how to get it.

SQL*Plus
is useful for automating routine DBA tasks. For
example, you can write scripts to help you create new users (as in
Example 1-5), report on database free space or the
lack thereof, perform nightly maintenance tasks, and the list goes
on. Anything you do to the database on a routine basis, SQL*Plus can
help you automate
it. If you are a developer, you can use SQL*Plus to build up queries,
develop ad hoc reports, and explore the data in your database. You
can use SQL*Plus to create and debug stored procedures, stored
functions, packages, and object types. If you have queries that
aren't performing well, you may be able to find out
why by using Oracle's EXPLAIN PLAN statement from SQL*Plus.
EXPLAIN PLAN will tell you the execution strategy chosen by the
optimizer for the query. Chapter 12
discusses EXPLAIN PLAN in more detail.

Many modern development tools provide GUI-based query generators.
These typically let you drag and drop tables into your query and then
draw lines between fields joining those tables together. This
drag-and-drop functionality may be great for a simple query that
joins a few tables, but I find that it quickly becomes cumbersome as
the query grows in complexity. It's not unusual,
when developing reports, to have queries that are many pages in
length. Sometimes these queries consist of several SELECT statements
unioned together, each query having one or more subqueries. When
developing one of those mega-queries, I'll take
SQL*Plus and a good editor over a GUI query tool any day of the week. Why?
Because with an editor I can keep bits and pieces of the query lying
around. Using copy and paste, I can pull out a subquery and execute
it independently without losing track of the larger query I am
building. I can easily comment out part of a
WHERE clause when debugging a query
and then uncomment it later. A good text editor lets you manipulate
query text in powerful ways that GUI interfaces do not.

Almost anything that you want to do with an Oracle database can be
done using SQL*Plus. You can write scripts to automate routine
maintenance tasks, report on the state of your database, or generate
ad hoc reports for end users. You can execute queries to explore your
database, and you can use SQL*Plus to create and manage any schema or
database object. Because of its universal availability, you will be
able to perform these functions anywhere you go. If you manage an
Oracle database or develop software to run against an Oracle
database, you will improve your productivity by mastering this tool.