DBA Tips Archive for Oracle


[an error occurred while processing this directive]

No Title

[an error occurred while processing this directive]

by Michael New, MichaelNew@earthlink.net, Gradation LLC

This article is translated to Serbo-Croatian language by Jovana Milutinovich from Webhostinggeeks.com.



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 - 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 server environment. Jeff's other interests include mathematical encryption theory, tutoring advanced mathematics, 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 20 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 and Mathematics.

Copyright (c) 1998-2019 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
Wednesday, 20-Feb-2013 13:01:23 EST
Page Count: 39260