DATABASE SCRIPTS

How to get tablespace quota details of an user in oracle

This script reports the tablespace quota allocated for each database user and the amount of tablespace consumed by that user. TABLESPACE QUOTA DETAILS OF ALL THE USERS:

TABLESPAE QUOTA DETAILS FOR A PARTICULAR USER:

OUTPUT:

How to monitor parallel queries in oracle db

Use below query to monitor currently running queries with parallel threads.

Find pending distributed pending transactions in oracle

Below script will display information about the distributed pending transactions in oracle.

How to find execution history of an sql_id

Below script will display execution history of an sql_id from AWR. It will join dba_hist_sqlstat and dba_hist_sqlsnapshot table to get the required information.

Script to get cpu usage and wait event information in oracle database

Below script will give information about the CPU usage and wait events class information of every minute for last 2 hours. As this query uses gv$active_session_history, so make sure you have TUNING license pack of oracle, before using this.

OUTPUT WILL LOOK AS BELOW:

How to find cpu and memory information of oracle database server

Below script is useful in getting CPU, memory and core, socket information of a database server from SQL prompt. SCRIPT:

OUTPUT:

Find user commits per minute in oracle database

Below script is useful in getting user commit statistics information in the oracle database. user commits is the number of commits happening the database. It will be helpful in tracking the number of transactions in the database.  

OUTPUT:

STAT_PER_MIN -Number of commits per minutes, during that snap time    

Find the active transactions in oracle database

Below script can be used to find the active transactions in the oracle database.

Find sessions consuming lot of CPU

Use below query to find the sessions using a lot of CPU.

Get sid from ospid

Below is the script to find the respective sid of an server proccess id.

SEE ALSO – Get ospid from sid

Page 1 of 41234
Skip to toolbar