Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Monitoring Database Tables, Indexes, Clusters, Snapshots, and Objects

Oracle Tips by Burleson

Since the days of SQLDBA or nothing (for you new folks, that was version 6), Oracle has come a long way with its database monitoring tools. Server Manager, Enterprise Manager, and the numerous other Oracle-provided monitoring tools make the task of monitoring databases easier.

That said, be aware that many of these tools still only monitor the very top level of the data dictionary, to answer questions such as how many users, how many tables, and how many indexes; they do little in the way of in-depth monitoring (to answer questions such as what is user X doing, when will table Y extend next). 

Note: Server Manager (SVRMGR), which was heavily used in Oracle7, 8, and 8i, is no longer available in 9i.

Several companies offer excellent monitoring tools: Precise, with its Q and Precise*SQL packages; the Quest product suite; Patrol from BMC; ECO Tools; and the many CA offerings spring to mind. All of these tools, however, while freeing some DBAs, end up crippling others because the DBAs never learn the underlying structures of the data dictionary. I am a firm believer in not providing complex, feature-rich tools to inexperienced DBAs, because they need to understand the data dictionary first. The point is, tools only go so far, and a DBA has to be able to dig into the data dictionary to find and correct problems. If you believe great tools make great works, try giving the best materials and the best tools to ineffective or inexperienced workers: the result may be better than with poor materials and tools, but it will probably still be unsatisfactory. As with all things, when using Oracle, knowledge means power: the power to solve problems or, better yet, to prevent them from happening in the first place.

In the past few years, since the publication of my first three books, I have interviewed dozens of candidates for Oracle DBA and developer positions. Some had great resumes, but when I began asking about the data dictionary and V$ tables, really getting under the hood of Oracle, I found many were clueless. To prevent anyone saying that about you, I refer you to Appendices D and E; they should become your map, guide, and bible to the Oracle data dictionary and support tables. Also, refer to those appendices (available on the Wiley Web site) throughout this chapter for definitions of the various views and tables used by the monitoring scripts. I have attempted to provide a “road map” of the views that relate to a specific topic (such as table or indexes). Use this map and The reference manual on the web page to see how the data dictionary views are laid out.

The data dictionary tables (which are usually suffixed with a dollar  sign ($), though there are exceptions) are owned by the SYS user and normally shouldn’t be accessed except when the supporting views don’t have the required data. These are documented in Appendix D. Instead, Oracle has provided DBA_, USER_, and ALL_ views into these tables, which should be used whenever possible; the DBA_ views are documented in Appendix E. In addition, Oracle has provided the dynamic performance tables (DPTs), which provide running statistics and internals information. These DPTs all begin with V$ or, in their pristine form, V_$. The DPTs are also documented in Reference manual..

The data dictionary views are created by the catalog.sql script, located in the $ORACLE_HOME/rdbms/admin directory. The catalog.sql script is a required readme file. The script has numerous remarks and comments that will improve your understanding of the data dictionary views severalfold. I warn you, though, the catalog.sql script is over 200 pages long.

Using the Oracle8, Oracle8i, and Oracle Data Dictionary

These tables, views, and DPTs provide detailed information about the system, the data dictionary, and the processes for the Oracle database. Reports can access the data dictionary objects to give the DBA just about any cut on the data he or she desires. Many, if not all, of the reports given in the following sections utilize these objects either directly or indirectly via the $tables, V$ DPTs, or DBA_ views.

The data dictionary objects can also be queried interactively during a DBA user session to find the current status of virtually any system parameter. The use of dynamic SQL against the DBA_ views can shorten a DBA task, such as switching users from one temporary tablespace to another or dropping a set of tables, by a factor of 10 or more.

In the days of SQLDBA, it was often easier to monitor with scripts than with the Oracle-provided monitor screens. The SVRMGR product, with its GUI interface, was an improvement, as was the Enterprise Manager. However, these still don’t provide the flexibility available from user-generated scripts. The Performance Pack can be customized to add user-generated monitoring queries. However, the Performance Pack is a cost add-on (and a performance cost) to the system DBA tools.

However, all of the above lack adequate report capabilities. To remedy this shortfall, DBAs must be prepared to create SQL, SQL*Plus, and PL/SQL reports that provide just the cut of information they require. This chapter discusses these reports and shows examples of scripts used to generate them. I suggest that, you, the DBA, review the contents of the V$ and DBA_ views as listed in either the Oracle, Oracle8i or Oracle Administrator’s Guide (Release 1 (9.0.1), June 2001, Part No. A90117-01, Oracle Corporation). Additional information is contained in the Oracle8, Oracle8i or Oracle Reference (Release 1 (9.0.1), June 2001, Part No. A90190-02, Oracle Corporation).

Using the Views and Dynamic Performance Tables to Monitor Tables, Clusters, Snapshots, Types, and Indexes

Now that we know all about the views and DPTs, let’s look at how they are used to monitor various database table-related constructs.


See Code Depot for Full Scripts

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter