DBA Tips Archive for Oracle

  

Search & Win   click me  

Snapshot Database - (Producing DBA Reports in HTML)

by Jeff Hunter, Sr. Database Administrator

Contents

Introduction

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/dba_scripts_archive_Oracle.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,073,741,824         9,109,504         1
ONLINE    SYSAUX             PERMANENT    LOCAL      AUTO          1,073,741,824       545,062,912        51
ONLINE    USERS              PERMANENT    LOCAL      AUTO          1,073,741,824       133,365,760        12
ONLINE    SYSTEM             PERMANENT    LOCAL      MANUAL        1,073,741,824       525,991,936        49
ONLINE    EXAMPLE            PERMANENT    LOCAL      AUTO          1,073,741,824        63,045,632         6
ONLINE    UNDOTBS2           UNDO         LOCAL      MANUAL        1,073,741,824        11,927,552         1
ONLINE    TEMP               TEMPORARY    LOCAL      MANUAL        1,073,741,824        28,311,552         3
                                                               ----------------- ----------------- ---------
avg                                                                                                       18
sum                                                                7,516,192,768     1,316,814,848

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

Oracle 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, developing high availability solutions, 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 17 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.


Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Monday, 20-Sep-2010 22:29:00 EDT
Page Count: 24127