SQL Script Repository
Feel free to download and use the below scripts from my SQL Script Repository – Upgrade Your Database – NOW!.
Scripts are used in various blog posts to monitor or diagnose things in Oracle databases. Click on the script’s name below to download. If you find any issues, please report any issues to me as well via the comment option.
| 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 |
| 19c and others |
spm_load_all.sql |
Load all plans from a given STS into SQL Plan Management |
| 19c and others | sta_awr.sql |
Create and execute a SQL Tuning Advisor task on a given SQL Tuning Set from AWR |
| 19c and others | sta_cc.sql |
Create and execute a SQL Tuning Advisor task on a given SQL Tuning Set captures from Cursor Cashe |
| 19c and others | export_sts_stagingtable.sql |
Pack an existing SQL Tuning Set into a staging table and export it within one script |
| 19c and others | import_sts_stagingtable.sql |
Import a SQL Tuning Set and unpack it into a staging table with one single script |
