Oracle utilities locating binary executables

For Oracle support & training call (800) 766-1884

Oracle Utilities - Using Hidden Programs, Import/Export, SQL Loader, oradebug, Dbverify, Tkprof and More

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.

It typically 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 the same applies to utilities.

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

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 9.2, as compared to 8.1.7.3.

select object_name
from dba_objects
where owner = 'SYS'
and object_type = 'PACKAGE BODY'
minus
select object_name
from dba_objects@ORCL8173
where owner = 'SYS'
and object_type = 'PACKAGE BODY';

This query simply displays all of the package bodies owned by SYS that exist in 9.2 but not in 8.1.7.3. 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.
Binary Discovery

Discovering new binary executables is slightly more involved than discovering database objects. It entails comparing two directories in the operating system and ignoring the duplicates. The dircmp command in UNIX can be used for this procedure because it compares two directories and reports the differences – exactly what we want.

dircmp –s <directory 1> <directory 2>

The –s option of the dircmp command tells UNIX to eliminate the matches from the output.

Unfortunately, no operating system command exists on Windows to perform such a comparison. It is also more difficult to access the physical drives on separate Windows machines than UNIX machines. However, the DIR/B DOS command can be used to list the files in a directory and the output can be redirected to a file. The two files can then be compared using any number of tools.

The above is an excerpt from Oracle Utilities - Using Hidden Programs, Import/Export, SQL Loader, oradebug, Dbverify, Tkprof and More by Rampant TechPress.

For more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.

You can buy it direct from the publisher for 30% off directly from Rampant TechPress.

For more details and scripts, see my new book " Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.

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

Fast-Track Oracle Support
PO Box 511
Kittrell, NC 27544

Email BC: