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.

SQL> CONNECT system
Enter password:
Connected.
SQL> CREATE USER ifernand
  2  IDENTIFIED BY change_immediately
  3  PASSWORD EXPIRE;

User created.

SQL> GRANT DBA TO ifernand;

Grant succeeded.

SQL> CONNECT ifernand
Enter password:
ERROR:
ORA-28001: the password has expired

Changing password for ifernand
New password:
Retype new password:
Password changed
Connected.

Enterprise Manager

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.

Figure 9-1. Performing password resets by using Enterprise Manager

SQL Developer

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.

Figure 9-2. Examining the structure of a table by using SQL Developer

Figure 9-3. Checking the execution plan of a query by using SQL Developer

SQL Developer can also be used to perform typical database administration tasks such as identifying and terminating blocking sessions.¹ 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.

Figure 9-4. Identifying a blocking session by using SQL Developer

Figure 9-5. Terminating a blocking session by using SQL Developer

Remote Diagnostic Agent

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-6. System information collected by Remote Diagnostic Agent

Figure 9-7. Database information collected by Remote Diagnostic Agent

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.

Dictionary Tables and Views

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.

SQL> DESCRIBE sys.ts$
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TS#                                       NOT NULL NUMBER
 NAME                                      NOT NULL VARCHAR2(30)
 OWNER#                                    NOT NULL NUMBER
 ONLINE$                                   NOT NULL NUMBER
 CONTENTS$                                 NOT NULL NUMBER
 UNDOFILE#                                          NUMBER
 UNDOBLOCK#                                         NUMBER
 BLOCKSIZE                                 NOT NULL NUMBER
 INC#                                      NOT NULL NUMBER
 SCNWRP                                             NUMBER
 SCNBAS                                             NUMBER
 DFLMINEXT                                 NOT NULL NUMBER
 DFLMAXEXT                                 NOT NULL NUMBER
 DFLINIT                                   NOT NULL NUMBER
 DFLINCR                                   NOT NULL NUMBER
 DFLMINLEN                                 NOT NULL NUMBER
 DFLEXTPCT                                 NOT NULL NUMBER
 DFLOGGING                                 NOT NULL NUMBER
 AFFSTRENGTH                               NOT NULL NUMBER
 BITMAPPED                                 NOT NULL NUMBER
 PLUGGED                                   NOT NULL NUMBER
 DIRECTALLOWED                             NOT NULL NUMBER
 FLAGS                                     NOT NULL NUMBER
 PITRSCNWRP                                         NUMBER
 PITRSCNBAS                                         NUMBER
 OWNERINSTANCE                                      VARCHAR2(30)
 BACKUPOWNER                                        VARCHAR2(30)
 GROUPNAME                                          VARCHAR2(30)
 SPARE1                                             NUMBER
 SPARE2                                             NUMBER
 SPARE3                                             VARCHAR2(1000)
 SPARE4                                             DATE

SQL> DESCRIBE sys.dba_tablespaces
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLESPACE_NAME                           NOT NULL VARCHAR2(30)
 BLOCK_SIZE                                NOT NULL NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENTS                               NOT NULL NUMBER
 MAX_EXTENTS                                        NUMBER
 MAX_SIZE                                           NUMBER
 PCT_INCREASE                                       NUMBER
 MIN_EXTLEN                                         NUMBER
 STATUS                                             VARCHAR2(9)
 CONTENTS                                           VARCHAR2(9)
 LOGGING                                            VARCHAR2(9)
 FORCE_LOGGING                                      VARCHAR2(3)
 EXTENT_MANAGEMENT                                  VARCHAR2(10)
 ALLOCATION_TYPE                                    VARCHAR2(9)
 PLUGGED_IN                                         VARCHAR2(3)
 SEGMENT_SPACE_MANAGEMENT                           VARCHAR2(6)
 DEF_TAB_COMPRESSION                                VARCHAR2(8)
 RETENTION                                          VARCHAR2(11)
 BIGFILE                                            VARCHAR2(3)
 PREDICATE_EVALUATION                               VARCHAR2(7)
 ENCRYPTED                                          VARCHAR2(3)
 COMPRESS_FOR                                       VARCHAR2(18)

SQL> SET LONG 10000
SQL> SELECT text
  2    FROM SYS.dba_views
  3   WHERE owner = ’SYS’
  4     AND view_name = ’DBA_TABLESPACES’; 

TEXT
--------------------------------------------------------------------------------
select ts.name, ts.blocksize, ts.blocksize * ts.dflinit,
          decode(bitand(ts.flags, 3), 1, to_number(NULL),
                 ts.blocksize * ts.dflincr),
          ts.dflminext,
          decode(ts.contents$, 1, to_number(NULL), ts.dflmaxext),
          decode(bitand(ts.flags, 4096), 4096, ts.affstrength, NULL),
          decode(bitand(ts.flags, 3), 1, to_number(NULL), ts.dflextpct),
          ts.blocksize * ts.dflminlen,
          decode(ts.online$, 1, ’ONLINE’, 2, ’OFFLINE’,
                 4, ’READ ONLY’, ’UNDEFINED’),
          decode(ts.contents$, 0, (decode(bitand(ts.flags, 16), 16, ’UNDO’,
                 ’PERMANENT’)), 1, ’TEMPORARY’),
          decode(bitand(ts.dflogging, 1), 0, ’NOLOGGING’, 1, ’LOGGING’),
          decode(bitand(ts.dflogging, 2), 0, ’NO’, 2, ’YES’),
          decode(ts.bitmapped, 0, ’DICTIONARY’, ’LOCAL’),
          decode(bitand(ts.flags, 3), 0, ’USER’, 1, ’SYSTEM’, 2, ’UNIFORM’,
                 ’UNDEFINED’),
          decode(ts.plugged, 0, ’NO’, ’YES’),
          decode(bitand(ts.flags,32), 32,’AUTO’, ’MANUAL’),
          decode(bitand(ts.flags,64), 64,’ENABLED’, ’DISABLED’),
          decode(bitand(ts.flags,16), 16, (decode(bitand(ts.flags, 512), 512,
                 ’GUARANTEE’, ’NOGUARANTEE’)), ’NOT APPLY’),
          decode(bitand(ts.flags,256), 256, ’YES’, ’NO’),
          decode(tsattr.storattr, 1, ’STORAGE’, ’HOST’),
          decode(bitand(ts.flags,16384), 16384, ’YES’, ’NO’),
          decode(bitand(ts.flags,64), 0, null,
                 decode(bitand(ts.flags,65536), 65536,’FOR ALL OPERATIONS’,
                 ’DIRECT LOAD ONLY’))
from sys.ts$ ts, sys.x$kcfistsa tsattr
where ts.online$ != 3
and bitand(flags,2048) != 2048
and ts.ts# = tsattr.tsid

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.

COLUMN free_percentage format 990.00

WITH total_space AS
     (SELECT   tablespace:name,
               SUM (CASE
                       WHEN autoextensible = ’YES’
                          THEN maxbytes
                       ELSE BYTES
                    END) AS total_space
          FROM dba_data_files
      GROUP BY tablespace:name),
     used_space AS
     (SELECT   tablespace:name,
               SUM (BYTES) AS used_space
          FROM dba_segments
      GROUP BY tablespace:name)
SELECT tablespace:name,
       100 - (fs.used_space / ts.total_space) * 100 AS free_percentage
  FROM total_space ts NATURAL JOIN used_space fs
 WHERE 100 - (fs.used_space / ts.total_space) * 100 < 10;

Third-Party Tools

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.

Figure 9-8. Examining the structure of a table by using Toad

Summary

Here are some of the key points touched on in this chapter:

• Enterprise Manager comes in two flavors: Database Express and Cloud Control. Both are web-based tools. Database Express is used to manage a single database, whereas Grid Control is used to manage multiple databases. You can accomplish most DBA tasks—from mundane tasks such as password resets and creating indexes, to complex tasks such as backup and recovery—by using Enterprise Manager instead of command-line tools such as SQL*Plus.
• SQL Developer is primarily a tool for software developers, but database administrators will find it very useful. Common uses are examining the structure of a table and checking the execution plan for a query. It can also be used to perform some typical database administration tasks such as identifying and terminating blocking sessions.
• Remote Diagnostic Agent (RDA) is a tool provided by Oracle Support to collect information about a database and its host system. RDA organizes the information it gathers into an HTML framework for easy viewing; it is a wonderful way to document all aspects of a database system.
• Oracle stores database metadata—data about data—in tables, just as in the case of user data. This collection of tables is called the data dictionary. The information in the data dictionary tables is very cryptic and condensed for maximum efficiency during database operation. The data dictionary views are provided to make the information more comprehensible to the database administrator.

Exercises

• Use any of the tools described in this chapter to answer the following questions about your database:
  • Which is the largest table in the database, and who owns it?
  • How many redo log groups have been created? Are the logs mirrored?
  • Which tables do not have indexes?
  • How many control files are being used? Where are they located?
  • What was the largest number of simultaneous sessions (high-water mark)?
  • Do the data files have fixed sizes, or do they expand automatically to accommodate more data?
• Use Oracle Enterprise Manager to perform the following tasks:
  • Reset the password of the hr user.
  • Add a data file to the USERS tablespace.

Footnotes

¹You cannot use Enterprise Manager to identify and terminate blocking sessions unless you have a license for the Diagnostics Pack.