Scripts

SQL Script Repository - Upgrade Your Database - NOW!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.

Last modified: Jan 15, 2024

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
19c and others check_patches_size.sql
Checks how much space is consumed by the patch rollback zip file inside the SYSTEM tablespace
19c and others unload_blob_from_table.sql
Unload BLOB data from within a table column, and store individual files in the file system

 

Share this: