SQL Script Repository
Please download scripts from this SQL Script Repository – Upgrade Your Database – NOW!.
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 |
