Scripts & Questions

This page is both my page for all the links to useful scripts AND for any questions you want to post regarding topics you find on my blog.  Always remember:

  1. I’m not Oracle Support and actually work for Microsoft now in Analytics and AI. I can’t interfere with a solution that’s being worked on with these great folks in the support team.
  2. I’m not licensing or sales.  Trust me-  you don’t want to ask me deep licensing questions or costs.  I know nothing in regards to either… 🙂

So go for it, post a questions at the bottom of the page and Discus will email and let me know!

New Stuff

GitHub Repository Links

Education Solution, including Automation for Deployment of databases, Analysis Services and ADF – GitHub for EDU Solution

Insights in a Day Solution, including Automation for databases, Analysis Services and ADF- Github for Insights for a Day

KSCOPE 2017 Hackathon, including all code to Raspberry Pi Project

Apple Scripts-  OmniFocus Weekly Status Report

DBAKevlar GitHub

Delphix Scripting Resources

Delphix GitHub Links

Delphix How To Videos


This is a collection of scripts that I’ve found useful over the years.  Please feel free to utilize any you would like and hopefully they will serve others as well as they have served me.  I have tried to ensure that if an author of the script is known, it is shown in the script.  You will see some that I’ve had for so long, I have no idea where the original source is from or even if it is in the original form, (vs. me having personalized it as I’ve gone along…) I have a tendency to utilize very generic, simple scripts and edit them to the needs of the specific situation. If you have a request for one of the ones here, just let me know!

AWR Warehouse Scripts, (NEW!!)

AWRW Top IO Waits


AWRW Analyze SQLID Data

AWRW Multiple Hash Plans for SQLID

CPU History by DBID

High Level AWR Data by DBID and Instance

Resource Allocation Scripts

Misc. Scripts

Dynamically Collecting Modified Object Stats
Check Rman Async
Check Mview Lag
Check DB Object Cache Info
Check for Data Dictionary Corruption
Set Sequence Cache
User Privilege Report
Check Ora Patch
DB Size
Datafile Space
Dynamic Index Monitoring Script
Check Index Monitoring
User Last Login
Locate Historical SQL_ID Info

RMAN Hot Backup Script for Easy Restores

RAC Schtuff
Global Cache Blocks Lost
Global Cache Corrupt Blocks

Long Operations
Library Cache Stats
Data Dictionary Hit
Duplicate SQL Search
Check Redo Size
Check Full Scans
Check Execs in Memory
Latch Check
Enqueue Waits
The Mutex Step Thru
Max Cursors
Redo Waits
AAS Expensive
AAS SQL Hist Stats
AAS High Var
AAS Exact
AAS Per Hour
AWR Per Minute
ASH Per Minute
Waits Aggregated

What’s in Undo
Find UNDO Blocks by SQL_ID
Dynamic Kill Sessions Script
Find Program from SQL_ID(or Hash)


Check Optimal Object Scans
Exadata Optimal Reads
ASM Cell Node Check

ASM Disk Info
View ASM Files

SQL Server
Check Process SQL
SQL Tuning Script by DB
Check Memory
Move SQL Server Logins
Query Waits
SQL Performance Stats
Dump the Stack Trace Data

I’m just going to point you to Glenn Berry’s page of DMV scripts, as they’re awesome!

ASH Queries, (NEW!)

Whats in the Ash Buffer
ASH CPU by User
ASH IO Waits
ASH Per Minute
ASH Wait for CPU
Tyler Muth ASH Resource Query

Script to email HTML AWR Report

AWR Streamlined Repository

I’ve been asked a couple times for this, so searched it out and added it here.  This is the DDL for a repository table to store data remotely/locally for the high level hourly data requests based off some wonderful queries from Karl Arao that we would email back and forth, discussing how we could make it into something *more*… 🙂


Hourly Report of AWR Data, identified by DB_ID, can then be used to report or fill into DDL created in table above.

