When I was growing up, I was sometimes woken at night by the sound of a walking stick tapping on the ground—it was the night watchman patrolling the neighborhood. He would have had a better chance of surprising any burglars if he’d crept up on them quietly, but I never questioned why he advertised his presence so loudly. Armed only with a walking stick, he would have had to rely on strong lungs to wake up the neighborhood if he saw any burglars, so perhaps it was best to advertise his presence and hope that burglars would flee when they heard him coming. Nevertheless, the sound of his stick was comforting—it was good to know that someone trustworthy was watching the neighborhood while we slept.

The database administrator is responsible for watching the database. If something goes wrong with the database that could have been prevented, there is nobody else to blame. Database availability, changes, security, growth, backups, workload, performance, and capacity are some of the areas that should be monitored. Luckily, Oracle offers many tools for database monitoring. Enterprise Manager puts the information at your fingertips and can send out e-mail messages when things go wrong. Oracle also maintains all sorts of totals and counts that keep track of database workload, performance, and capacity. All that remains is to create regular snapshots of these numbers—this is automatically done by tools such as Statspack and Automatic Workload Repository (AWR). This time-series data can be manipulated with SQL queries and turned into graphs. You saw how to enable Statspack snapshots in Chapter 6. Statspack is a free tool, whereas AWR requires you to be licensed for Enterprise Edition as well as Diagnostics Pack. For that reason, I give equal time to Statspack and AWR in this book.

Monitoring Database Availability

The DBA must continuously monitor the availability of the database and take the necessary action to restore service. The best source of information is the alert log, which contains error messages as well as alert messages. A sample excerpt covering database startup is shown in Listing 10-1.

Starting up:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options.
ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1
System name:    Linux
Node name:      localhost.localdomain
Release:        3.8.13-16.2.1.el6uek.x86_64
Version:        #1 SMP Thu Nov 7 17:01:44 PST 2013
Machine:        x86_64
Using parameter settings in server-side spfile /u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfileorcl.ora
System parameters with non-default values:
  processes                = 300
  memory_target            = 396M
  control_files            = "/u01/app/oracle/oradata/ORCL/controlfile/o1_mf_9fxn1csd_.ctl"
  control_files            = "/u01/app/oracle/fast_recovery_area/ORCL/controlfile/o1_mf_9fxn1d0k_.ctl"
  db_block_size            = 8192
  compatible               = "12.1.0.0.0"
  db_create_file_dest      = "/u01/app/oracle/oradata"
  db_recovery_file_dest    = "/u01/app/oracle/fast_recovery_area"
  db_recovery_file_dest_size= 4800M
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = “”
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=orclXDB)"
  audit_file_dest          = "/u01/app/oracle/admin/orcl/adump"
  audit_trail              = "DB"
  db_name                  = "orcl"
  open_cursors             = 300
  diagnostic_dest          = "/u01/app/oracle"
  enable_pluggable_database= TRUE
NOTE: remote asm mode is local (mode 0x1; from cluster type)
Starting background process PMON
Starting background process PSP0
Sat Jul 05 22:59:50 2014
PMON started with pid=2, OS id=2103
Starting background process VKTM
Sat Jul 05 22:59:50 2014
PSP0 started with pid=3, OS id=2105
Sat Jul 05 22:59:51 2014
VKTM started with pid=4, OS id=2107 at elevated priority
Starting background process GEN0
Sat Jul 05 22:59:51 2014
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Starting background process MMAN
Sat Jul 05 22:59:51 2014
GEN0 started with pid=5, OS id=2111
Sat Jul 05 22:59:51 2014
MMAN started with pid=6, OS id=2113
Starting background process DIAG
Starting background process DBRM
Sat Jul 05 22:59:52 2014
DIAG started with pid=8, OS id=2117
Starting background process DIA0
Sat Jul 05 22:59:52 2014
DBRM started with pid=9, OS id=2119
Starting background process DBW0
Sat Jul 05 22:59:52 2014
DIA0 started with pid=10, OS id=2121
Starting background process LGWR
Sat Jul 05 22:59:52 2014
DBW0 started with pid=11, OS id=2123
Starting background process CKPT
Sat Jul 05 22:59:52 2014
LGWR started with pid=12, OS id=2125
Starting background process SMON
Sat Jul 05 22:59:53 2014
CKPT started with pid=13, OS id=2127
Starting background process RECO
Sat Jul 05 22:59:53 2014
SMON started with pid=14, OS id=2129
Starting background process LREG
Sat Jul 05 22:59:53 2014
RECO started with pid=15, OS id=2131
Starting background process MMON
Sat Jul 05 22:59:53 2014
LREG started with pid=16, OS id=2133
Starting background process MMNL
Sat Jul 05 22:59:53 2014
MMON started with pid=17, OS id=2135
Sat Jul 05 22:59:54 2014
starting up 1 dispatcher(s) for network address ’(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))’...
Sat Jul 05 22:59:54 2014
MMNL started with pid=18, OS id=2137
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /u01/app/oracle
Sat Jul 05 22:59:56 2014
ALTER DATABASE   MOUNT
Sat Jul 05 22:59:56 2014
Using default pga_aggregate_limit of 2048 MB
Sat Jul 05 23:00:01 2014
Successful mount of redo thread 1, with mount id 1379942639
Sat Jul 05 23:00:01 2014
Database mounted in Exclusive Mode
Lost write protection disabled
Ping without log force is disabled.
Completed: ALTER DATABASE   MOUNT
Sat Jul 05 23:00:02 2014
ALTER DATABASE OPEN

Fortunately, there is no need to stay glued to a computer the whole day, reviewing the alert log. You can use Oracle Enterprise Manager to monitor the database and send e-mail messages—or even open problem tickets—whenever problems are detected. Step-by-step instructions for configuring the monitoring capability can be found in the Oracle Database 12c 2 Day DBA manual provided on Oracle’s website.

It is even possible to customize and extend the alerting capabilities of Enterprise Manager. For example, an alert could be generated if a batch job continued running past a defined maintenance window. Corrective action can be taken automatically if needed; for example, a job that continues running outside a defined maintenance window can be killed automatically, or an incident record can be created in an incident-management system.

Monitoring Changes

The DBA can monitor changes to database objects using the Oracle auditing facilities. The command AUDIT ALL enables auditing for a wide variety of actions that modify the database and objects in it. Examples of such actions are ALTER SYSTEM, ALTER TABLESPACE, ALTER TABLE, and ALTER INDEX. The list of audited actions is contained in DBA_STMT_AUDIT_OPTS. Auditing of modifications to tables must be separately enabled, using the command AUDIT ALTER TABLE. If the database initialization parameter AUDIT_TRAIL is set to db_extended or xml:extended, Oracle also records the SQL statement associated with the action. As illustrated in Listing 10-2, you can find the information in the DBA_AUDIT_OBJECT view.

SQL> SELECT   TIMESTAMP,
  2           action_name,
  3           sql_text
  4      FROM dba_audit_object
  5     WHERE owner = ’IFERNANDEZ’
  6  ORDER BY TIMESTAMP;

TIMESTAMP         ACTION_NAME     SQL_TEXT
----------------- --------------- --------------------------------------------------
08/11/02 10:54:49 CREATE TABLE    CREATE TABLE mydual AS SELECT * FROM dual
08/11/02 10:54:57 ALTER TABLE     ALTER TABLE mydual ADD (dummy2 INTEGER)
08/11/02 10:55:28 DROP TABLE      DROP TABLE mydual

I have more to say on the subject of auditing in Chapter 14, in particular about the location and sizing of the audit trail.

Monitoring Security

The DBA can use the Oracle auditing facilities to monitor database usage. The AUDIT CREATE SESSION command causes all connections and disconnections to be recorded. Of particular interest for security purposes are the USERHOST, TERMINAL, and OS_USERNAME values in the DBA_AUDIT_SESSION view. These tell you where a connection originated; unusual values may indicate unauthorized intrusions. Listing 10-3 shows an example.

PDB1@ORCL> SELECT         userhost,
  2           os_username,
  3           username,
  4           COUNT (*)
  5      FROM dba_audit_session
  6  GROUP BY userhost,
  7           os_username,
  8           username
  9  ORDER BY userhost,
 10           os_username,
 11           username;

USERHOST                       OS_USERNAM USERNAME               COUNT(*)
------------------------------ ---------- -------------------- ----------
localhost.localdomain          oracle     APEX_LISTENER                 3
localhost.localdomain          oracle     APEX_PUBLIC_USER              6
localhost.localdomain          oracle     PMUSER                        1
localhost.localdomain          oracle     SOE                           5

PDB1@ORCL>
PDB1@ORCL> SELECT TIMESTAMP AS logon_time,
  2         logoff_time
  3    FROM dba_audit_session
  4  WHERE username = ’SOE’
  5  ORDER BY 1; 

LOGON_TIM LOGOFF_TI
--------- ---------
18-APR-15 18-APR-15
18-APR-15 18-APR-15
18-APR-15 18-APR-15
18-APR-15 18-APR-15
18-APR-15 18-APR-15

You should also check the database’s security settings. Here are two important questions to ask:

• Is there a password policy that forces users to change their passwords at regular intervals? Is password complexity enforced? Is an account automatically locked if there are too many failed login attempts? (The answers can be found in the DBA_PROFILES view.)
• Which users have DBA privileges? (The answer can be found in the DBA_ROLE_PRIVS view.)

Monitoring Backups

Oracle provides a utility called Recovery Manager (RMAN) for database backups and recovery. One of the advantages of RMAN is that it maintains detailed history information. You can use RMAN commands such as list backup to review the history of backups. You can issue commands such as report need backup and report unrecoverable to determine whether fresh backups are needed. You can also obtain a report of backups on the View Backup Report page of Enterprise Manager.

The use of RMAN for backups and recovery is discussed in Chapters 12 and 13, respectively.

Monitoring Growth

As illustrated in Listing 10-4, you can use the information in the DBA_DATA_FILES view to monitor the size of your database; the query shows the size in megabytes of each tablespace in the database.

SELECT   tablespace:name,
         SUM (BYTES) / 1048576 AS mb
    FROM dba_data_files
GROUP BY tablespace:name
ORDER BY tablespace:name;

You can incorporate the query in Listing 10-4 into a batch report with the SQL*Plus command-line tool, or you can execute it interactively with SQL Developer. Alternatively, as illustrated in Figure 10-1, Enterprise Manager offers a visual representation of space usage.

Figure 10-1. Database size report in Enterprise Manager

Table sizes can be monitored using the DBA_SEGMENTS view. The query in Listing 10-5 displays table sizes in megabytes; it uses the RANK function to limit the list to the ten biggest tables.

WITH ranked_information AS

     (SELECT owner,
             segment_name,
             BYTES / 1048576 AS mb,
             RANK () OVER (ORDER BY BYTES DESC) AS RANK
        FROM dba_segments
       WHERE segment_type = ’TABLE’)

SELECT   RANK,
         owner,
         segment_name,
         mb
    FROM ranked_information
   WHERE RANK <= 10

ORDER BY RANK;

In some cases, it is also useful to monitor the number of rows in specific tables; this is efficiently accomplished by checking the NUM_ROWS value in the DBA_TABLES view. NUM_ROWS is an approximate value that is updated when optimizer statistics are collected. If accurate counts are required, you can use the computationally expensive SELECT COUNT(*) command instead. Historical data is not automatically stored by Oracle; you have to develop your own method of capturing and storing this information. You could, for example, create a special table in which to store periodic snapshots of the number of rows in specific tables that are of interest to you.

Monitoring Workload

It is very important to understand the database workload. Most databases have a distinctive workload profile. If you regularly check your database workload profile, you can detect deviations and trends.

The V$SYSSTAT view keeps track of hundreds of different aspects of the database workload, such as logons, executions, commits, logical reads, physical reads, redo size, and the like. These metrics—with a few notable exceptions such as logons current and opened cursors current—are cumulative, which means their values increase during the lifetime of the database. Snapshots of the information in the V$SYSSTAT view are available in the STATS$SYSSTAT table (in the case of Statspack) and in the DBA_HIST_SYSSTAT view (in the case of AWR); they are described in Listing 10-6. The SNAP_ID (snapshot ID) and DBID (database ID) values are used to identify snapshots—they link to the STATS$SNAPSHOT table and the DBA_HIST_SNAPSHOT view, which contain details such as the time when the snapshot was created.

SQL> DESCRIBE v$sysstat;
 Name                                       Null?    Type
 ------------------------------------------ -------- -------------------
 STATISTIC#                                          NUMBER
 NAME                                                VARCHAR2(64)
 CLASS                                               NUMBER
 VALUE                                               NUMBER
 STAT_ID                                             NUMBER

