Find active transactions in oracle database Script can be used to find the active transactions. col name format a10 col username format a8 col osuser format a8 col start_time format a17 …

 Script to monitor the ASM disk usage in Oracle The following disk gives you an idea of the Total size, used size, and percentage of space used. SET LINESIZE 150 …

Monitor lag in standby database using dgmgrl Script to monitor lag in standby database and send mail. #!/bin/bash export ORACLE_HOME=/u02/app/oracle/product/12.1.0/dbhome_1 export ORACLE_SID=primdb export PATH=$ORACLE_HOME/bin:$PATH echo -e “show database stydb”|${ORACLE_HOME}/bin/dgmgrl sys/orcl1234 …

Monitor parallel queries running in Oracle Database Query to monitor currently running queries with parallel option: select s.inst_id, decode(px.qcinst_id,NULL,s.username, ‘ – ‘||lower(substr(s.program,length(s.program)-4,4) ) ) “Username”, decode(px.qcinst_id,NULL, ‘QC’, ‘(Slave)’) “QC/Slave” , …

Check execution time and plan for SQL ID from the history table in Oracle dba_hist_active_sess_history table is used to fetch the information about SQL id set linesize 150 col exec_start …

Check the Memory usage used by Oracle from Operating System Check the SGA_TARGET and PGA_TARGET size : SQL> select sum(value)/1024/1024/1024 from v$parameter where name in (‘sga_target’,’pga_aggregate_target’); SUM(VALUE)/1024/1024/1024 ————————- 1.5 If …

 Check maximum query duration in Oracle for undo retention setting Check the maximum query duration in Oracle: SQL> select max(maxquerylen) from v$UNDOSTAT; SQL> select max(maxquerylen) from DBA_HIST_UNDOSTAT; Set the undo …

 Steps to increase the size of SGA memory in Oracle Check the SGA parameter which show the size of SGA allocated in Oracle SQL> show parameter sga_ NAME TYPE VALUE …