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.