Then felt I like some watcher of the skies
When a new planet swims into his ken;
Or like stout Cortez, when with eagle eyes
He stared at the Pacific—and all his men
Look’d at each other with a wild surmise—
Silent, upon a peak in Darien.
“On First Looking into Chapman’s Homer” by John Keats
If you are going to be responsible for a database, you need to know what it contains and how it is being used. Which are the biggest tables? How are the data files, control files, and log files laid out? How many people have database accounts? How many people use the database at a time? Your first action when you acquire responsibility for a database should be to thoroughly explore it. Form-based tools such as Enterprise Manager, SQL Developer, and Remote Diagnostic Agent make it easy to explore the database.
Form-based tools also simplify the task of database administration. A long time ago in my career, I remember a manager looking on as I fixed a database problem. It took quite a while for me to get everything right, because the only tool I had was the SQL*Plus command-line tool and I was constructing SQL commands on the fly to fix the problem. A workman is as good as his tools. The manager was not very impressed.
As illustrated in Listing 9-1, I suggest that you create an account with DBA privileges for your personal use so that you are not tempted to use the SYS or SYSTEM account while browsing or performing chores. There are certainly times when you will have to use one of these accounts, but you should be able to do most of your daily work with your own account. This helps prevent unnecessary objects from being created in the SYS and SYSTEM schemas.
Enterprise Manager comes in two flavors: Database Express and Grid Control. Both of them are web-based tools. Database Express is used to manage a single database, whereas Grid Control is used to manage multiple databases. Database Express is automatically installed when you create a database. I use the terms Enterprise Manager and Database Express interchangeably in this rest of this chapter.
As discussed in Chapter 6, Database Express can be installed by the Database Configuration Assistant if you so choose—this is the recommended method.
As you saw in Figure 6-20, the URL that you need to connect to Database Express is displayed after Database Configuration Assistant has finished creating the database. The URL shown in Figure 6-20 is https://localhost:5500/em.; this can be used to connect from within the VM if you first install the Adobe Flash plugin. Replace localhost with the IP address of your VM to connect from outside the VM if you have opened the Database Express port; Chapter 6 explains how to do so. The starting screen of Database Express was shown in Figure 6-21.
As illustrated in Figure 9-1, you can accomplish a subset of DBA tasks—such as password resets—by using EM Express instead of command-line tools such as SQL*Plus.
Tip Before clicking the Apply or OK button on any task screen, click the Show SQL button to review the SQL statements that Oracle will use to perform the task. This is a great way to learn new SQL commands.
Chapter 5 discussed the installation of SQL Developer. Until a few years ago, Oracle was very weak in the area of tools for software developers, but this changed with the release of SQL Developer. This Java-based tool for the Windows platform can be downloaded from the Oracle Technology Network (http://otn.oracle.com). The direct link is www.oracle.com/technology/software/products/sql/index.html.
SQL Developer is primarily a tool for software developers, but DBAs will find it extremely useful. Figure 9-2 and Figure 9-3 show common uses—examining the structure of a table and checking the execution plan for a query. Notice how easy it is to browse through collections of different types of objects by using the object tree in the left pane and the tabs in the right pane. The SQL tab in particular is very interesting; it displays SQL commands for creating the object. You can use these SQL commands as a template for creating a similar object.
SQL Developer can also be used to perform typical database administration tasks such as identifying and terminating blocking sessions.1 In Figure 9-4, you can see that session 128 has been blocked by session 125 for 2,500 seconds. In Figure 9-5, you can see that session 125 is idle. It is waiting for a “SQL*Net message from client,” and you can decide to terminate the session.
Remote Diagnostic Agent (RDA) is provided by Oracle Support to collect all the information about a database and its host system that might aid in the diagnosis of a problem. Oracle Support typically asks that you use this tool and send the collected data to Oracle whenever you request help in solving a problem. Although it was created to aid in the diagnosis of a problem, it is of great help in exploring and documenting database configurations.
RDA organizes the information it gathers into an HTML framework for easy viewing; the starting URL is RDA__start.htm. This is a wonderful way to document all aspects of a system. Figure 9-6 shows an example of system information collected by RDA, and Figure 9-7 shows an example of database information.
Figure 9-7 is particularly interesting because it shows usage information of different Oracle features. You can use this information to check whether your organization is suitably licensed for the features it is using—for example, using Active Session History (ASH), Automatic Workload Repository (AWR), and Automatic Database Diagnostic Monitor (ADDM) requires a license for the Diagnostics Pack.
Tip RDA is a wonderful way to instantly create documentation about a database system. You can use RDA on all the databases in your organization and tie them together with a simple HTML framework.
Not surprisingly, Oracle stores database metadata—data about data—in tables, just as in the case of user data. This collection of tables is owned by the user sys and is called the data dictionary. An example is the ts$ table, which stores information about all tables in the database. However, the information in these tables is kept very condensed for efficiency, and Oracle therefore provides a large number of views that are more suitable for querying. If you have DBA privileges, you can query these views; an example is the dba_tablespaces view, also owned by sys. Familiarity with these views is the hallmark of a competent database administrator.
Table 9-1 lists a few of the better-known views. You can find a complete list in Oracle Database Reference along with detailed explanations. Listing 9-2 shows the columns of the ts$ table as well as the corresponding dba_tablespaces view. It also shows the actual definition of the dba_tablespaces view as listed in the dba_views view.
Name | Description |
---|---|
dba_users | Information about all users of the database |
dba_tablespaces | Descriptions of each tablespace |
dba_data_files | Descriptions of each data file in each tablespace |
dba_indexes | Descriptions of each index in the database |
dba_ind_columns | Descriptions of each column of each index |
dba_tables | Descriptions of each table in the database |
dba_tab_columns | Descriptions of each column of each table |
dba_views | Descriptions of each view in the database |
These views are typically queried by monitoring tools. For example, the query shown in Listing 9-3 displays the names of tablespaces that are more than 90 percent full.
Tip You can find a complete list of data dictionary views in a data dictionary view called DICTIONARY.
Several third-party tools are widely used; examples are Toad and DBArtisan. I particularly like DBArtisan because it allows access to multiple database technologies—Oracle, SQL Server, DB2, and MySQL—from a single console. Figure 9-8 shows the use of Toad to examine the structure of a table. Note the similarity to the corresponding screen of SQL Developer that you saw in Figure 9-2.
Here are some of the key points touched on in this chapter:
1You cannot use Enterprise Manager to identify and terminate blocking sessions unless you have a license for the Diagnostics Pack.
3.137.217.220