Shell Script to load data

Explain Plans/Execution Plans

Get Explain Plans

Valuable ASH URLS

›Karl Arao:

›Tyler Muth:

›Kyle Hailey, John Beresniewicz, Graham Wood:


Questions and Recommendations for Blog Posts

29 thoughts on “Scripts & Questions

  • Pingback: Redgate Webinar, “ASH and AWR Performance Data” | DBA Kevlar

  • Pingback: Performance Tuning with ASH and AWR Data – All Things Oracle

  • Pingback: AWR Warehouse Webinar from ODTUG | DBA Kevlar

  • Pingback: AWR Warehouse, Status | DBA Kevlar

  • Pingback: AWR Warehouse, Status - Oracle - Oracle - Toad World

  • February 15, 2015 at 5:13 pm

    it’s great, thank u

  • March 23, 2015 at 7:37 am

    is there any way to check “the query volume” on a table in production at specific interval(Say 9pm to 10pm)?

  • March 23, 2015 at 8:17 am

    You can look at IO, Buffer Busy Waits, etc. on a specific object for a given hour interval through snapshots, but I’m not sure what your term, “query volume” would signify. It’s not a term that is used for a specific wait event, etc.?

  • February 18, 2016 at 6:32 am

    Very kind and very nice to share such scripts as well as those articles. Grateful for that.

  • June 6, 2016 at 4:45 pm

    I am running into temp space errors when trying to run a sql query. How can I estimate how much temp space would I need for a query that is currently running?

  • June 6, 2016 at 7:25 pm

    There are a couple different ways to do this, but I would start with ensuring the stats are up to date for the objects involved and then create an explain plan for the statement, then
    Inspect the temp usage displayed for the explain plan.

    If the statement has already run, you can look at the data in the AWR:

  • June 7, 2016 at 6:41 pm

    Thank you. I will try this and let you know, am not sure if I have privileges to query the dbms_xplan.Btw, am a developer and very much interested in learning about performance tuning, oem etc. What’s a good book you would recommend if I’ve to understand looking at oem and understanding the awr reports etc, understanding explain plans etc?

  • June 18, 2016 at 6:23 am

    Is there any way to capture the dbsnmp “account lock” errors in oem 12c and create corrective action for that ..We have a big environment and a lot of time databases show as down only because of this .we cannot keep the same password forever due to the policies.

  • Pingback: dba kevlar – ORACLE BOSS

  • February 16, 2017 at 4:06 pm

    Need help in fixing Error while tying to register EBS12.2.5 on OEM

    Error Message:

    EBSTargetDiscovererR122 WLS Admin Server not running on Patch node. Starting it.
    EBSTargetDiscovererR122 Starting WLS Admin Server using /XXXX/app/oracle/fs2/EBSapps/appl/ad/12.0.0/patch/115/bin/
    EBSTargetDiscovererR122 Failed to start Admin Server on Patch node.
    Discovery Error: oracle.apps.fnd.oam.em.sdk.targetData.discovery.AppsDiscoveryException: Error while attempting to discover EBS on : XXXX( oracle_database )


    All the services on both file system is already up and running…
    not sure why it is tying to restart again and erroring out..

  • February 17, 2017 at 10:37 am

    I already did the agent restart but no luck…

    still facing the same issue..

  • April 4, 2017 at 6:08 pm

    I was wondering if you might have any insight into the syntax of an emcli command below.

    I need to regularly update the SYS password for the DB per our security policy. The sys account password is set directly to the DB from Cyberark. We’ve written a Bash shell script to then copy the password file to the Standby. I am trying to set the monitoring password for the Standby DB’s in EM Grid control for around 100 DBs after copying the password file over to the Standby DB server. I’m running the emcli commands as the SYSMAN account for Grid OMS.

    The questions that I have are:
    Does this update have to be run from the local standby DB server or can it be run from EM Grid Control OMS server? I’m hoping to run this from the OMS server rather than all the individual DB servers.

    The syntax I’ve worked out so far is the following …

    emcli set_monitoring_credential -target_name=DR_SID -target_type=oracle_database -set_name=DBCredsSYSDBA -cred_type=DBCreds -test -attributes=”DBUserName:SYS;DBPassword:password;DBRole:SYSDBA”

    and I’ve also tried with the set_name=DBCredsMonitoring and both failed.

    The error I’m getting is …
    Error:Invalid credential type for set name.Credential set not found for target type , Setname DBCredsSYSDBA

    Any input would be appreciated.



  • April 6, 2017 at 3:56 pm

    On further research, I believe that I’ve worked out the correct syntax and answered my own question. The syntax …
    emcli set_monitoring_credential -target_name=’DR_SID’ -target_type=’oracle_database’ -set_name=’DBCredsSYSDBA’ -cred_type=’DB_Creds’ -attributes=’DBUserName:sys;DBPassword:xxxxxxxxx;DBRole:SYSDBA’

    However, the command runs without any response back to the terminal – ie, no command processed type reply.

    I have even tried logging into emcli with the -force option …
    emcli login -username=sysman -password=xxx -force

    and repeated the set_monitoring_credential command. It also fails. I may be running into the Bug 21367784.

    I’ll retest this again in the future.

  • April 12, 2017 at 1:13 pm

    Windows Server 2012 R2 Standard: Clean install of Oracle DB
    12c, Release 2 ( as we prepare to load OEM 13c.

    Found the wonderful templates for OEM 13c; however, they are
    for Oracle 12.1. 🙁

    I could not find OEM 13c Template for DB

    Q: Have you found
    templates for OEM13c and DB 12.2?

    If not, I am searching for values to use as found in the
    templates for 12.1


  • April 12, 2017 at 1:22 pm

    Sorry, haven’t see a template built for this yet, but its common to have a delay from the time the database release goes GA and the time a template’s been created. I’d stay tuned and soon enough they’ll have it!

  • April 12, 2017 at 1:23 pm

    I’d submit an SR to EM support. There are some changes in EBS support for 13c, so see if there is a known issue or a patch.

  • April 12, 2017 at 1:24 pm

    Look into creating a metric extension to check and on trigger, submit the corrective action.

  • May 1, 2017 at 9:50 am


    I am wondering if you have any leads on any one that has previously integrated OEM 13c (or even before) with the vFire Core (VM Service Manager) for the purpose of ticketing. Most of the documents and information I find is for OEM managing VMware virtual machines not for connecting with the Service Manager. I have looked at some documentation for building connectors for OEM and I have glanced at the API User Guide for VMware Service Manager. They seem to have two totally different approaches so I am not sure they are even compatible.

  • June 23, 2017 at 12:10 pm

    If anyone stumbles here and looks for a solution to the same problem: No templates for 13c HOWEVER, there is a fix:

    Create an empty 12.2 (or your version) database to be used as OEM 13c repository.

    Use Database Configuration Assistant. Choose “create a database” , “Advanced Configuration”, “Single Instance database”, then choose the “GENERAL PURPOSE or TRANSACTION PROCESSING” Template.

    So far, been working just right for me.

  • June 28, 2017 at 1:40 pm

    Would love a link, thanks!

  • November 16, 2017 at 12:59 pm

    I am having issues with the alerts that I am receiving from OEM13c especially related to – Agent is unable to communicate with the OMS. (REASON = Agent is Unreachable (REASON : Agent to OMS Communication is broken. Failure connecting to https://xyz:4903/empbs/upload and it clears immediately in couple of seconds. Can you please let me know how to fix this error. I know this issue occurs when the agent is unable to reach the OMS but because of this error I am receiving other alerts like “The current status of the target is Up” especially related to the databases. Can you please let me know if there is a work around or a patch that needs to be applied to fix this issue

    Thank you

Comments are closed.