For years, SQL*Plus has been the de facto standard tool for connecting to Oracle databases. Since the early versions of Oracle, it has been included as a part of any Oracle RDBMS installation. SQL*Plus is a command-line tool and is launched on all Oracle platforms using the command, sqlplus. This command-line tool has been a staple of Oracle DBAs for many years and has a powerful, interactive command interface that can be used to issue SQL statements, create database objects, launch scripts, and start up databases. For some beginners, that power comes at a price, in the form of SQL*Plus's somewhat steep learning curve. However, although SQL*Plus can be challenging to use as a SQL and PL/SQL development tool, it is an extremely useful tool for DBAs, since most of the commands we will execute are fairly straightforward. Until Version 11g, the Windows installation of Oracle included a version of SQL*Plus that could be executed from the desktop. That tool, invoked using the SQLPLUSW.exe program, was deprecated starting with Version 11g. Throughout the course of this book, we will learn to use SQL*Plus to execute basic administration commands such as startup and shutdown. The following is a screenshot of the command-line SQL*Plus tool from a DOS command line in Windows:
Oracle Enterprise Manager (OEM ) is an interesting and useful tool that has been available since Oracle Database Server Version 8.0. With Version 8i, Oracle moved to a Java-capable browser console that could be run standalone or with a management server. The management server served as a central repository for database information, and would communicate with various agents on the database servers. These agents acted to gather information from all the databases in the enterprise and relayed it to the management server, giving the DBA a centralized single point of administration. Enterprise Manager followed this architecture through Version 9i.
Beginning with Version 10g, Enterprise Manager experienced a graphical and architectural overhaul. OEM 10g featured a completely integrated browser interface that offered complete control of nearly every aspect of database administration, including storage allocation, schema object manipulation, backup and recovery, database parameter configuration, and performance monitoring. OEM 10g and 11g can be used in either of the two configurations, Database Control or Grid Control . Database Control runs on a database server and gives the DBA full administrative control of all aspects of a single database running on that server. Grid Control can be used at the enterprise level in the same way as Database Control can be used at the database level. Grid Control is installed as a centralized server with agents deployed on individual database servers. Like the earlier versions of OEM, these agents communicate with the Grid Control server, allowing the DBA direct control over every administrative aspect of these databases.
Another makeover for OEM comes with the latest version, Oracle Enterprise Manager 12c, Cloud Control. OEM 12c features a cleaner graphical interface and a number of new features, a few of which are listed here:
Integration with Oracle Support for easier creation and resolution of service requests
A compliance framework for easier monitoring and reporting in environments where compliance auditing and oversight is crucial
Improved web interface security
Enhancements providing better control over backup and recovery operations
Automatic discovery of databases
The following screenshot shows the UI of OEM 11g:
The Tool for Oracle Application Developers (TOAD ) is a full-featured development and administration tool for Oracle as well as other relational database systems, including Microsoft SQL Server, Sybase, and IBM's DB2. It was originally created by Jim McDaniel for his own use; he later released it as freeware for the Oracle community at large. Eventually, Quest Software acquired the rights to TOAD and began distributing a licensed version, while greatly expanding on the original functionality. TOAD is immensely popular among both DBAs and developers, due to its large feature set. For DBAs, it is a complete administration tool, allowing the user to control every major aspect of the database, including storage manipulation, object creation, and security control. For developers, TOAD offers a robust coding interface, including advanced debugging facilities. TOAD is available for download in both freeware and trial licensed versions. A screenshot of the TOAD UI is shown as follows:
DBArtisan (now called DBArtisanXE), by Embarcadero Technologies, is another complete suite of database management tools that operate across multiple platforms. DBArtisan is only available as a licensed product, but has extensive administration capabilities, including the ability to do advanced capacity and performance management, all packaged in an attractive and user-friendly GUI front end. A trial version is available for download from Embarcadero's website.
Oracle SQL Developer , originally called Raptor, is a GUI database interface that takes a somewhat different approach from its competitors. While many of the major licensable GUI administration products have continued to expand their product offerings through more and more add-on components, SQL Developer is a much more dedicated tool. Although it lacks a full set of administration features, it serves well as a streamlined SQL interface to the Oracle database. You can create and manipulate database objects in the GUI interface, as well as write and execute SQL statements from a command line. Administration-oriented activities such as storage control are left to other tools. SQL Developer aims to be a strong SQL and PL/SQL editor with some GUI functionalities. SQL Developer has gained popularity in recent years, in large part owing to several benefits, which are listed as follows:
It is completely free with no mandatory licensable components, although third-party add-ons are available for purchase.
It is a true cross-platform client-side tool written primarily in Java. While a majority of the commonly used SQL tools are available only on the Windows platform, SQL Developer runs on Windows, Linux, and even the Mac.
SQL Developer supports read-only connections to many popular databases, including SQL Server, Sybase, MySQL, Microsoft Access, DB2, and Teradata.
Because it is written in Java, it allows for the creation and addition of third-party extensions. If you want a capability that SQL Developer does not have, you can write your own!
It is provided by Oracle and is now included with any installation of Oracle database. It has essentially replaced SQL*Plus as Oracle's default SQL interface, although SQL*Plus is still available from the command line.
The UI of SQL Developer is as shown in the following screenshot:
For these reasons, the tools we use in this book for the purposes of demonstration will primarily be SQL Developer and SQL*Plus. From time to time, we'll also look at managing database operations with Enterprise Manager. But before we get ahead of ourselves, let's find out a little about the data we'll be using, and look at the Companylink database.