SQL Scripts

explain.sql

by Jeff Maresh. A fully instrumented EXPLAIN PLAN script for Oracle8i, extracting informat ...(more)

by Jeff Maresh. A fully instrumented EXPLAIN PLAN script for Oracle8i, extracting information from all of the columns of the PLAN_TABLE. (less)

enqwaits.sql

SQL*Plus script creates a stored procedure named ENQWAITS, which produces a detailed repor ...(more)

SQL*Plus script creates a stored procedure named ENQWAITS, which produces a detailed report on enqueued (a.k.a. locked) "waiters" and their "blockers". It starts with the V$LOCK view but joins with V$SESSION, V$SQLAREA, V$TRANSACTION, and joins back to V$LOCK to find related locks. Another SQL*Plus script, run_enqwaits.sql (ASCII text 1Kb) is included to run this exhaustive report. Also, if you don't want to create a stored procedure and just run this as an anonymous PL/SQL block, then use this SQL*Plus script, temp_enqwaits.sql (less)

extmap.sql

SQL*Plus script that might save your life someday! The report's main purpose is to provide ...(more)

SQL*Plus script that might save your life someday! The report's main purpose is to provide a mapping of objects and their extents by the datafiles in the database, so that in the event of the need for an "object point-in-time" recovery, only the necessary datafiles need to be restored and recovered in the CLONE database. This report is one of those you hope you never have to use, but if you need it, you'll kiss me full on the lips for providing it to you! (less)

filestat.sql

SQL*Plus script to help you interpret the data in the V$FILESTAT view, to help detect thos ...(more)

SQL*Plus script to help you interpret the data in the V$FILESTAT view, to help detect those tablespaces with the greatest I/O load. This script depends on having the configuration parameter TIMED_STATISTICS set to TRUE (less)

function_idx.sql

SQL*Plus script to query the database and list all function based indexes.

gen_analyze_dd.sql

SQL*Plus script to query the Oracle data dictionary in order to generate another SQL*Plus ...(more)

SQL*Plus script to query the Oracle data dictionary in order to generate another SQL*Plus script (named run_analyze_dd.sql) to ANALYZE & VALIDATE STRUCTURE all of the clusters, tables, and indexes belonging to the SYS schema. This form of ANALYZE does not generate CBO statistics (which is verboten), but instead checks for any corruption. From Oracle8i v8.1.6 onwards, the parameter DB_BLOCK_CHECKING = TRUE always for all objects belonging to SYS, which can prevent smooth upgrade from databases running version 7.3.4 through 8.1.5 in which a database corruption bug resided. See the bulletin from Oracle Support. (less)

gen_pin.sql

I execute this script on a daily basis (at a low period) in order to pin frequently execut ...(more)

I execute this script on a daily basis (at a low period) in order to pin frequently executed objects that are not already pinned and to un-pin infrequently executed objects that have previously been pinned into the Shared Pool. (less)

gen_rebuild_idx.sql

Along with invalidated compiled objects (see gen_recompile_idx.sql below), indexes marke ...(more)

Along with invalidated compiled objects (see gen_recompile_idx.sql below), indexes marked UNUSABLE can be a real pain-in-the-neck for DBAs, causing lots of unnecessary problems. This SQL*Plus script uses the technique of SQL-generating-SQL to query the data dictionary and generate a tried-and-true index rebuild script. (less)

gen_recompile.sql

Invalidated objects (such as packages, procedures, views, etc) cause real trouble in envir ...(more)

Invalidated objects (such as packages, procedures, views, etc) cause real trouble in environments using the JDBC "Thin" drivers. In many cases, it seems that the only remedy is to disconnect and reconnect a new session to the database. To help prevent this, I use this simple SQL-generating-SQL script to find all invalidated objects and recompile them appropriately. (less)

hc.sql

SQL*Plus script which executes an "anonymous" PL/SQL block to perform a "health check" of ...(more)

SQL*Plus script which executes an "anonymous" PL/SQL block to perform a "health check" of a database according to my own preferences and prejudices. Recently updated to accommodate Oracle8i. Also, recently updated to remove accommodations for databases at versions below Oracle7 v7.3. My feeling is, if you're not at least running v7.3 (or above), then you've got troubles larger than this script can help with! (less)

i.sql

SQL*Plus script on the ALL_IND_COLUMNS view to display all indexes associated with a table ...(more)

SQL*Plus script on the ALL_IND_COLUMNS view to display all indexes associated with a table. NOTE: this script has been newly "inoculated" against the dreaded CBO bug involving queries against un-analyzed data-dictionary views and OPTIMIZER_MODE = FIRST_ROWS or ALL_ROWS. (less)

latches.sql

SQL*Plus script to query the V$LATCH view and detect the latch families experiencing the h ...(more)

SQL*Plus script to query the V$LATCH view and detect the latch families experiencing the highest contention. Please read the description in this script for proper interpretation of the report results! (less)

latchfree.sql

SQL*Plus script which queries the V$SESSION_WAIT view to identify all of the sessions curr ...(more)

SQL*Plus script which queries the V$SESSION_WAIT view to identify all of the sessions currently experiencing the "latch free" wait-event. It then joins to the V$LATCH_CHILDREN, V$SESSION, V$PROCESS, and V$SQLAREA views to acquire more information about the specific latch being held, about the session, and about the SQL statement currently being executed. (less)

logswitch.sql

SQL*Plus script for Oracle8 databases which runs an "anonymous" PL/SQL block to query the ...(more)

SQL*Plus script for Oracle8 databases which runs an "anonymous" PL/SQL block to query the V$LOG_HISTORY view to determine how often (on average) the redo log files are being switched. The report has three parts: by hour of day, by day of week, and by date from the beginning of the redo log history to the present. (less)

locks.sql

SQL*Plus script to query the V$LOCK view and provide a decoded report, identifying blocker ...(more)

SQL*Plus script to query the V$LOCK view and provide a decoded report, identifying blockers and waiters. (less)

mts.sql

SQL*Plus script which queries the V$DISPATCHER, V$SHARED_SERVER, and V$CIRCUIT views (amon ...(more)

SQL*Plus script which queries the V$DISPATCHER, V$SHARED_SERVER, and V$CIRCUIT views (among others) to report on the state of the Multi-Threaded Server (MTS) sub-system. (less)

nondefparm.sql

Query X$ tables to display any initialization parameters not at their default values.

parm.sql

Query X$ tables to display documented and undocumented initialization parameters