SQL> DESCRIBE stats$sysstat;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SNAP_ID                                   NOT NULL NUMBER
 DBID                                      NOT NULL NUMBER
 INSTANCE_NUMBER                           NOT NULL NUMBER
 STATISTIC#                                NOT NULL NUMBER
 NAME                                      NOT NULL VARCHAR2(64)
 VALUE                                              NUMBER

SQL> DESCRIBE dba_hist_sysstat
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SNAP_ID                                   NOT NULL NUMBER
 DBID                                      NOT NULL NUMBER
 INSTANCE_NUMBER                           NOT NULL NUMBER
 STAT_ID                                   NOT NULL NUMBER
 STAT_NAME                                 NOT NULL VARCHAR2(64)
 VALUE                                              NUMBER

The Excel graph shown in Figure 10-2 was generated using the data produced by the SQL query shown in Listing 10-7. The graph shows a distinct pattern, indicating database stability. The PIVOT operator is used to produce new columns, and the LAG analytic function is used to operate on data in different rows. The use of subquery factoring breaks the query into logical pieces and makes it readable.

WITH
     -- Pivot the data in the STATS$SYSSTAT table
     -- Create separate columns for physical reads and logical reads

     pivoted_data AS

     (SELECT *
        FROM (SELECT snap_id,
                     NAME,
                     VALUE
                FROM stats$sysstat)
             PIVOT (SUM(value)
               FOR name IN (’physical reads’ AS physical_reads,
                            ’session logical reads’ AS logical_reads))),

     deltas AS

     -- Use the LAG analytic function to determine the amount of increase

     (SELECT snap_id,

             snap_time,

             snap_time
               - LAG (snap_time)
                   OVER (PARTITION BY startup_time
                         ORDER BY snap_id)
               AS duration,

             physical_reads
               - LAG (physical_reads)
                   OVER (PARTITION BY startup_time
                         ORDER BY snap_id)
               AS physical_reads,

             logical_reads
               - LAG (logical_reads)
                   OVER (PARTITION BY startup_time
                         ORDER BY snap_id)
               AS logical_reads

        FROM pivoted_data NATURAL JOIN stats$snapshot)

SELECT   snap_id,
         to_char(snap_time, ’yyyy/mm/dd hh24:mi’) as snap_time,
         physical_reads / duration / (24 * 60 * 60) as physical_reads_per_second,
         logical_reads / duration / (24 * 60 * 60) AS logical_reads_per_second
    FROM deltas

ORDER BY snap_id;

Figure 10-2. Excel graph of logical reads and physical reads produced using the time-series data in the STATS$SYSSTAT table

Monitoring Performance

Be sure to monitor the performance of important queries. The V$SQLAREA view records execution statistics such as executions, CPU time, elapsed time, logical reads, and physical reads for each SQL statement cached in the library cache—it is therefore a source of workload information as well as performance information. Snapshots of the information in the V$SQLAREA view are available in the STATS$SQL_SUMMARY table (in the case of Statspack) and in the DBA_HIST_SQLSTAT view (in the case of AWR). The SQL statement in Listing 10-8 retrieves the average execution times of each of two SQL queries of choice in successive time periods; the query can easily be modified to include additional queries that are critical to application performance. Notice the use of the LAST_VALUE analytic function to interpolate missing values.

