Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 Oracle Oerr Utility
Oracle Tips by Burleson Consulting

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

The oerr utility (command line executable) can be used to look up Oracle error messages. Many products within Oracle contain an msg file, or message library. Available only on UNIX platforms and not on Windows, type oerr at the command prompt to receive help on how to use this tool.

 

%oerr

Usage: oerr facility error

 

Facility is identified by the three-letter prefix in the error string.  For example, if the developer gets ORA-7300, "ora" is the facility and "7300" is the error.  So type "oerr ora 7300". If one gets LCD-111, type "oerr lcd 111", and so on.

DBMS_PROFILER

This package can be used to capture time spent on PL/SQL calls. Tracing helps find wait times for SQL, but what about time spent on PL/SQL? The steps to run DBMS_PROFILER are easy to perform. Prior to running, get the latest version of the source code from MetaLink. See Note 243775.1, “Implementing and Using the PL.SQL Profiler” and download the PROF.zip file.

 

Once the files have been downloaded and extracted, take a look at profiler_7.html and see if this utility does not impress with what it can do. The number of times a command or instruction was executed and the time spent doing it are readily seen in the HTML-formatted output. Best of all, this tool is free.

DBMS_DEBUG

For Forms developers, running debug in a Forms session is made easy because of the GUI interface and modal windows inside Forms Builder. In regular PL/SQL on the command line, the same is not true. Although DBMS_DEBUG can provide pretty much the same output as what is seen in Forms debugging, the overhead of running the debugger is somewhat problematic. MetaLink note 221346.1, “DBMS_DEBUG: Simple Example of Debugging An Anonymous Block” offers a fairly simple example of using the package.

 

The basic steps are to run two sessions. In the first, initialize debug, obtain an identifier, and call the code. A second session is then attached to the first using the identifier. So, in session one:

 

alter session set plsql_debug=true;

set serveroutput on

var x varchar2(50)

begin

  :x := dbms_debug.initialize();

  dbms_debug.debug_on();

end;

/

print x

 

begin

  do_whatever;

end;

/

 

In session two:

 

set serveroutput on

exec dbms_debug.attach_session('&ssid')

 

When done, turn off debugging and in session two, detach. The output is then available for viewing.

Summary

From release to release, the number of built-in packages within Oracle has shown a steady increase over the past ten years. It would be safe to assume this trend will continue. What defines a package as being a utility is mostly left to the interpretation of the user. The name of a package does not always belie its function. Do not think that only UTL packages are utility related. As shown in this chapter, utility-like tools can be named DBMS and even be command line executables.

 

In general, remember that utilities come in the following forms:

  • Built-in packages

  • Command line executable

  • External programs, from Oracle or not

Sage advice in construction applies here as well:  use the right tool for the job at hand. There are certainly plenty to choose from, so make sure the task is not being made harder when a simpler utility would have sufficed.

 


 


Fo
r 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.


 

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.