Scripts

Content usage subject to Terms Of Use

Please direct any questions, comments or other feedback to scripts@optimaldba.com

Statspack Related Scripts

snap_matrix_daily - Displays a list of statspack snapshot ids (first) for every day in 2 hour increments.
snap_matrix_weekly - Displays a list of statspack snapshot ids for every day (first) in weekly format Sun - Sat
Top 15 SQL summary - Displays the Top 15 SQL statements in terms of time and resource consumption. This is the 10g compatible version.
Statistics Weekly Matrix - This script will display a matrix view of a statistic. The number of days to be displayed and the statistic name are passed as parameters. For the number of days, the report will start at the Sunday prior to the sysdate - <number of days>. For example, if you specifiy 30 days and that day falls on a Tuesday, the report starts with the Sunday of that week. As statistic names can contain spaces, you have to pass in the statistic name as <single quote><double quote><statistic name><double quote><single quote>.
Event Weekly Matrix - This script will display a matrix view of an event. The number of days to be displayed and the statistic name are passed as parameters. For the number of days, the report will start at the Sunday prior to the sysdate - <number of days>. For example, if you specifiy 30 days and that day falls on a Tuesday, the report starts with the Sunday of that week. As event names can contain spaces, you have to pass in the event name as <single quote><double quote><event name><double quote><single quote>.
Weekly Matrix Header - This is the script called by the Statistics and Events Weekly Matrix scripts. It sets up the report and spool file title.

Performance Diagnosis Scripts

Response Time Analysis - Reports all components of a current instances response time. This works for 10g only.
Session Event - A report of all Events for a session
Session IO - A report of Logical and Physical IO for a session
Session Stats - A report of current oimportant Statistics for a session
Session Stats2 - A report of all Statistics for a session
Session Wait 9i - A report of current Wait Event for a session
Session Wait 10g - A report of current Wait Event for a session
Session Wait History - A report of last 10 Wait Events for a session

DBMS_METADATA

These scripts use the supplied package DBMS_METADATA to extract ddl from a database.
USE at your own risk, dbms_metadata is rather buggy (see my presentation).

Extract schema menu - The menuing script that calls the other scripts.
Extract schema dependencies - Displays all the dependencies for a schema.
Extract schema ddl - Extracts ddl for a specified schema. This is not totally complete and you may need to add additional calls.
Extract schema ddl 10g - Extracts ddl for a specified schema for 10g.

Other

Privileges Report - Reports all privileges for users in a hierarchical manner. This works for 10g only and will not work if the username contains a '.'
DEMO Schema - Creates the DEMO schema. To increase/decrease the number of orders, change the v_max_orders value inside the cr_order_detail.sql script. You also need to modify the paths for cr_demo_ts to reflect your directory structure.