William Robertson code

xplanx.sql - an easier way to call dbms_xplan.display_cursor

2011-10-16T15:00:00.000Z

Similar to xplan, but calls dbms_xplan.display_cursor instead of Explain Plan.

Parallel PL/SQL launcher update

2008-08-01T19:54:00.007+01:00

A while ago I wrote an experimental function to submit up to four PL/SQL commands simultaneously using Oracle's Parallel Query capabilities and a pipelined function. The results were promising but somewhat frustrating, as I could only get two...

Parallel PL/SQL launcher

2007-10-21T23:36:00.000+01:00

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 10g

2007-04-22T21:24:00.000+01:00

Here is a 10g update of last year's 9i Sudoku solver. The row/column/square free values intersection code is simplified by the new MULTISET INTERSECT operator, the validation procedure now uses IS A SET, and I've used MEMBER OF in the new "cross...

DBMS_ADVISOR tuning advice

2006-11-15T13:01:00.000Z

One new feature in 10g is DBMS_ADVISOR, which analyses a specified query and recommend ways to improve performance. This script analyzes whatever query is in the SQL*Plus buffer, so when testing a query you can simply enter @tune

SQL*Plus Index report

2006-09-08T00:08:00.000+01:00

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.

Sudoku solver

2006-03-20T23:39:00.000Z

The idea behind this solution is to find the intersect set of {row.freelist, column.freelist, square.freelist}. If that gives only one value, that must be the value of the cell. Now if row, column and square could all be instances of a Sudoku...

Utility assortment

2005-12-03T19:28:00.000Z

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

2005-12-03T18:06:00.000Z

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

2005-12-03T17:47:00.000Z

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

2005-12-03T17:41:00.000Z

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

2005-12-01T00:44:00.000Z

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

2005-11-30T18:03:00.000Z

My handy Explain Plan script for SQL*Plus. Just type: @xplan

Which

2005-11-29T21:34:00.000Z

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. Usage - use any of the following: @which emp @which scott.emp @which em% The report...

My sessions

2005-11-28T22:27:00.000Z

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

2005-11-28T22:23:00.000Z

List all database sessions. Handy when you don't have TOAD, PL/SQL Developer etc around.

Dict

2005-11-28T00:34:00.000Z

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

2005-11-28T00:26:00.000Z

Dependency report for specified object (table, package etc) using DBA_DEPENDENCIES view.

Constr

2005-11-28T00:21:00.000Z

Database constraints report, for the specified table, e.g. "@constr emp". Requires three stored types, which are included (commented out) within the script.

Cardinality

2005-11-27T19:37:00.000Z

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

2005-11-27T19:31:00.000Z

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

2005-11-05T19:21:00.000Z

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

2005-11-05T19:14:00.000Z

Report on the population of [column] within [table]. Shows you how many null values exist in a column.

Biggest

2005-10-22T23:16:00.000+01:00

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.