Discovery

From Utiliwiki

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. The first thing to do is find them! 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.

Other Things 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 discovered:

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

select name
from v$parameter
minus
select name
from v$parameter@PRIOR_VERSION;

• 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
minus
select name
from v$obsolete_parameter@PRIOR_VERSION;

• 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
where view_name like 'V$%'
minus
select view_name
from v$fixed_view_definition@PRIOR_VERSION;

• 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
minus
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 a SQL statement.