Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Advanced Oracle Utilities Methods of Discovery

Oracle Database Tips by Donald Burleson

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

Methods of Discovery

Although many utilities are documented here, the ability to "discover" them is important in order to know which ones exist.  This ability can be transferred with each new release of the database, revealing the presence of new utilities that may or may not be useful for the task at hand. 


It often takes months, or even years to become familiar with the key features of a new database version.  It takes time for the features to become well known, and that timeline also applies to the utilities.  The truly useful utilities often take much longer to find and become popular, though Oracle does not always document or publicize utilities well.  In some cases, the utility has a use that Oracle did not envision when the utility was written, and it becomes the job of the Oracle user community to exploit and document the utility to its fullest.


For example, the most popular technical sessions at Oracle Open World concern TKPROF even though the utility has existed for many years.  There is no need to wait for complete documentation since these utilities can be found and applied by the individual.  Once their existence is discovered, they can be investigated and their benefits and drawbacks can be identified.

Package Discovery

Fortunately, the PL/SQL packages that are installed in the database are the easiest utilities to find.  To reveal the new packages, a database link must be created within the version of Oracle that contains the packages.  The database link should point to a prior version of Oracle.  This allows a query to be executed to determine the differences between the two databases. 

For example, the following query, through a database link, displays the packages that were added in Oracle version 10.2 as compared to 9.2.


select object_name

   from dba_objects

   where owner      = 'SYS'

    and object_type = 'PACKAGE BODY'


    select object_name

      from  dba_objects@Oracle92

         where owner     = 'SYS'

         and object_type = 'PACKAGE BODY';


This query simply displays all of the package bodies owned by SYS that exist in 10.2 but not in 9.2.  The same query can be executed to compare packages in any two databases, provided that the database link object exists to connect the two instances. 

Other Changes Worth Discovering

The same approach used to discover new PL/SQL packages can be applied to find other useful features in new versions of the database.  The following new Oracle features can be easily revealed:

1.   Instance Parameters - New instance parameters can easily be identified with the        following query:

 select name

   from v$parameter


select name

   from v$parameter@PRIOR_VERSION;


2.   Obsolete Parameters - Oracle provides a list of obsolete parameters with each version of the database, beginning in 8.1.5. 

select name

  from v$obsolete_parameter


select name

   from v$obsolete_parameter@PRIOR_VERSION;


3.   V$ Views - New v$ and gv$ views are usually an indicator of new functionality in the database.  They are listed with the following query:

select view_name

   from v$fixed_view_definition


select view_name

   from v$fixed_view_definition@PRIOR_VERSION;


4.   System Events - The new system events are also very interesting to DBAs.  Some of them offer new tuning features.  They can be obtained from the following query:

select name

   from v$event_name


select name

   from v$event_name@PRIOR_VERSION;


The @PRIOR_VERSION contained in each query above represents a database link to another version of Oracle.  This designation can be used to find any new characteristic of the database via an SQL statement.


The next section of this book will categorize the PL/SQL and binary utilities according to the version of the database.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.