SQL Script Repository

 Please  download scripts from this SQL Script Repository

The scripts get used in various blog posts to monitor or diagnose things in Oracle databases. Click on the script’s name below to download the script. And please report any issues to me as well.

DB Version	Script	Purpose
19c	check_patches_19.sql	Check applied RUs and RURs with CDB_REGISTRY_SQLPATCH
18c	check_patches_18.sql	Check applied RUs and RURs with CDB_REGISTRY_SQLPATCH
12.1, 12.2	check_patches.sql	Monitor applied PSUs, BPs, RUs, RURs with DBA_REGISTRY_SQLPATCH
12.1, 12.2, 18c, 19c	check_components.sql	Checks all installed components CDB-wide with DBA_REGISTRY
12.1, 12.2, 18c, 19c	check_inv_objs.sql	Checks all invalid objects CDB-wide
11.2, 12.1, 12.2, 18c, 19c	capture_awr.sql	Capture statements from AWR – uses the oldest and newest available snapshot by default and limits to 5000 statements by elapsed time.
11.2, 12.1, 12.2, 18c, 19c	capture_cc.sql	Capture statements from Cursor Cache – uses a conservative test setting for 30 seconds only polling the CC every 5 seconds – needs to be adjusted for live systems.
19c and below	spa_cpu.sql	Run a full SQL Performance Analyzer task on captured SQL Statements – compare on CPU
19c and below	spa_buffer.sql	Run a full SQL Performance Analyzer task on capture SQL Statements – compare on buffer gets
19c and below	spa_elapsed.sql	Run a full SQL Performance Analyzer task on capture SQL Statements – compare on elapsed time
19c and below	spa_report_cpu.sql	Generate a regressed report comparing two workloads based on CPU
19c and below	spa_report_buffer.sql	Generate a regressed report comparing two workloads based on buffer gets
19c and below	spa_report_elapsed.sql	Generate a regressed report comparing two workloads based on elapsed time