Oracle code and scripts

  • Pretty PL/SQL
    Presenting PL/SQL code snippets in web pages, complete with syntax highlighting using Google Code Prettifier.
  • Ref cursor to CSV converter
    A PL/SQL utility for producing character-delimited output from SQL, with an option to save it to a file.
  • Performance analysis for a specified SQL ID
    How long does it typically take? How often is it called? What is the execution plan? Has it changed recently? Are the stats up to date?
  • undo_tracker.sql - how long will that session take to finish rolling back?
    You've cancelled or killed a long-running operation, but the session is still rolling back (you can see this from v$transaction). How much longer will it take?
  • xplanx.sql - an easier way to call dbms_xplan.display_cursor
    Similar to xplan, but calls dbms_xplan.display_cursor instead of Explain Plan.
  • Parallel PL/SQL launcher
    This is an experimental package for submitting PL/SQL calls in parallel using the Oracle SQL Parallel Query mechanism. Many have complained that PL/SQL has no multi-threading support, as Java (for example) does. The conventional solution is to use...
  • Sudoku solver
    The basic idea behind this was that if the intersect set of {row.freelist, column.freelist, square.freelist} gives only one value, then that must be the value of the cell. Now if row, column and square could all be instances of a Sudoku...
  • Automatic SQL tuning advice
    A handy SQL*Plus script for tuning a query using dbms_sqltune. Just run:@tune_sql_id [sql-id]
  • SQL*Plus Index report
    Report indexes for the specified table. Specify an index name to see details about that index (which table, what columns). Allows user.table, or user.index.
  • Utility assortment
    ECHO (word-wrapper for DBMS_OUTPUT), TO_DECIMAL, TO_BASE (number conversion functions), SEND_MAIL (HTML formatted email), LIST_ELEMENT (string tokeniser), SPLIT, TO_STRING etc, all in one rather arbitrary package.
  • Exception object
    An Exception object you can pass around between procedures. It was a bit of an experiment and I haven't found a real use for it yet, but maybe that's just because I've been working on warehouse systems for a while. Let me know if you find a use for...
  • Job control object
    Job control modelled on Unix: declare "job" objects, submit them, wait for them to complete. Uses DBMS_JOB to submit tasks, DBMS_ALERT for jobs to pass back status and error messages, so the calling procedure can easily track the background jobs it...
  • Timer object
    Timer object to simplify the routine task of capturing start and end times and displaying formatted results. Includes "stop", "resume", "restart", "elapsed" and "show" methods.
  • AVG and SUM for INTERVALs
    Presumably due to an oversight, Oracle have not yet overloaded the SUM and AVG functions to support the INTERVAL DAY TO SECOND datatype. Here are my AVG_DSINTERVAL and SUM_DSINTERVAL aggregate/analytic functions.
  • xplan.sql - Explain Plan utility
    My handy Explain Plan script for SQL*Plus. Just type: @xplan
  • Which
    Resolves an object name following the same resolution path as SQL or PL/SQL: e.g. given the name "EMP", figure out that it's a public synonym for SCOTT.EMPLOYEES by calling @which emp
  • My sessions
    A variation of sessions.sql, but limited to the current OS user account. Handy when there are a lot of sessions and you only want to see your own. I should probably write a generic one that takes parameters...
  • Sessions
    List all database sessions. Handy when you don't have TOAD, PL/SQL Developer etc around.
  • Dict
    Find Oracle data dictionary information, grouping the results into USER_/ALL_/DBA_/other. e.g. "@dict dict" finds DICT, DICTIONARY, DICT_COLUMNS, GV$LOGMNR_DICTIONARY and V$LOGMNR_DICTIONARY together with their descriptions.
  • Deps
    Dependency report for specified object (table, package etc) using DBA_DEPENDENCIES view.
  • Constr
    Database constraints report, for the specified table, e.g. "@constr emp". Requires three stored types, which are included (commented out) within the script.
  • Cardinality
    Reports on how unique a column is. For example, 90% of its values might be unique, with the remaining duplicates appearing in groups of twos and threes. This is useful when investigating a system that doesn't have a full set of unique constraints,...
  • Count
    Shows the distinct values of [column] within [table], e.g. "@count object_type all_objects", which is the same as "SELECT object_type, COUNT(*) FROM all_objects GROUP BY object_type", but less typing.
  • Most
    Find the value that occurs most often in a column. For example, "@most object_type all_objects" reports "VIEW" and the number of occurrences; or "@most object_type all_objects 5" to see the top 5 object types.
  • Percent
    Report on the population of [column] within [table]. Shows you how many null values exist in a column.
  • Biggest
    Specify a table and a column name, to find the longest value held in that column. Can be useful when analyzing usage patterns or diagnosing "value too large" errors.