Evergreen Database Technologies, Inc.
Expert Oracle Consulting Services
Downloadable Library
of scripts and other tools
Feel free to download as you wish. No
warranty is implied or provided -- use at your own risk!
Please provide credit for authorship if
you find these materials useful or if you pass them to others.
Unless otherwise noted, all materials
here are written by Tim Gorman.
Content
last updated: 25-May 2009
General
SQL*Plus scripts:
SQL*Plus
scripts related to STATSPACK:
SQL*Plus
scripts related to AWR:
UNIX
shell scripts for Oracle DBAs:
SQL*Plus
scripts (complete list, in alphabetic order by name):
- aqcoalesce.sql (ASCII text
– 7Kb) SQL*Plus script to create
the AQCOALESCE script documented in MetaLink note #271855.1 (entitled “Procedure to Manually Coalesce All
IOTs/Indexes Associated with AQ Tables”), which dynamically identifies
index-organized tables (IOTs) and indexes to be periodically rebuilt and
coalesced within the Oracle Advanced Queueing (AQ) mechanism. This DDL script is based on the source
provided in the MetaLink note, with some additional error handling and
monitoring (via DBMS_APPLICATION_INFO) added. Once the procedure is created with this
script, then the run_aqcoalesce.sql
(ASCII text – 1Kb) SQL*Plus script can be used to execute it in “debug” or
production mode as desired. Also,
the run_aqcoalesce.sh (ASCII text – 1Kb)
korn-shell script is also available to run the procedure from the UNIX “cron” utility, if desired. Thanks to Dan Fink for bringing this
procedure to light. New on 25-May 2009!!!
- audit_ddl.sql
(ASCII text – 2Kb) SQL*Plus script
to issue the necessary commands to enable database auditing of all DDL
commands (i.e. CREATE, ALTER, DROP, TRUNCATE, GRANT, REVOKE). DDL auditing is extremely useful in any
database environment (especially production), as it can help resolve
“mystery problems” caused by people making untracked changes to the
structure of the database. The
script will also submit a job to the DBMS_JOB package to purge the
database audit-trail table of entries older than 45 days, so be certain
that you edit this job (or comment it out) according to your own customs
and wishes.
- awr_evtrends.sql (ASCII text – 4Kb) SQL*Plus script to display “trends” for
specific wait events captured in the AWR repository over time, and display
summary totals hourly and daily as a ratio. The intent is to highlight the greatest
deviations from the norm over time.
It is also useful for use after a change has been made, to
demonstrate the impact (i.e. “before” vs “after” effects). It is similar to the “sp_evtrends.sql” script used with the STATSPACK
package… New on 17-November 2008!!!
- betwixt.sql (ASCII text file - 2Kb) SQL*Plus script with DDL commands to
create a pipelined table function named BETWIXT, which takes a range of
dates (i.e. begin-date and end-date) as parameters and returns all of the
time-intervals (i.e. interval size specified as a third parameter) between
them. Ideal for pivoting on ranges
of date/time values.
- cbo_stats.sql (ASCII text file – 4Kb) SQL*Plus script to display the
statistics used by the cost-based optimizer (CBO) at the global table- and
index-levels, as well as at the partition and sub-partition levels, if
they exist. New on 17-November 2008!!!
- dba_column_usage.sql (ASCII text file -
3Kb) SQL*Plus script with DDL
commands to create a view named DBA_COLUMN_USAGE on the useful (but
normally hidden) data dictionary table SYS.COL_USAGE$. This table is populated by the Oracle
cost-based optimizer (CBO) in Oracle9i and above with information about how
columns are used in the WHERE clauses of SQL statements. This view makes this data more readily
accessible for usage-tracking purposes.
- enqwaits.sql (ASCII text - 19Kb) 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 (ASCII text – 14Kb)…
- estimate_mttr.sql
(ASCII text file – 13Kb) SQL*Plus script to query the RMAN
“recovery catalog” repository to estimate how long a restore/recovery
operation might take, based on how long it took to perform the backups of
the pieces that will need to be restored.
This is certainly not a perfect estimate, as the amount of time to
perform a backup is not a reliable predictor of the amount of time to
perform a restore and recovery, but it provides a good general idea. New on
01-March 2008!!!
- exchpart.sql (ASCII text file - 16Kb) SQL*Plus script with DDL commands to
create the package EXCHPART, with its procedures PREPARE and FINISH. These packaged procedures facilitate the
use of the ALTER TABLE … EXCHANGE PARTITION command while loading data
into a data warehouse. The use of
the packaged procedures are demonstrated in the SQL*Plus test script “test_exchpart.sql” (ASCII text file – 1Kb). Updated on 14-Dec 2007!!!
- extmap.sql (ASCII text file - 3Kb) 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!
- filestat.sql (ASCII text file - 2Kb) 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…
- gen_analyze_dd.sql (ASCII text file –
2Kb) 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…
- gen_csv.sql (ASCII text file - 2Kb) SQL*Plus script
to be used to dump the contents of a table to a comma-separated file
(a.k.a. MS-Excel “.csv” text format).
- gen_pin.sql (ASCII text file – 2Kb) 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.
- gen_rebuild_idx.sql (ASCII text file – 2Kb)
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.
- gen_recompile.sql (ASCII text file - 2Kb)
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.!
- gen_validate.sql (ASCII text file - 15Kb) This
is a rather involved SQL*Plus script which uses the technique of
“SQL-generating-SQL” to help gather information to validate differences in
metadata and data between different schemas. There is copious explanation and
documentation within the script. I
use it for re-hosting projects or cloning operations, to ensure that
everything has been copied successfully from one schema to another.
- i.sql
(ASCII text - 2Kb) SQL*Plus script on the ALL_IND_COLUMNS view to display
all indexes associated with a table.
This script has recently been updated to provide information about
whether the index is bitmap, partitioned, and how it is partitioned.
- infinite_dual.sql (ASCII text - 2Kb) SQL*Plus
script to create a view named INFINITE_DUAL, which is based on data
generated from a pipelined table function named F_INFINITE_DUAL. The inspiration for this function was
the UNIX/BSD "yes" command, which would return the letter
"y" (or any phrase passed as a parameter) infinitely. Since Oracle contains a DUAL table of
exactly one row, then the INFINITE_DUAL view can be considered the other
extreme option, a row source that returns an infinite number of rows. Newly added on
19-January 2005!!!
- latches.sql (ASCII text file - 2Kb) 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!
- latchfree.sql (ASCII text file - 2Kb) 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.
- locks.sql (ASCII text file - 1Kb) SQL*Plus script to
query the V$LOCK view and provide a decoded report, identifying blockers
and waiters.
- logswitch.sql (ASCII text - 9Kb) 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.
- mts.sql (ASCII text file - 3Kb) 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.
- nondefparm.sql (ASCII text file – 2Kb) Query X$ tables to display any
initialization parameters not at their default values.
- parm.sql (ASCII text file - 1Kb) Query X$ tables to
display documented and undocumented initialization parameters.
- prevent1555_ddl.sql (ASCII text file –
9Kb) This PL/SQL package is
designed to help prevent the dreaded ORA-01555 error message. It does so by using “dummy” transactions
to set up “roadblocks” in a rollback segment, forcing that rollback
segment to grow rather than overwrite inactive transactions. The technique is discussed in the paper “Cats, Dogs, and ORA-01555” and it’s accompanying
presentation. The procedures in
this PL/SQL package are intended to be used with the UNIX korn-shell
script “prevent1555.sh”. For more usage information, see those
two entries.
- prevent1555.sh
(ASCII text file – 5Kb) This UNIX
Korn-shell script is more general-purpose, utilizing the PREVENT1555
package to place “roadblock” transactions in all of the non-SYSTEM
rollback segments that are online.
If this is done before the start of a query or transaction which
has failed from the ORA-01555 (“snapshot too old”) error in the past, then
this complete set of “roadblock” transactions should be put into place
first. They will cause any or all
rollback segments to grow rather than allow overwrite of already-committed
undo entries necessary for successful consistent reads. The shell-script will prompt for an
Oracle account and password, as well as the number of minutes that the
“roadblock” transactions should remain in place…
- privs.sql (ASCII text file – 2Kb) Queries data dictionary views to display
a full listing of all permissions and roles granted to an Oracle
account/user or a role.
- ptx.sql (ASCII text file - 1Kb) A useful little script
intended for Oracle8 databases for the purpose of monitoring the progress
of a parallel DML operation. It queries the V$TRANSACTION view and
displays the amount of work performed by each "parallel execution
slave process".
- px.sql (ASCII text file – 1Kb) SQL*Plus script to display information
regarding any PX sessions in Oracle8i and above.
- rman_chk_catalog.sql (ASCII text file –
12Kb) SQL*Plus script to create the
RMAN_CHK package inside a “recovery catalog” schema used by RMAN. This package contains the RECOVERABILITY
procedure which can be used to determine if RMAN is capable of restoring
and recovering a database to a specified point-in-time, according to the
information stored in its recovery catalog database. This packaged procedure can be executed
as a regularly scheduled report (an excellent idea!) or simply to verify
recoverability.
- rman_chk_nocatalog.sql (ASCII text file
– 11Kb) SQL*Plus script to create
the RMAN_CHK package inside the SYS schema of a target database being
backed-up by RMAN. This package can
supplement the use of the catalog-based package (above) or it can replace
the use of the catalog-based package, if you are using RMAN in “NOCATALOG”
mode. The intent of this package is
exactly the same as the catalog-based version of this package -- to
determine if RMAN is capable of restoring and recovering a database to a
specified point-in-time, according to the information stored in its
recovery catalog database. This
packaged procedure can be executed as a regularly scheduled report (an
excellent idea!) or simply to verify recoverability.
- rman_chk_test.sql (ASCII text file –
2Kb) SQL*Plus script to that can be
used to test either the catalog-based version of the RMAN_CHK package or
the controlfile-based “nocatalog” version (sources to both just above).
- rman_most_recent.sql (ASCII text file – 1Kb) SQL*Plus script that queries the RMAN
“recovery catalog” repository to provide a quick report on the most recent
backups for a database. New on 01-March 2008!!!
- schema_show_space.sql (ASCII text file -
2Kb) Wrapper procedure to call the SHOW_SPACE procedure (see “show_space.sql”, below) for all of the objects
and sub-objects in a schema.
- sesstime.sql (ASCII text file – 2Kb) SQL*Plus script to query the V$SESSION,
V$SESSTAT, and V$SESSION_EVENT views to display cumulative response-time
timing breakdowns (similar to that found in the first section of the
YAPP report from www.oraperf.com)
for individual sessions. This is an
excellent initial step for quick investigation of performance issues on
individual sessions. Please also
see the script “systime.sql” and most especially please utilize the
website www.oraperf.com and the
excellent YAPP reports. NOTE:
due to the use of “analytic SQL” features, this script can only be used
with v8.1.6 and higher.
- sesstime80.sql (ASCII text file – 2Kb) A modified version of “sesstime.sql”
(above) with the advanced Oracle8i analytic SQL removed, for use
with Oracle8 v8.0 and Oracle7 v7.x.
- shrink_files.sql (ASCII text file - 2Kb)
Report from data dictionary views to determine if datafiles can be
“shrunk” using ALTER DATABASE DATAFILE … RESIZE.
- show_space.sql (ASCII text file - 2Kb) Wrapper
procedure for the DBMS_SPACE procedures.
- sort_use.sql (ASCII text file - 2Kb) Query
V$SORT_USAGE to determine who is doing what in the TEMP tablespaces.
- space_usage.sql (ASCII text file – 6Kb) Stored procedure to act as a “container”
for the DBMS_SPACE packaged procedures.
Displays space utilitization within segments, whether tables,
indexes, partitioned, subpartitioned, or in freelist-managed or
bitmap-managed tablespaces. New on 08-January 2008!!!
- spc.sql (ASCII text file - 2Kb) Query to determine how
much space is being consumed by segments in datafiles and tempfiles,
including summations by tablespace and for the entire database.
- spcheck.sql (ASCII text file – 3Kb) SQL*Plus script
to display a quick status of DBMS_JOB jobs for STATSPACK. Intended to be executed from the
PERFSTAT schema.
- sphistory.sql (ASCII text file – 3Kb) SQL*Plus
script to display the “history” of resource consumption and execution
plans used by a specified SQL statement, using the detailed snapshot
information captured in STATSPACK.
This script is intended to augment the cumulative information found
in the standard “spreport.sql” or a derived YAPP report. It is also useful for use after a change
has been made, to demonstrate the impact (i.e. “before” vs “after”
effects). It is similar to the “sqlhistory.sql” script used with the AWR
repository…
- spparms.sql
(ASCII text file – 2Kb) SQL*Plus script to adjust the default SQL
statistics gathering thresholds higher than the defaults upon
installation.
- sppurpkg.sql (ASCII text file – 5Kb) SQL*Plus script SQL*Plus script
containing DDL commands to create the package SPPURPKG, intended for use
with STATSPACK from Oracle database versions 8.1.7 and above. Adapted from the "sppurge.sql"
script which is included with standard STATSPACK v8.1.7, it is easier to
use because it can be called automatically from the DBMS_JOB package
(instead of interactively as with "sppurge.sql") and it takes
only the number of days of STATSPACK data to retain (instead of prompting
for a begin/end range of SNAP_IDs, like "sppurge.sql"). After the package is created, then this
script will submit the procedure "SPPURPKG.RUN(14)" (i.e. purge
data older than 14 days) to run once per day. You may want to modify this, depending
on the volume of activity on the database(s) being monitored by STATSPACK
and the amount of storage you are prepared to allocate to the PERFSTAT
schema.
- sptrends.sql (ASCII text file – 3Kb) SQL*Plus
script to display daily and hourly trends for specified database
performance statistics, and highlight periods which deviate significantly
from average. The goal of this
report is to help identify periods of “interest” within the days, weeks,
or months of data stored in the STATS$SYSSTAT table of the STATSPACK
repository, to allow drilling down on those periods for more detailed
analysis.
- spviews80.sql (ASCII text file – 3Kb) SQL*Plus
script to add the V$TEMPFILE and V$BUFFER_POOL_STATISTICS view to an
Oracle8 v8.0.x database. Then, with
these views present, STATSPACK v8.1.7 can be loaded and used.
- sp_avg_redo_write_time.sql
(ASCII text file – 4Kb) SQL*Plus script to query the STATS$SYSSTAT table
in the STATSPACK repository to display the average write time for redo
logging, summarized first by day and then by hour, over a specified period
of time. Note: These queries are excellent examples of
the new “windowing SQL” analytical functions such as LAG…
- sp_buffer_busy_waits.sql (ASCII text
file – 4Kb) SQL*Plus script to query the segment statistics views of
Oracle9i STATSPACK to provide a listing of the segments (i.e. tables,
indexes, etc) which are causing the most “buffer busy waits”, summarized
by day and by hour of day. Buffer
busy waits are indicative of contention for the same database block in the
Oracle Buffer Cache. Note: These queries are excellent examples of
the new “windowing SQL” analytical functions such as RANK and LAG…
- sp_delta_views.sql (ASCII text file –
47Kb) SQL*Plus script containing
DDL to create views that automatically calculate the "delta" or
difference between adjacent samples of data gathered by the STATSPACK
package. Each of these views is
named the same as the corresponding tables in the STATSPACK repository,
except that the prefix of "STATS$" has been replaced by a prefix
of "DELTA$". These views
are extremely useful in using the STATSPACK repository like a data
warehouse of Oracle performance tuning information.
- sp_evtrends.sql (ASCII text file – 5Kb)
SQL*Plus script to display daily and hourly trends for specified “wait
events”, and highlight periods which deviate significantly from
average. The goal of this report is
to help identify periods of “interest” within the days, weeks, or months
of data stored in the STATS$SYSTEM_EVENT table of the STATSPACK
repository, to allow drilling down on those periods for more detailed
analysis.
- sp_gc_waits.sql (ASCII text file – 6Kb)
SQL*Plus script to query the segment statistics views of Oracle9i STATSPACK
to provide a listing of the segments (i.e. tables, indexes, etc) which are
causing the most “global cache waits”, summarized by day and by hour of
day. Global cache waits are
indicative of contention for the same block in a multi-instance RAC
environment. Note: These queries are excellent examples of
the new “windowing SQL” analytical functions such as RANK and LAG…
- sp_itl_waits.sql (ASCII text file – 4Kb)
SQL*Plus script to query the segment statistics views of Oracle9i
STATSPACK to provide a listing of the segments (i.e. tables, indexes, etc)
which are causing the most “ITL waits”, summarized by day and by hour of
day. ITL waits are indicative of
contention for the same block and can be relieved by raising the INITRANS
parameter on the segment. Note: These queries are excellent examples of
the new “windowing SQL” analytical functions such as RANK and LAG…
- sp_oica.sql (ASCII text file – 4Kb) SQL*Plus script
provide a recommended calculated value for the OPTIMIZER_INDEX_COST_ADJ
parameter, using information gathered from STATSPACK over time. Before considering setting this
parameter, please first consider leaving it at default and gathering
“system” statistics with the DBMS_STATS package first. Also, please read the white paper “Search for Intelligent Life in the
Cost-Based Optimizer” to better understand the purpose of this
parameter. It is not to be used to
“bias” the optimizer toward or away from using indexes, but rather simply
to accurately inform the optimizer about the costs and benefits of
considering indexes for execution plans.
- sp_parm_changes.sql (ASCII text file – 2Kb)
SQL*Plus script to query the STATS$PARAMETER table within the STATSPACK
repository to provide a report of changes to instance-level parameters
made over the specified period of time.
This provides something of an “audit trail” of parameter changes, to
confirm or contradict notes made in “init.ora” files or elsewhere. An excellent diagnostic tool for
debugging “mysterious” events.
- sp_row_lock_waits.sql (ASCII text file –
4Kb) SQL*Plus script to query the segment statistics views of Oracle9i STATSPACK
to provide a listing of the segments (i.e. tables, indexes, etc) which are
causing the most “row lock waits”, summarized by day and by hour of
day. Row-locks are generally
indicative of naïve or poor application design, as they indicate different
users contending for the same row of data.
Note: These queries
are excellent examples of the new “windowing SQL” analytical functions
such as RANK and LAG…
- sp_shutdown.sql (ASCII text file – 1Kb) SQL*Plus script containing DDL to create
a BEFORE SHUTDOWN database-event trigger, intended to call the
STATSPACK.SNAP packaged procedure one last time to flush any data stored
in the V$ views before it is lost forever.
- sp_systime_9i.sql (ASCII text file – 11Kb)
and sp_systime_8i.sql (ASCII text file –
11Kb) These SQL*Plus scripts query
the STATS$SYSTAT and STATS$SYSTEM_EVENT tables within the STATSPACK
repository to display cumulative response-time timing
breakdowns (similar to that found in the first section of the YAPP report
from www.oraperf.com) for the entire
database instance. These reports
both summarize first to a daily level and then to an hourly level. The report prompts for the number of
days to report upon. This is an
excellent initial step for quick investigation of performance issues on
the entire database instance.
- sqldups.sql (ASCII text file - 1Kb) Query against
the V$SQLAREA view to display all SQL statements which are exactly the
same in the first 75 characters of their text. The report only displays
those situations where there are more than 50 such copies. This situation
is likely (but not definitely) caused by not using "bind
variables" in the application…
- sqlhistory.sql (ASCII text file – 9Kb) SQL*Plus script to display the “history”
of resource consumption and execution plans used by a specified SQL statement,
using the detailed snapshot information captured in AWR. This script is intended to augment the
cumulative information found in the standard “awrsqrpt.sql”. It is useful for use after a change has
been made, to demonstrate the impact (i.e. “before” vs “after” effects),
and it is useful for detecting when something caused a SQL statement’s
execution plan to change. It is
similar to the “sphistory.sql” script used with
the STATSPACK repository… New on 17-November 2008!!!
- summarize_stats.sql (ASCII text file - 7Kb)
SQL*Plus script with DDL to create the PL/SQL stored procedure
SUMMARIZE_STATS, which can be used to aggregate partition-level statistics
to the “global” level for a table and it’s associated local indexes. Also, the procedure can aggregate
sub-partition-level statistics to the partition-level, prior to
aggregating the partition-level to the “global” level, if specified. New on
21-January 2008!!!
- sysevent.sql (ASCII text file - 3Kb) SQL*Plus
script to use to detect overall database contention by querying the
V$SYSTEM_EVENT view. This script is one of the first to use when trying to
determine the overall sickness or health of a database instance. This
script depends on having the configuration parameter TIMED_STATISTICS set
to TRUE.
- systime.sql (ASCII text file – 2Kb) SQL*Plus script to query the V$SYSTAT,
and V$SYSTEM_EVENT views to display cumulative response-time
timing breakdowns (similar to that found in the first section of the
YAPP report from www.oraperf.com)
for the entire database instance.
This is an excellent initial step for quick investigation of
performance issues on the entire database instance. Of course, if you can generate a
BSTAT/ESTAT or a STATSPACK report and have it post-processed on www.oraperf.com, that would be better,
but this script gives a good start along the same investigative path. .
Please also see the script “systime.sql” and most especially please
utilize the website www.oraperf.com
and the excellent YAPP reports. NOTE:
due to the use of “analytic SQL” features, this script can only be used
with v8.1.6 and higher.
- systime80.sql (ASCII text file – 2Kb) A modified version of “systime.sql”
(above) with the advanced Oracle8i analytic SQL removed, for use
with Oracle8 v8.0 and Oracle7 v7.x.
- sw.sql (ASCII text file - 2Kb) SQL*Plus script which
provides detailed information about sessions as they experience the
specified wait-events. The script prompts for all or part of a wait-event
name, and displays detailed current information from the
V$SESSION_WAIT view as well as cumulative summary
information from the V$SESSION_EVENT view.
- top_stmt2.sql (ASCII text - 6Kb) A newer version
of the TOP_STMT stored procedure, which is capable of summarizing SQL
statements performance information in the Shared SQL Area even when
uncacheable SQL statements are being issued due to the lack of "bind
variables". The TOP_STMT2 stored procedure is superior to TOP_STMT
for most situations. The SQL*Plus script run_top_stmt2.sql
(ASCII text - 1Kb) is also available to run TOP_STMT2. If you don’t want to create a stored
procedure, then run the script as an anonymous PL/SQL block using “temp_top_stmt2.sql” (ASCII text – 6Kb).
- top_stmt4_10g.sql (ASCII text – 15Kb), top_stmt4_9i.sql (ASCII text - 15Kb), and top_stmt4_8i.sql (ASCII text – 13Kb): This stored
procedure summarizes SQL execution information in either all versions of
the STATSPACK repository. The major
difference is that the 8i version does not include CPU time and elapsed
time information. This is intended
to be created within the PERFSTAT schema. The SQL*Plus script run_top_stmt4.sql (ASCII text - 1Kb) is also
available to run the TOP_STMT4 stored procedure. Updated on
26-March 2008!!!
- top_stmt5_10g.sql (ASCII text - 15Kb): The
newest version of the TOP_STMT series of stored procedures. This version summarizes SQL execution
information in the 10g version of the AWR repository. This version should be created within a
schema with directly-granted access to the DBA_HIST_ views. The SQL*Plus
script run_top_stmt5.sql (ASCII text -
1Kb) is also available to run the TOP_STMT5 stored procedure. New on
26-March 2008!!!
- trclvl12.ddl (ASCII text - 2Kb) SQL*Plus script to
create the TRCLVL12 stored procedure, which uses the DBMS_SYSTEM.SET_EV
procedure to enable SQL Tracing at level 12 (i.e. display "bind
variable" values as well as "wait event" information). Use the SQL*Plus script trclvl12.sql (ASCII text - 2Kb) to make using the
TRCLVL12 stored procedure a little more "user-friendly"…
- tracetrg.sql (ASCII text – 2Kb) SQL*Plus script containing DDL to create
a database-level AFTER LOGON event trigger to enable SQL Trace when a
specific Oracle account/user connects to the database. The feature of AFTER LOGON event
triggers are available only with Oracle8i and above.
- unload.sql (ASCII text file - 13Kb) Gary Dodge's
(gdodge@us.oracle.com) way-cool
answer to the longest-standing gap in the Oracle product suite - the lack
of a "SQL*UnLoader" utility to complement the
"SQL*Loader" utility. This is a SQL*Plus script which queries
the data dictionary in order to produce 1) another SQL*Plus script to be
used to dump a table to a "spool" file and 2) a SQL*Loader
control file to be used to load the data from the previously generated
"spool" file
- unparm.sql (ASCII text file - 1KB) SQL*Plus script
which queries X$ tables to detect whether any "undocumented"
initialization parameters have been set for a database. Generally, the
only benign undocumented parameter (i.e. begins with an
"under-score" character) is "_TRACE_FILES_PUBLIC". All
others could be cause for concern in a trouble-shooting situation…
- user_source.sql
(ASCII text file - 1Kb) SQL*Plus script to query the USER_SOURCE view and
extract PL/SQL source into a text file, and then launch the default text
editor on the file.
- w.sql
(ASCII text file - 1Kb) A very simple SQL*Plus script to quickly view
summary wait-event information from the V$SESSSION_WAIT view. The purpose
is to get an initial high-level summary of the current state of the
database instance. From here, you might want to "drill down"
using the "sw.sql" script (described above)
to get more detailed information. If the wait-events of interest are
"latch free", you may want to "drill down" using the
"latchfree.sql" script (described
above). If the wait-events of interest are "write complete
waits", "free buffer waits", and "buffer busy
waits", you may want to "drill down" using the "wts.sql" script (described below).
- who_is_using.sql (ASCII text file – 3Kb) SQL*Plus script downloadable from Oracle MetaLink note #1054939.6. This wrapped PL/SQL procedure can be used
to display who is using a particular PL/SQL procedure, function, or
package. New on 17-Nov 2008!!!
- wts.sql (ASCII text file - 2Kb) SQL*Plus script to
query detailed information about sessions which are waiting on the related
wait-events "write complete waits", "free buffer
waits", and "buffer busy waits". The first two wait-events
indicate that the DBWR process is "not keeping up", and the
third one simply indicates contention for database block buffers within
the Buffer Cache of the Oracle SGA. If caused by INSERT statements, this
can be resolved by adding FREELISTS to the relevant table(s) and related
index(es). If caused by UPDATE and DELETE statements, the only resolution
is typically application-code rewrite or redesign. This is because UPDATE
and DELETE statements have WHERE clauses that are determined by the
application code…
UNIX
shell scripts (complete list, in alphabetic order by name):
- check_10gr2rac.sh
(ASCII text file – 54Kb) UNIX Korn-shell
script which can be used to gather trouble-shooting information about
Oracle10gR2 RAC environments, including the CRS layer. Essentially, this script calls just
about every possible permutation of the “cluvfy”, “crsctl”, and “srvctl”
commands. If the script is running
in “verbose” mode, it displays the calling syntax and output of each
command. If it is running in
“terse” or “non-verbose” mode, then it only displays output when a command
returns a non-success status. New on 07-Apr 2008!!!
- chk_db_up.sh
(ASCII text file – 4Kb) UNIX
Korn-shell script which is intended to called from the UNIX cron utility
very often (i.e. once per minute, once per 5 minutes, etc) every day. It sends email to the configured email
accounts (note the need for files named “.dbapage” and “.dbamail” in the
“$HOME” directory of the owning UNIX account) when it detects that the
specified Oracle database instance is not available.
- chk_df.sh (ASCII text file – 10Kb) UNIX Korn-shell script which is intended
to called from the UNIX cron utility fairly often (i.e. once per
couple hours or so) every day. It
sends email to the configured email accounts (note the need for files
named “.dbapage” and “.dbamail” in the “$HOME” directory of the owning
UNIX account) when it detects that any of the UNIX file-systems upon which
the specified database instance depends has less than 10% total space
available.
- chk_idx_rebuild.sh (ASCII text file – 6Kb) UNIX Korn-shell script which is intended
to called from the UNIX cron utility perhaps once per day or a
couple times per week. It sends
email to the configured email accounts (note the need for files named
“.dbapage” and “.dbamail” in the “$HOME” directory of the owning UNIX
account) when it detects that any indexes, index partitions, or index
subpartitions are in an UNUSABLE state.
- chk_obj_recompile.sh (ASCII text file –
6Kb) UNIX Korn-shell script which
is intended to called from the UNIX cron utility perhaps once per
day or a couple times per week. It
sends email to the configured email accounts (note the need for files
named “.dbapage” and “.dbamail” in the “$HOME” directory of the owning
UNIX account) when it detects that any invalid compiled database objects,
such as database views or compiled programs (i.e. packages, procedures,
functions, triggers) are in INVALID status.
- chk_oerr.sh (ASCII text file – 11Kb) UNIX Korn-shell script which is intended
to called from the UNIX cron perhaps once per day or a couple times
per week. It sends email to the
configured email accounts (note the need for files named “.dbapage” and
“.dbamail” in the “$HOME” directory of the owning UNIX account) when it
detects newly-logged ORA- error messages in the “alert.log” file or in the
“.trc” files in the BACKGROUND_DUMP_DEST or USER_DUMP_DEST
directories. Also reports newly
generated “core” files in the CORE_DUMP_DEST.
- chk_spc.sh(ASCII text file – 7Kb) UNIX Korn-shell script which is intended
to called from the UNIX cron utility fairly often (i.e. once per
couple hours or so) every day. It
sends email to the configured email accounts (note the need for files
named “.dbapage” and “.dbamail” in the “$HOME” directory of the owning
UNIX account) when it detects any tablespaces that are more than 90% full
(threshold is configurable, of course).
- clonedb.sh (ASCII text file - 13Kb) UNIX Korn-shell
script which can be used to copy a subset (i.e. one specified tablespace
plus the SYSTEM tablespace plus any tablespaces with rollback segments)
from one database to a new "clone" database. This shell script
utilizes the new CONNECT INTERNAL feature of SQL*Plus, which is present
from Oracle8 v8.0.5 and forward. Using the technique of
"SQL-generating-SQL", this script generates a complete set of
SQL scripts to be used in creating a "clone" database. Along the
way, the script also serves as terrific documentation of the steps
necessary to accomplish, one of the most useful feats in any DBA's bag of
tricks. Also requires the SQL script "clonedb_arch.sql"
(ASCII text file - 2Kb).
- fileprobe.sh (ASCII text file – 16Kb) UNIX Korn-shell script which performs
checks on the permissions of files in the Oracle software distribution
(i.e. ORACLE_HOME), administration files (i.e. ORACLE_BASE/admin), and
database files (i.e. control files, online redo log files, archived redo
log files, data files, and temp files).
- iostat.sh (ASCII text file - 10Kb) UNIX Korn-shell
script which summarizes information from the UNIX “iostat” utility
to display I/O performance statistics from the UNIX device perspective. Written and tested in Solaris 2.8.
- oramem.sh (ASCII text file - 6Kb) UNIX Korn-shell
script which uses the Solaris “pmap” utility to display totals of
virtual memory consumed by server processes in an Oracle instance. Written and tested in Solaris 2.8; probably works in Solaris 2.9 and 2.10,
but not yet tested.
- oraprobe.sh (ASCII text file – 10Kb) UNIX Korn-shell script which calls
SQL*Plus to probe an Oracle database for possible use of default
or guess-able passwords for the standard accounts created during the
installation of Oracle products.
The script works in two passes:
the first pass uses the fixed list of standard accounts with
guess-able passwords; the
second pass is used whenever the first pass works. The second pass acquires a list of the
accounts existing in the ALL_USERS data dictionary view and then tries the
account name as the password. In a future
version of the script, I’ll include guess-able passwords here also,
as well as guess-able variants (such as common number substitution
for letters, etc). The intent
of this script is not to cause harm or to encourage mayhem, but rather to
convince DBAs and database application designers of the importance of strong
password protection, which have been built into the RDBMS since v8.0.
- oratop.sh (ASCII text file - 6Kb) UNIX Korn-shell
script to serve as a "top 10" listing of the most CPU-intensive
Oracle "dedicated server" processes for the database instance
indicated by the current setting of $ORACLE_SID. Written for HP-UX;
probably adaptable to other versions of UNIX… J
- sp_vmstat.sh (ASCII text file – 10Kb) UNIX Korn-shell script which calls the
UNIX vmstat utility on Solaris, Linux, and HP-UX, saving the
results to a custom-built table (CSTATS$VMSTAT) in the PERFSTAT
schema. The script is intended to
be run from the UNIX cron utility, and the frequency and duration
parameters to vmstat are also parameters to the script. Personally, I like to set the frequency
parameter to “4” and the duration parameter to “900” seconds (i.e.
15 mins), then schedule cron to call the script once per hour. Some very interesting system statistics
can be gathered this way, and correlated with database information
gathered by STATSPACK.
- stdby_applylogs.sh (ASCII text file –
8Kb) UNIX Korn-shell script which
simulates the “managed recovery process” (i.e. MRPn) process in an Oracle DataGuard physical standby database
instance – it scans for newly-arrived archived redo log files from the
primary database instance and applies them to the physical standby
instance. This script is intended
for use in an Oracle “Standard Edition” database to maintain a “physical
standby database” without the use of Oracle “Enterprise Edition” and the
DataGuard feature. New on 19-March 2008!!!
- stdby_chk.sh (ASCII text file – 9Kb) UNIX Korn-shell script which queries the
mounted un-opened “physical standby” database instance and the opened
“primary” database instance to check to see if the standby is lagging more
than two archived redo log files behind the primary. This script is intended for use in an
Oracle “Standard Edition” database to maintain a “physical standby
database” without the use of Oracle “Enterprise Edition” and the DataGuard
feature. New on 19-March 2008!!!
- stdby_init.sh (ASCII text file – 15Kb) UNIX Korn-shell script which uses Oracle
RMAN to initialize a “physical standby” database instance. This script is intended for use in an
Oracle “Standard Edition” database to maintain a “physical standby
database” without the use of Oracle “Enterprise Edition” and the DataGuard
feature. New on 19-March 2008!!!
- `stdby_ship.sh (ASCII text file – 12Kb) UNIX Korn-shell script which uses either
“scp” or “sftp” to copy newly-archived redo log files from the “primary”
database instance to the “physical standby” database instance. This script is intended for use in an
Oracle “Standard Edition” database to maintain a “physical standby
database” without the use of Oracle “Enterprise Edition” and the DataGuard
feature. New on 19-March 2008!!!
- tnsprobe.sh (ASCII text file – 6Kb) UNIX Korn-shell script which calls tnsping
and lsnrctl to probe a database server host machine for any
ports (in the range from 1025 to 65536) with Oracle TNS Listeners on
them. If one is found, then the
Oracle lsnrctl services command is issued to find if the topology
of databases served by the TNS Listener can be displayed, and also to
determine if the TNS Listener is passworded. If the lsnrctl services command
succeeds, then its output is parsed and the “oraprobe.sh”
shell script (see above) is called to probe the Oracle database for
accounts with guess-able passwords.
The intent of this script is not to cause harm or to
encourage mayhem, but rather to convince DBAs and database application
designers of the importance of strong password protection, which
have been built into the RDBMS since v8.0.
- traceon.sh (ASCII text file - 4Kb) UNIX Korn-shell
script which uses the Oracle Server Manager ORADEBUG facility to enable
SQL Tracing at level 12 (i.e. display "bind variable" values as
well as "wait event" information) for an Oracle session.
- traceoff.sh (ASCII text file - 4Kb) UNIX Korn-shell
script to disable SQL Tracing using the Server Manager ORADEBUG facility.
- wakepmon.sh (ASCII text file - 5Kb) UNIX Korn-shell
script that uses the ORADEBUG WAKEUP command to "wake up" the
PMON background process, in the event that it doesn't seem to be doing
it's job. Generally something like this is not needed, but in some limited
cases it might be helpful…