Oracle DBA Tips Corner      Return to the Oracle DBA Tips Corner.

Snapshot Database - (Producing DBA Reports in HTML)

by Jeff Hunter, Sr. Database Administrator

Contents

1. Overview
2. Snapshot Database Scripts
3. Running Snapshot Database
4. About the Author

Overview

DBA's rely on Oracle's data dictionary views and dynamic performance views in order to support and better manage their databases. Although these views provide a simple and easy mechanism to query critical information regarding the database, it helps to have a collection of accurate and readily available SQL scripts to query these views.

Over the years, I have published a vast array of Oracle DBA scripts that can be used to manage many aspects of the database including space management, performance, backups, security, and session management. These scripts are available for individual download or can be downloaded as an archive using the link http://www.idevelopment.info/data/Oracle/DBA_scripts/common.zip. Once the scripts have been put in place, they can be easily run from within SQL*Plus while logged in as a DBA user. For example, to query tablespace information:

SQL> @dba_tablespaces Status Tablespace Name TS Type Ext. Mgt. Seg. Mgt. Tablespace Size Used (in bytes) Pct. Used -------- --------------- ----------- ---------- --------- ---------------- ---------------- --------- ONLINE UNDOTBS1 UNDO LOCAL MANUAL 1,283,457,024 9,043,968 1 ONLINE SYSAUX PERMANENT LOCAL AUTO 524,288,000 378,732,544 72 ONLINE USERS PERMANENT LOCAL AUTO 2,147,483,648 321,257,472 15 ONLINE SYSTEM PERMANENT LOCAL MANUAL 838,860,800 505,544,704 60 ONLINE INDX PERMANENT LOCAL AUTO 1,073,741,824 65,536 0 ONLINE UNDOTBS2 UNDO LOCAL MANUAL 1,283,457,024 22,282,240 2 ONLINE TEMP TEMPORARY LOCAL MANUAL 1,073,741,824 92,274,688 9 ---------------- ---------------- --------- avg 23 sum 8,225,030,144 1,329,201,152 7 rows selected.

Featured in this article is a new approach to SQL scripting that allows the Oracle DBA to collect a wide array of metrics and statistics using a single script. This Snapshot Database script will contain a collection of useful queries that run as a whole and produce an eye-catching HTML report. The report will contain critical database information including installed options, storage, performance data, and security.

Currently, I have three separate Snapshot Database scripts available for each major Oracle release (8i, 9i, and 10g).

Snapshot Database Scripts

Snapshot Database Scripts
Oracle Release	Snapshot Database Script	Sample Output
11g	Coming soon...	Coming soon...
10g	dba_snapshot_database_10g.sql	dba_snapshot_database_10g.html
9i	dba_snapshot_database_9i.sql	dba_snapshot_database_9i.html
8i	dba_snapshot_database_8i.sql	dba_snapshot_database_8i.html

Running Snapshot Database

After downloading the appropriate Snapshot Database script for your Oracle release, login to SQL*Plus as a DBA user and execute the script (no parameters required).

Note that the screen will clear upon starting the script and no feedback will be displayed while the script is running. After the script completes, the location of the HTML report is displayed and you will be logged out of the database.

SQL> connect system/manager@db_name SQL> @dba_snapshot_database_10g.sql Output written to: dba_snapshot_database_10g_ORCL_20080417.html Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP and Data Mining Scoring Engine options

About the Author

Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, capacity planning, database security, and physical / logical database design in a UNIX, Linux, and Windows server environment. Jeff's other interests include mathematical encryption theory, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 16 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science.