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'
minus
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
minus
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
minus
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
minus
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
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 an SQL statement.
The next section of this book will categorize the PL/SQL and binary
utilities according to the version of the database.
and include the URL for the page.