WITH pivoted_data AS

     -- Pivot the data in STATS$SQL_SUMMARY using two values of the SQL ID column.
     -- q1 and q2 refer to the two values of interest in the SQL ID column.
     -- Create new columns representing the total elapsed time and the number of
     -- executions of each of the two queries.
     -- The newly created columns are named as follows:
     --   q1_elapsed_time
     --   q1_executions
     --   q2_elapsed_time
     --   q2_executions

     (SELECT *
        FROM (SELECT snap_id,
                     sql_id,
                     elapsed_time,
                     executions
                FROM stats$sql_summary)
             PIVOT (SUM(elapsed_time) AS elapsed_time,
                    SUM(executions) AS executions
               FOR sql_id IN (’&&1’ AS q1,
                              ’&&2’ AS q2))),

     interpolated_data AS

     -- Interpolate any missing values using the LAST_VALUE analytic function.
     -- Use the last non-null value after the database was started.
     -- The STATS$SNAPSHOT view tells us when the database was started.

     (SELECT snap_id,

             snap_time,

             startup_time,

             LAST_VALUE (q1_elapsed_time IGNORE NULLS)
               OVER (PARTITION BY startup_time
                     ORDER BY snap_id
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
               AS q1_elapsed_time_i,

             LAST_VALUE (q1_executions IGNORE NULLS)
               OVER (PARTITION BY startup_time
                     ORDER BY snap_id
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
               AS q1_executions_i,

             LAST_VALUE (q2_elapsed_time IGNORE NULLS)
               OVER (PARTITION BY startup_time
                     ORDER BY snap_id
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
               AS q2_elapsed_time_i, 

             LAST_VALUE (q2_executions IGNORE NULLS)
               OVER (PARTITION BY startup_time
                     ORDER BY snap_id
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
               AS q2_executions_i

        FROM pivoted_data NATURAL JOIN stats$snapshot),

     deltas AS

     -- Use the LAG analytic function to determine the amount of increase.

     (SELECT snap_id,

             snap_time,

             q1_elapsed_time_i
               - LAG (q1_elapsed_time_i)
                   OVER (PARTITION BY startup_time
                         ORDER BY snap_id)
               AS q1_elapsed_time_d,

             q1_executions_i
               - LAG (q1_executions_i)
                   OVER (PARTITION BY startup_time
                         ORDER BY snap_id)
               AS q1_executions_d,

             q2_elapsed_time_i
               - LAG (q2_elapsed_time_i)
                   OVER (PARTITION BY startup_time
                         ORDER BY snap_id)
               AS q2_elapsed_time_d,

             q2_executions_i
               - LAG (q2_executions_i)
                   OVER (PARTITION BY startup_time
                         ORDER BY snap_id)
               AS q2_executions_d

        FROM interpolated_data) 

-- Print the number of executions and average execution time for each time period.
-- Convert microseconds to seconds when printing the average execution time.
-- Don’t print any negative values.

SELECT   snap_id,

         TO_CHAR (snap_time, ’yyyy/mm/dd hh24:mi’) AS snap_time,

         CASE
           WHEN q1_elapsed_time_d > 0 AND q1_executions_d > 0
             THEN q1_executions_d
         END AS q1_executions_d,

         CASE
           WHEN q1_elapsed_time_d > 0 AND q1_executions_d > 0
             THEN q1_elapsed_time_d / q1_executions_d / 1000000
         END AS q1_elapsed_time_a,

         CASE
           WHEN q2_elapsed_time_d > 0 AND q2_executions_d > 0
             THEN q2_executions_d
         END AS q2_executions_d,

         CASE
           WHEN q2_elapsed_time_d > 0 AND q2_executions_d > 0
             THEN q2_elapsed_time_d / q2_executions_d / 1000000
         END AS q2_elapsed_time_a

    FROM deltas

ORDER BY snap_id;

Monitoring Capacity

The V$OSSTAT view offers cumulative values for operating system metrics such as CPU usage. Snapshots of the information in the V$OSSTAT view are available in the STATS$OSSTAT table (in the case of Statspack) and in the DBA_HIST_OSSTAT view (in the case of AWR); they are described in Listing 10-9. The SQL statement in Listing 10-10 computes the CPU utilization percentage for each time period.

SQL> DESCRIBE v$osstat;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STAT_NAME                                          VARCHAR2(64)
 VALUE                                              NUMBER
 OSSTAT_ID                                          NUMBER
 COMMENTS                                           VARCHAR2(64)
 CUMULATIVE                                         VARCHAR2(3)

SQL> DESCRIBE stats$osstat
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SNAP_ID                                   NOT NULL NUMBER
 DBID                                      NOT NULL NUMBER
 INSTANCE_NUMBER                           NOT NULL NUMBER
 OSSTAT_ID                                 NOT NULL NUMBER
 VALUE                                              NUMBER

SQL> DESCRIBE dba_hist_osstat
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SNAP_ID                                   NOT NULL NUMBER
 DBID                                      NOT NULL NUMBER
 INSTANCE_NUMBER                           NOT NULL NUMBER
 STAT_ID                                   NOT NULL NUMBER
 STAT_NAME                                 NOT NULL VARCHAR2(64)
 VALUE                                              NUMBER

WITH
     pivoted_data AS

     -- Pivot the data in the STATS$OSSTAT table
     -- Create separate columns for total idle time and total busy time

     (SELECT *
        FROM (SELECT snap_id,
                     osstat_id,
                     VALUE
                FROM stats$osstat)
             PIVOT (SUM(value)
               FOR osstat_id IN (1 AS idle_time,
                                 2 AS busy_time))),

     deltas AS

     -- Use the LAG analytic function to determine the amount of increase

     (SELECT snap_id,

             snap_time,

             idle_time
               - LAG (idle_time)
                   OVER (PARTITION BY startup_time
                         ORDER BY snap_id)
               AS idle_time,

             busy_time
               - LAG (busy_time)
                   OVER (PARTITION BY startup_time
                         ORDER BY snap_id)
               AS busy_time

        FROM pivoted_data NATURAL JOIN stats$snapshot)

SELECT   snap_id,
         to_char(snap_time, ’yyyy/mm/dd hh24:mi’) as snap_time,
         busy_time / (idle_time + busy_time) AS cpu_utilization_p
    FROM deltas

ORDER BY snap_id;

Third-Party Tools

Many organizations use third-party tools for enterprise-wide monitoring. Examples include Patrol from BMC, Tivoli from IBM, and OpenView from HP. Open source tools such as Nagios are also a popular choice. An enterprise-wide tool that is agnostic to database and server technologies will have more breadth than Oracle Enterprise Manager but not as much depth. However, Enterprise Manager is constrained by the number of extra-cost options that must be separately licensed—they include Provisioning Pack, Database Change Management Pack, Database Tuning Pack, Database Configuration Pack, Database Diagnostics Pack, System Monitoring Plug-in for Hosts, and so on.

If you join a team of DBAs, you should ask your colleagues for information about the monitoring tools being used in your organization.

Summary

Here is a short summary of the concepts touched on in this chapter:

• Database availability, changes, security, growth, backups, workload, performance, and capacity are some of the areas that should be monitored by the DBA.
• The alert log contains error messages and informational messages. The location of the alert log is listed in the V$DIAG_INFO view. The name of the alert log is alert_SID.log, where SID is the name of your database instance.
• Enterprise Manager monitors the database and sends e-mail messages when problems are detected.
• The command AUDIT ALL enables auditing for a wide variety of actions that modify the database and objects in it, such as ALTER SYSTEM, ALTER TABLESPACE, ALTER TABLE, and ALTER INDEX.
• The AUDIT CREATE SESSION command causes all connections and disconnections to be recorded.
• Recovery Manager (RMAN) maintains detailed history information about backups. RMAN commands such as list backup, report need backup, and report unrecoverable can be used to review backups. Enterprise Manager can also be used to review backups.
• Database size can be monitored using DBA_DATA_FILES; table size can be monitored using DBA_SEGMENTS.
• Oracle maintains a large number of totals and counts that keep track of database workload, performance, and capacity. Statspack and AWR create regular snapshots of these numbers—this time-series data can be manipulated with SQL queries and turned into graphs. The V$SQLAREA view records execution statistics such as executions, CPU time, elapsed time, logical reads, and physical reads for each SQL statement cached in the library cache. The V$OSSTAT view offers cumulative values of operating system metrics such as CPU usage.

Exercises

• Review the discussion of user management in Chapter 8. Also review the definitions of the DBA_ROLE_PRIVS and DBA_PROFILES views in Oracle Database 12c Reference in the Oracle documentation set. Answer the following questions about the database pdb1 in your VM: Which database users have DBA authority? Is there a password policy that forces database users to change their passwords at regular intervals? Is password complexity enforced? Is an account automatically locked if there are too many failed login attempts?
• Develop a system that tracks database growth and table growth. Create history tables to capture the information contained in the DBA_DATA_FILES and DBA_SEGMENTS views. Write SQL queries that report daily, weekly, and monthly growth.
• Modify the Statspack queries in this chapter for use with the corresponding AWR views: DBA_HIST_SNAPSHOT, DBA_HIST_SYSSTAT, DBA_HIST_SQLSTAT, and DBA_HIST_OSSTAT. The definitions of the AWR views can be found in the Oracle Database 12c Reference manual.

Further Reading

• Oracle Database 12c 2 Day DBA Chapter 3 (Introduction to Oracle Enterprise Manager Database Express. http://docs.oracle.com.
• Oracle by Example (OBE) tutorial “Getting Started with Oracle Enterprise Manager Express.”