A hidden Oracle utility is not a utility that is physically hidden in the Oracle software. Rather, a hidden utility is an executable or PL/SQL package that is either undocumented or is included such that the documentation is difficult to find. For example, some PL/SQL packages are never loaded by the Oracle installer, yet their definitions remain in the operating system files.

The main directories where Oracle utilities reside are here:

• $ORACLE_HOME/bin -- This contains the binary executables used by the Oracle server. Most of the tools discussed in the Oracle Utilities book reside in this directory.
  
• $ORACLE_HOME/plsql/demo -- This contains a useful collection of SQL scripts related to many utilities, including the dbms_profiler utility.
  
• $ORACLE_HOME/rdbms/admin -- This contains many SQL scripts used for creating PL/SQL packages and their required environments.
  
• $ORACLE_HOME/sqlplus/admin -- This contains scripts used with autotrace and other utilities.
  
• $ORACLE_HOME/otrace/admin -- This is the administration directory for the Oracle Trace diagnostic tool.
  
• $ORACLE_HOME/otrace/sysman -- This is used by utilities such as oemctl and the Oracle Management Server (OMS).
  
• $ORACLE_HOME/otrace/lib -- This contains facility files used with the oerr utility.

Many of these undocumented utilities such as TKPROF have surfaced from the obscure and entered mainstream Oracle toolkits while others remain hidden inside the O/S.

Finding hidden Oracle utilities

The easiest way to find hidden utilities is to look for new packages within Oracle. The following query compares the packages in Oracle10g with new packages in Oracle11g, using a database link between two instances, each on a different release of Oracle:

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

This query will quickly display all package bodies owned by SYS that exist in Oracle11g but not in Oracle10g.

Finding hidden Oracle executable utilities

Discovering new binaries entails comparing two directories in the operating system and ignoring the duplicates entries. The dircmp UNIX command can be used to find only new entries within two directories:

dircmp -- s 
/u01/aoracle/product/9.0.3/bin 
/u01/oracle/product/10.2.0/bin

Here we compare the bin directory of 9i with Oracle 10.2.