utilities for oracle developers
Below you'll find a small number of utilities that might come in handy from time-to-time. Feel free to use as you wish (please read this disclaimer first). As of December 2015, these utilities have been moved to the oracle-developer.net GitHub site and can be forked and enhanced as required.
- MyStats Utility
A variation on Jonathan Lewis's SNAP_MY_STATS package to report the resource consumption of a unit of work between two snapshots. Designed to work under constrained developer environments, this version has enhancements such as time model statistics and the option to snap and report on specific statistics. ***Update*** Now available in two formats: 1) as a PL/SQL package and 2) as a free-standing SQL*Plus script (i.e. no installation/database objects needed). June 2007 (updated November 2015) - XPlan Utility
A utility to add parent ID and execution order information to plans reported by DBMS_XPLAN. XPlan includes DISPLAY, DISPLAY_CURSOR and DISPLAY_AWR functionality for use in exactly the same way as the DBMS_XPLAN equivalents. Supports versions from 10g onwards. ***Update*** Now available in two formats: 1) as a PL/SQL package and 2) as a collection of three free-standing SQL*Plus scripts (i.e. no installation/database objects needed). January 2009 (updated October 2011) - RunStats Utility
A variation on Tom Kyte's invaluable RUNSTATS utility that compares the resource consumption of two alternative units of work. Designed to work under constrained developer environments and builds on the original with enhancements such as "pause and resume" functionality, time model statistics and the option to report on specific statistics. ***Update*** Now available in two formats: 1) as a PL/SQL package and 2) as a free-standing SQL*Plus script (i.e. no installation/database objects needed). January 2007 (updated October 2011) - MOATS
The (M)other (O)f (A)ll (T)uning (S)cripts. A tuning and diagnostics utility for SQL*Plus co-developed with Tanel Poder. Includes active session sampling and a TOP-style utility for displaying instance activity at regular refresh intervals. Supports versions from 10g Release 2 onwards. September 2010 (updated March 2011) - Dictionary Long Application
Two versions of an application to encapsulate the data dictionary views that have LONG columns. LONGs are converted to CLOBs in pipelined functions, enabling us to search, copy and manipulate these columns with string functions. A static version supports 9.2 and 10.1 and a dynamic ANYDATASET version supports 10.2 onwards. August 2007 (updated July 2010) - Collection Sorter Utility
A package of two functions to sort collections, including support for descending and distinct sorts. Works with versions from 9i Release 2 onwards but can be easily edited to support 8i. November 2009 - Collection Cardinality Utility
A utility to provide the CBO with the correct cardinality of small collections used in TABLE() queries (such as variable in-lists). This helps to achieve better execution plans without having to use the undocumented CARDINALITY hint. Supports versions from 10g onwards. November 2009 - PL/SQL Counter
A simple package to start and maintain a session-based counter. Useful for debugging and instrumentation. May 2008 - REPLACEF
A function to simplify string building and debugging by replacing multiple placeholders from a collection of inputs. January 2007 - IS_NUMBER
Two common, simple and effective IS_NUMBER functions for SQL and PL/SQL. October 2006 - PL/SQL Timer
A simple package to output the elapsed time between two points. Supports versions from 8i onwards. July 2005 - Data Dump Utility
Procedure to dump the contents of a table or the results of a query to flat file. February 2004 - Merge Counter
A package to capture and report separate INSERT and UPDATE rowcounts from a MERGE statement. February 2004 - Call Stack Parser
A small package of functions for parsing the PL/SQL call stack. Includes an implementation of "WHOAMI" and "CALLER". September 2003 - PUT_LINE
A simple wrapper to DBMS_OUTPUT.PUT_LINE to workaround the 255 byte limit (not needed from 10.2 onwards). April 2002