Oracle Production Support Scripts

Shell script to alert us when specified background process exceeds limit

3 years ago by Binnary Ajay KumarOracle Production Support Scripts

Hello Everyone, The below script will help us to find number of processes runs in the background and send us alerts based on warning and critical thresholds. Script : # ]$ cat /home/oracle/DBA/bin/running_process.sh

#!/bin/bash
# Syntax to run this script
# ./running_process.sh oracle 100 170
# ./running_process.sh PROCESS_NAME WARNING CRITICAL
export MAILIST='ajaykumar@ktexperts.com,
process_name=$1
_limit1=$2
_limit2=$3
_proc=`ps -ef | grep $process_name | wc -l`
if [ "$_proc" -ge "$_limit2" ]
 then
echo "Current Running $process_name Proccess is running too high $_proc " > /tmp/proc.log
cat /tmp/proc.log
cat /tmp/proc.log | mailx -s "${TODAY} - Current Running $process_name Proccess is running too high/Critical $_proc " ${MAILIST}
else
if [ "$_proc" -ge "$_limit1" ]
 then
echo "Current Running $process_name Proccess is running more than expected $_proc " > /tmp/proc.log
cat /tmp/proc.log
cat /tmp/proc.log | mailx -s "${TODAY} - Current Running $process_name Proccess is running more than expected $_proc  " ${MAILIST}
fi
fi

#!/bin/bash

# Syntax to run this script

# ./running_process.sh oracle 100 170

# ./running_process.sh PROCESS_NAME WARNING CRITICAL

export MAILIST='ajaykumar@ktexperts.com,

process_name=$1

_limit1=$2

_limit2=$3

_proc=`ps -ef | grep $process_name | wc -l`

if [ "$_proc" -ge "$_limit2" ]

then

echo "Current Running $process_name Proccess is running too high $_proc " > /tmp/proc.log

cat /tmp/proc.log

cat /tmp/proc.log | mailx -s "${TODAY} - Current Running $process_name Proccess is running too high/Critical $_proc " ${MAILIST}

else

if [ "$_proc" -ge "$_limit1" ]

then

echo "Current Running $process_name Proccess is running more than expected $_proc " > /tmp/proc.log

cat /tmp/proc.log

cat /tmp/proc.log | mailx -s "${TODAY} - Current Running $process_name Proccess is running more than expected $_proc " ${MAILIST}

Script execution : Syntax : ./running_process.sh <PROCESS_NAME> <WARNING> <CRITICAL> Here I… Read More

Protected: Oracle : Shell Script to Send email if RAC services not running

3 years ago by Rakesh SahuOracle Production Support Scripts

There is no excerpt because this is a protected post.

HOW TO CONNECT AND ACCESS ATP DATABASE

3 years ago by Venkata Sai PavanOracle DB Admin

HOW TO CONNECT AND ACCESS ATP DATABASE In this article, we will know “HOW TO CONNECT AND ACCESS ATP DATABASE” We can access ATP database through SQL developer for that we have to download wallet file of this particular database 1. Click on the ATP… Read More

Oracle : Shell Script to gather stats for Stale tables

3 years ago by Rakesh SahuOracle Production Support Scripts

Dear Readers, In this article, we wrote an automation job for Shell Script to gather stats for Stale tables. Note : 1) Please test the Shell script in Non-Prod before implementing it in Production. Script for gather stats for Stale tables

#!/bin/bash

function PreChks
{
ORACLE_HOME=/u01/app/oracle/product/12.1.0.2
if [[ ! -e $ORACLE_HOME ]]; then
        echo " "
        echo "Error :::  Given ORACLE_HOME :: $ORACLE_HOME does not exist!!! "
        echo " "
        exit;
fi

ORACLE_SID=mvmrp1
export PATH=$ORACLE_HOME/bin:$PATH:.
export ORACLE_BASE ORACLE_HOME PATH ORACLE_SID
LOGFILE=/tmp/main.log
echo "Task Start at `date` " > $LOGFILE
export MAIL_RECIPIENT_INFO='ktexperts@admin.com'
}

function get_objects
{
echo "#CHECK FOR DATABASE and Tables  STATUS##">>"$LOGFILE"
DB_OPEN_MODE=`sqlplus -s / as sysdba <<EOF
set pagesize 0 feedback off verify off heading off echo off
select open_mode from v\\$database;
EOF`
echo "$DB_OPEN_MODE">>"$LOGFILE"
if [[ $DB_OPEN_MODE = "READ WRITE" ]]
then
echo "Fetch Stale_Stats Data"
sqlplus -s / as sysdba <<EOF > /tmp/a.log
set pagesize 0 feedback off verify off heading off echo off
set linesize 300
select table_name, owner from dba_tab_statistics where stale_stats='YES' and owner in ('MVM1') order by table_name;
EOF
else
##DATABASE IS NOT OPEN##
  mail_subject="Database $INSTANCE is not open hence Gather Stats cant not Start...."
        echo " "
        echo "Database $INSTANCE is not open hence Gather Stats cant not Start...."
        echo " "
   mailx -s "${mail_subject}" ${MAIL_RECIPIENT_INFO}<<EOF
 " Database $INSTANCE is not open hence Gather Stats cant not Start...."
EOF
exit 0
fi
cat /tmp/a.log >> /tmp/main.log
}


function gather_stats
{
cat /tmp/a.log | while read p
do
tbl=`echo $p | awk '{ print $1 }'`
owner=`echo $p | awk '{ print $2 }'`
SQL=`echo "Exec dbms_stats.gather_table_stats('$owner','$tbl',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>'for all indexed columns size 1',CASCADE=>True,degree=> 4);"`
echo $SQL >> /tmp/main.log
sqlplus -s / as sysdba <<EOF >> /tmp/main.log
Exec dbms_stats.gather_table_stats('$owner','$tbl',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>'for all indexed columns size 1',CASCADE=>True,degree=> 4);
EOF
done
}


PreChks
get_objects
gather_stats
get_objects
mail_subject="mvmrp1 Stale Stats Report"
cat "$LOGFILE" | mailx -s "${mail_subject}" ${MAIL_RECIPIENT_INFO}

#!/bin/bash

function PreChks

{

ORACLE_HOME=/u01/app/oracle/product/12.1.0.2

if [[ ! -e $ORACLE_HOME ]]; then

echo " "

echo "Error ::: Given ORACLE_HOME :: $ORACLE_HOME does not exist!!! "

echo " "

exit;

ORACLE_SID=mvmrp1

export PATH=$ORACLE_HOME/bin:$PATH:.

export ORACLE_BASE ORACLE_HOME PATH ORACLE_SID

LOGFILE=/tmp/main.log

echo "Task Start at `date` " > $LOGFILE

export MAIL_RECIPIENT_INFO='ktexperts@admin.com'

}

function get_objects

{

echo "#CHECK FOR DATABASE and Tables STATUS##">>"$LOGFILE"

DB_OPEN_MODE=`sqlplus -s / as sysdba <<EOF

set pagesize 0 feedback off verify off heading off echo off

select open_mode from v\\$database;

EOF`

echo "$DB_OPEN_MODE">>"$LOGFILE"

if [[ $DB_OPEN_MODE = "READ WRITE" ]]

then

echo "Fetch Stale_Stats Data"

sqlplus -s / as sysdba <<EOF > /tmp/a.log

set pagesize 0 feedback off verify off heading off echo off

set linesize 300

select table_name, owner from dba_tab_statistics where stale_stats='YES' and owner in ('MVM1') order by table_name;

EOF

else

##DATABASE IS NOT OPEN##

mail_subject="Database $INSTANCE is not open hence Gather Stats cant not Start...."

echo " "

echo "Database $INSTANCE is not open hence Gather Stats cant not Start...."

echo " "

mailx -s "${mail_subject}" ${MAIL_RECIPIENT_INFO}<<EOF

" Database $INSTANCE is not open hence Gather Stats cant not Start...."

EOF

exit 0

cat /tmp/a.log >> /tmp/main.log

}

function gather_stats

{

cat /tmp/a.log | while read p

tbl=`echo $p | awk '{ print $1 }'`

owner=`echo $p | awk '{ print $2 }'`

SQL=`echo "Exec dbms_stats.gather_table_stats('$owner','$tbl',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>'for all indexed columns size 1',CASCADE=>True,degree=> 4);"`

echo $SQL >> /tmp/main.log

sqlplus -s / as sysdba <<EOF >> /tmp/main.log

Exec dbms_stats.gather_table_stats('$owner','$tbl',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>'for all indexed columns size 1',CASCADE=>True,degree=> 4);

EOF

done

}

PreChks

get_objects

gather_stats

get_objects

mail_subject="mvmrp1 Stale Stats Report"

cat "$LOGFILE" | mailx -s "${mail_subject}" ${MAIL_RECIPIENT_INFO}

Output :

##################

Task Start at Mon Jun 14 21:00:01 PDT 2021 #CHECK FOR DATABASE and Tables  STATUS## READ WRITE READ WRITE #CHECK FOR DATABASE and Tables  STATUS## READ WRITE READ WRITE
EMP					MVM1
DEPT				MVM1
SALGRADE			MVM1
Exec dbms_stats.gather_table_stats('MVM1','EMP',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>'for all indexed columns size 1',CASCADE=>True,degree=> 4);

PL/SQL procedure successfully completed.

Exec dbms_stats.gather_table_stats('MVM1','DEPT',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>'for all indexed columns size 1',CASCADE=>True,degree=> 4);

PL/SQL procedure successfully completed.

Exec dbms_stats.gather_table_stats('MVM1','SALGRADE',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>'for all indexed columns size 1',CASCADE=>True,degree=> 4);

PL/SQL procedure successfully completed.

#CHECK FOR DATABASE and Tables  STATUS## READ WRITE

##################

Task Start at Mon Jun 14 21:00:01 PDT 2021 #CHECK FOR DATABASE and Tables STATUS## READ WRITE READ WRITE #CHECK FOR DATABASE and Tables STATUS## READ WRITE READ WRITE

EMP MVM1

DEPT MVM1

SALGRADE MVM1

Exec dbms_stats.gather_table_stats('MVM1','EMP',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>'for all indexed columns size 1',CASCADE=>True,degree=> 4);

PL/SQL procedure successfully completed.

Exec dbms_stats.gather_table_stats('MVM1','DEPT',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>'for all indexed columns size 1',CASCADE=>True,degree=> 4);

PL/SQL procedure successfully completed.

Exec dbms_stats.gather_table_stats('MVM1','SALGRADE',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,METHOD_OPT=>'for all indexed columns size 1',CASCADE=>True,degree=> 4);

PL/SQL procedure successfully completed.

#CHECK FOR DATABASE and Tables STATUS## READ WRITE

Oracle Production Support Scripts

3 years ago by KTEXPERTS TEAMOracle Production Support Scripts

Dear Readers, Please check the below articles to get good knowledge on Oracle Production Support Scripts. SNO Article Name Article Link 1 Oracle : Check Partition Summary More than N Days (60 Days) https://www.ktexperts.com/oracle-partition-check-summary-more-than-n-days-60-days/ 2 UNDO segments not Releasing Space https://www.ktexperts.com/undo-segments-not-releasing-space/ 3 Oracle : How… Read More

Oracle : Check Partition Summary More than N Days (60 Days)

4 years ago by Rakesh SahuOracle DB Admin

Dear Readers, In this article, we will see the following Check Partition Summary More than N Days (60 Days). Script :

cat parti_last_60days.sh
#!/bin/bash
#Script to Check Partition In last 60 Days

#./Partition Report.sh DEMO

## Environment variables
TODAY=`date +%d-%b-%Y-%H:%M:%S`
rm /tmp/part1.log /tmp/part.log
touch /tmp/part1.log
export ORACLE_HOME='/u01/app/oracle/product/12.1.0.2/db_1'
export ORACLE_SID='ORCL11'
export ORACLE_BASE='/u01/app/oracle'
export PATH=$PATH:${ORACLE_HOME}/bin
export MAILIST='ktexpert@ktexperts.com'
export LOG='/tmp'


_OWNER=$1

function sql_script
{
echo " "
echo "Started To Run Partition Check Sql Query"
echo " "
sqlplus vinod/vinod <<EOF > /tmp/t.log
SET HEADING OFF FEEDBACK OFF ECHO OFF PAGESIZE 0
spool /tmp/schema.log
select table_name from dba_tables where owner='$_OWNER';
spool off;
EOF
sed -i '1d' /tmp/schema.log
sed -i '$d' /tmp/schema.log
wc=`wc -l /tmp/schema.log |awk '{ print $1 }'`

for (( c=1; c<=$wc; c++ ))
do
_tbls=`sed ''$c'!d' /tmp/schema.log`
_tbls1=` echo -n "${_tbls//[[:space:]]/}"`
if [ "$_tbls1" ]; then
echo "Checking Partition details on $_tbls1@$_OWNER"
sqlplus vinod/vinod <<EOF > /tmp/t.log
set line 2000 pages 2000
SET HEADING ON FEEDBACK OFF ECHO OFF PAGESIZE 0 trimspool on
col OBJECT_NAME for a40
col SUBOBJECT_NAME for a40
col OWNER for a30
col CREATED for a30
spool /tmp/part.log;
SELECT object_name,subobject_name,owner,created FROM dba_objects where owner='$_OWNER' and OBJECT_NAME='$_tbls1' and object_type='TABLE PARTITION' and trunc(created) <= trunc(sysdate-60) order by 4;
spool off
EOF
fi
sed -i '1d' /tmp/part.log
sed -i '$d' /tmp/part.log
#echo "################ Partition Check on $_tbls1 table of $_OWNER schema 60 Days Back ################# " >> /tmp/part1.log
if [ ! -s /tmp/part.log ]; then
#echo " " >> /tmp/part1.log
#echo "Checked and No Partition Found 60 Days Back for $_tbls1 table " >> /tmp/part1.log
#echo " " >> /tmp/part1.log
_var='FALSE'
else
echo "################ Partition Check on $_tbls1 table of $_OWNER schema 60 Days Back ################# " >> /tmp/part1.log
echo " " >> /tmp/part1.log
echo "-------------------------------------------------------------------------------------------------------------------------------------------" >> /tmp/part1.log
echo "TABLE_NAME Partition_Name OWNER PARTITION_CREATED_DATE" >> /tmp/part1.log
echo "-------------------------------------------------------------------------------------------------------------------------------------------" >> /tmp/part1.log
cat /tmp/part.log >> /tmp/part1.log
fi
done

echo " "
echo "----- End Of Query Execution logs can be check at /tmp/part1.log ------"
}

function send_mail
{
cat /tmp/part1.log | mailx -s "${TODAY} - Partition Check Summary" ${MAILIST}
}

#MAIN
sql_script
send_mail

cat parti_last_60days.sh

#!/bin/bash

#Script to Check Partition In last 60 Days

#./Partition Report.sh DEMO

## Environment variables

TODAY=`date +%d-%b-%Y-%H:%M:%S`

rm /tmp/part1.log /tmp/part.log

touch /tmp/part1.log

export ORACLE_HOME='/u01/app/oracle/product/12.1.0.2/db_1'

export ORACLE_SID='ORCL11'

export ORACLE_BASE='/u01/app/oracle'

export PATH=$PATH:${ORACLE_HOME}/bin

export MAILIST='ktexpert@ktexperts.com'

export LOG='/tmp'

_OWNER=$1

function sql_script

{

echo " "

echo "Started To Run Partition Check Sql Query"

echo " "

sqlplus vinod/vinod <<EOF > /tmp/t.log

SET HEADING OFF FEEDBACK OFF ECHO OFF PAGESIZE 0

spool /tmp/schema.log

select table_name from dba_tables where owner='$_OWNER';

spool off;

EOF

sed -i '1d' /tmp/schema.log

sed -i '$d' /tmp/schema.log

wc=`wc -l /tmp/schema.log |awk '{ print $1 }'`

for (( c=1; c<=$wc; c++ ))

_tbls=`sed ''$c'!d' /tmp/schema.log`

_tbls1=` echo -n "${_tbls//[[:space:]]/}"`

if [ "$_tbls1" ]; then

echo "Checking Partition details on $_tbls1@$_OWNER"

sqlplus vinod/vinod <<EOF > /tmp/t.log

set line 2000 pages 2000

SET HEADING ON FEEDBACK OFF ECHO OFF PAGESIZE 0 trimspool on

col OBJECT_NAME for a40

col SUBOBJECT_NAME for a40

col OWNER for a30

col CREATED for a30

spool /tmp/part.log;

SELECT object_name,subobject_name,owner,created FROM dba_objects where owner='$_OWNER' and OBJECT_NAME='$_tbls1' and object_type='TABLE PARTITION' and trunc(created) <= trunc(sysdate-60) order by 4;

spool off

EOF

sed -i '1d' /tmp/part.log

sed -i '$d' /tmp/part.log

#echo "################ Partition Check on $_tbls1 table of $_OWNER schema 60 Days Back ################# " >> /tmp/part1.log

if [ ! -s /tmp/part.log ]; then

#echo " " >> /tmp/part1.log

#echo "Checked and No Partition Found 60 Days Back for $_tbls1 table " >> /tmp/part1.log

#echo " " >> /tmp/part1.log

_var='FALSE'

else

echo "################ Partition Check on $_tbls1 table of $_OWNER schema 60 Days Back ################# " >> /tmp/part1.log

echo " " >> /tmp/part1.log

echo "-------------------------------------------------------------------------------------------------------------------------------------------" >> /tmp/part1.log

echo "TABLE_NAME Partition_Name OWNER PARTITION_CREATED_DATE" >> /tmp/part1.log

echo "-------------------------------------------------------------------------------------------------------------------------------------------" >> /tmp/part1.log

cat /tmp/part.log >> /tmp/part1.log

done

echo " "

echo "----- End Of Query Execution logs can be check at /tmp/part1.log ------"

}

function send_mail

{

cat /tmp/part1.log | mailx -s "${TODAY} - Partition Check Summary" ${MAILIST}

}

#MAIN

sql_script

send_mail

Out Put :

################   Partition Check on DEPART table of DEMO schema 60 Days Back ################# 
 
-------------------------------------------------------------------------------------------------------------------------------------------
TABLE_NAME                               Partition_Name                           OWNER                          PARTITION_CREATED_DATE
-------------------------------------------------------------------------------------------------------------------------------------------
DEPART                                   SYS_P21641                               DEMO                           11-JUN-20
DEPART                                   SYS_P13521                               DEMO                           27-JUL-20
DEPART                                   SYS_P13530                               DEMO                           28-JUL-20
DEPART                                   SYS_P13587                               DEMO                           29-JUL-20
DEPART                                   SYS_P13631                               DEMO                           04-AUG-20
DEPART                                   SYS_P13668                               DEMO                           05-AUG-20
DEPART                                   SYS_P13768                               DEMO                           06-AUG-20
DEPART                                   SYS_P13856                               DEMO                           19-AUG-20
DEPART                                   SYS_P13894                               DEMO                           20-AUG-20
DEPART                                   SYS_P13928                               DEMO                           11-SEP-20
DEPART                                   SYS_P14004                               DEMO                           12-SEP-20
DEPART                                   SYS_P14067                               DEMO                           14-SEP-20
DEPART                                   SYS_P14072                               DEMO                           15-SEP-20
################   Partition Check on EMPBKP table of DEMO schema 60 Days Back ################# 
 
-------------------------------------------------------------------------------------------------------------------------------------------
TABLE_NAME                               Partition_Name                           OWNER                          PARTITION_CREATED_DATE
-------------------------------------------------------------------------------------------------------------------------------------------
EMPBKP                                   SYS_P21661                               DEMO                           11-JUN-20
EMPBKP                                   SYS_P13561                               DEMO                           28-JUL-20
EMPBKP                                   SYS_P13632                               DEMO                           04-AUG-20
EMPBKP                                   SYS_P13672                               DEMO                           05-AUG-20
EMPBKP                                   SYS_P13772                               DEMO                           06-AUG-20
EMPBKP                                   SYS_P13857                               DEMO                           19-AUG-20
EMPBKP                                   SYS_P13903                               DEMO                           20-AUG-20
EMPBKP                                   SYS_P13949                               DEMO                           11-SEP-20
EMPBKP                                   SYS_P14009                               DEMO                           12-SEP-20

################ Partition Check on DEPART table of DEMO schema 60 Days Back #################

-------------------------------------------------------------------------------------------------------------------------------------------

TABLE_NAME Partition_Name OWNER PARTITION_CREATED_DATE

-------------------------------------------------------------------------------------------------------------------------------------------

DEPART SYS_P21641 DEMO 11-JUN-20

DEPART SYS_P13521 DEMO 27-JUL-20

DEPART SYS_P13530 DEMO 28-JUL-20

DEPART SYS_P13587 DEMO 29-JUL-20

DEPART SYS_P13631 DEMO 04-AUG-20

DEPART SYS_P13668 DEMO 05-AUG-20

DEPART SYS_P13768 DEMO 06-AUG-20

DEPART SYS_P13856 DEMO 19-AUG-20

DEPART SYS_P13894 DEMO 20-AUG-20

DEPART SYS_P13928 DEMO 11-SEP-20

DEPART SYS_P14004 DEMO 12-SEP-20

DEPART SYS_P14067 DEMO 14-SEP-20

DEPART SYS_P14072 DEMO 15-SEP-20

################ Partition Check on EMPBKP table of DEMO schema 60 Days Back #################

-------------------------------------------------------------------------------------------------------------------------------------------

TABLE_NAME Partition_Name OWNER PARTITION_CREATED_DATE

-------------------------------------------------------------------------------------------------------------------------------------------

EMPBKP SYS_P21661 DEMO 11-JUN-20

EMPBKP SYS_P13561 DEMO 28-JUL-20

EMPBKP SYS_P13632 DEMO 04-AUG-20

EMPBKP SYS_P13672 DEMO 05-AUG-20

EMPBKP SYS_P13772 DEMO 06-AUG-20

EMPBKP SYS_P13857 DEMO 19-AUG-20

EMPBKP SYS_P13903 DEMO 20-AUG-20

EMPBKP SYS_P13949 DEMO 11-SEP-20

EMPBKP SYS_P14009 DEMO 12-SEP-20

Thank you for giving your valuable time to read the above information. Follow us on Website … Read More

UNDO segments not Releasing Space

4 years ago by Rakesh MongaOracle DB Admin

Dear Readers, In this article, we will see UNDO segments not Releasing Space . UNDERSTANDING: There are three status of UNDO segments: ACTIVE: Currently in Used EXPIRED: Extend cannot be Used UNEXPIRED: unexpired extents were reused The UNDO records are not deleted when they are… Read More

Oracle : How to Kill Blocking Sessions in RAC

4 years ago by Vinod SiramOracle DB Admin

Dear Readers, In this article, we will see Kill blocking sessions in RAC . Kill blocking sessions in RAC:

set serveroutput on
set feedback off
set linesize 300

declare
blockcount number;
sqlreport varchar(3000);
sqloutput varchar(5000);
v_rec varchar(3000);

cursor blk_curs is select B.USERNAME BUSER,B.SID BSID,B.SERIAL# BSERIAL,B.INST_ID BID,B.STATUS BSTAT,B.LAST_CALL_ET BLAST,W.USERNAME WUSER,W.SID WSID,W.SERIAL# WSERIAL,W.INST_ID WID
from (select INST_ID,SID,SERIAL#,USERNAME,STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION, LAST_CALL_ET from gv$session where BLOCKING_SESSION >0) W,
(select INST_ID,SID,SERIAL#,USERNAME,STATUS,LAST_CALL_ET from gv$session ) B
where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID and B.username not in ('SYS','SYSTEM','DBSNMP','RMAN');
cursor distval is select distinct B.SID CSID, B.USERNAME CUSER from (select INST_ID,SID,SERIAL#,USERNAME,STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION, LAST_CALL_ET
from gv$session where BLOCKING_SESSION >0) W, (select INST_ID,SID,SERIAL#,USERNAME,STATUS,LAST_CALL_ET from gv$session ) B
where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID and B.username not in ('SYS','SYSTEM','DBSNMP','RMAN');
cursor killcurs(v in varchar, b in number) is select SID ,serial# SERIAL,inst_id ,username ,to_char(logon_time,'''DD-MON-YYYY HH24-MI-SS''') OTIME,event,status ,machine ,round(LAST_CALL_ET/60,2) OMIN
FROM gv$session where username is not null and username=v and sid=b order by inst_id;
begin
DBMS_OUTPUT.put_line('Checking for blocking sessions on this database');
DBMS_OUTPUT.put_line('-----------------------------------------------');
select count(*) into blockcount from (select * from gv$session where BLOCKING_SESSION >0) W,
(select * from gv$session ) B where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID and B.username not in ('SYS','SYSTEM','DBSNMP','RMAN');
IF blockcount > 0 THEN
DBMS_OUTPUT.put_line('Found blocking sessions -> Fetching report for the same');
DBMS_OUTPUT.put_line('-------------------------------------------------------');
for v_rec in blk_curs LOOP
dbms_output.put_line(v_rec.BUSER ||' ('||v_rec.BSID||','||v_rec.BSERIAL||',@'||v_rec.BID||') is Currently '||v_rec.BSTAT||' for last '||v_rec.BLAST||' Sec and it''s BLOCKING user '|| v_rec.WUSER||' ('||v_rec.WSID||','||v_rec.WSERIAL||',@'||v_rec.WID||')');
end loop;
DBMS_OUTPUT.put_line('-');
DBMS_OUTPUT.put_line('-');
DBMS_OUTPUT.put_line('Further details on blocking sessions -> includes kill script of blocking session');
DBMS_OUTPUT.put_line('--------------------------------------------------------------------------------');
for v_rec2 in distval LOOP
for v_rec3 in killcurs(v_rec2.CUSER, v_rec2.CSID) loop
dbms_output.put_line('alter system kill session '''||v_rec3.SID|| ',' || v_rec3.SERIAL||',@'||v_rec3.inst_id|| ''' immediate; '||v_rec3.username||' '||v_rec3.OTIME||' '|| v_rec3.event||' '||v_rec3.status||' '||v_rec3.machine||' '||v_rec3.OMIN);
end loop;
end loop;
DBMS_OUTPUT.put_line('-');
DBMS_OUTPUT.put_line('-');
ELSE
DBMS_OUTPUT.put_line('-');
DBMS_OUTPUT.put_line('-');
DBMS_OUTPUT.put_line('Hurrey !!! No blocking sessions found');
DBMS_OUTPUT.put_line('-');
DBMS_OUTPUT.put_line('-');
END IF;
END;
/

set serveroutput on

set feedback off

set linesize 300

declare

blockcount number;

sqlreport varchar(3000);

sqloutput varchar(5000);

v_rec varchar(3000);

cursor blk_curs is select B.USERNAME BUSER,B.SID BSID,B.SERIAL# BSERIAL,B.INST_ID BID,B.STATUS BSTAT,B.LAST_CALL_ET BLAST,W.USERNAME WUSER,W.SID WSID,W.SERIAL# WSERIAL,W.INST_ID WID

from (select INST_ID,SID,SERIAL#,USERNAME,STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION, LAST_CALL_ET from gv$session where BLOCKING_SESSION >0) W,

(select INST_ID,SID,SERIAL#,USERNAME,STATUS,LAST_CALL_ET from gv$session ) B

where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID and B.username not in ('SYS','SYSTEM','DBSNMP','RMAN');

cursor distval is select distinct B.SID CSID, B.USERNAME CUSER from (select INST_ID,SID,SERIAL#,USERNAME,STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION, LAST_CALL_ET

from gv$session where BLOCKING_SESSION >0) W, (select INST_ID,SID,SERIAL#,USERNAME,STATUS,LAST_CALL_ET from gv$session ) B

where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID and B.username not in ('SYS','SYSTEM','DBSNMP','RMAN');

cursor killcurs(v in varchar, b in number) is select SID ,serial# SERIAL,inst_id ,username ,to_char(logon_time,'''DD-MON-YYYY HH24-MI-SS''') OTIME,event,status ,machine ,round(LAST_CALL_ET/60,2) OMIN

FROM gv$session where username is not null and username=v and sid=b order by inst_id;

begin

DBMS_OUTPUT.put_line('Checking for blocking sessions on this database');

DBMS_OUTPUT.put_line('-----------------------------------------------');

select count(*) into blockcount from (select * from gv$session where BLOCKING_SESSION >0) W,

(select * from gv$session ) B where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID and B.username not in ('SYS','SYSTEM','DBSNMP','RMAN');

IF blockcount > 0 THEN

DBMS_OUTPUT.put_line('Found blocking sessions -> Fetching report for the same');

DBMS_OUTPUT.put_line('-------------------------------------------------------');

for v_rec in blk_curs LOOP

dbms_output.put_line(v_rec.BUSER ||' ('||v_rec.BSID||','||v_rec.BSERIAL||',@'||v_rec.BID||') is Currently '||v_rec.BSTAT||' for last '||v_rec.BLAST||' Sec and it''s BLOCKING user '|| v_rec.WUSER||' ('||v_rec.WSID||','||v_rec.WSERIAL||',@'||v_rec.WID||')');

end loop;

DBMS_OUTPUT.put_line('-');

DBMS_OUTPUT.put_line('Further details on blocking sessions -> includes kill script of blocking session');

DBMS_OUTPUT.put_line('--------------------------------------------------------------------------------');

for v_rec2 in distval LOOP

for v_rec3 in killcurs(v_rec2.CUSER, v_rec2.CSID) loop

dbms_output.put_line('alter system kill session '''||v_rec3.SID|| ',' || v_rec3.SERIAL||',@'||v_rec3.inst_id|| ''' immediate; '||v_rec3.username||' '||v_rec3.OTIME||' '|| v_rec3.event||' '||v_rec3.status||' '||v_rec3.machine||' '||v_rec3.OMIN);

end loop;

DBMS_OUTPUT.put_line('-');

ELSE

DBMS_OUTPUT.put_line('-');

DBMS_OUTPUT.put_line('Hurrey !!! No blocking sessions found');

DBMS_OUTPUT.put_line('-');

END IF;

END;

Output:

Checking for blocking sessions on this database
------------------------------------------------
Found blocking sessions -> Fetching report for the same
--------------------------------------------------------
VINOD (3033,27215,@1) is Currently INACTIVE for last 23 Sec and it's BLOCKING user VINOD (2126,11412,@2)
-
-
Further details on blocking sessions -> includes kill script of blocking session
--------------------------------------------------------------------------------
alter system kill session '3033,27215,@1' immediate, VINOD '02-NOV-2020 19-12-04' SQL*Net message from client INACTIVE server1 .38

Checking for blocking sessions on this database

------------------------------------------------

Found blocking sessions -> Fetching report for the same

--------------------------------------------------------

VINOD (3033,27215,@1) is Currently INACTIVE for last 23 Sec and it's BLOCKING user VINOD (2126,11412,@2)

Further details on blocking sessions -> includes kill script of blocking session

--------------------------------------------------------------------------------

alter system kill session '3033,27215,@1' immediate, VINOD '02-NOV-2020 19-12-04' SQL*Net message from client INACTIVE server1 .38

Thank you for giving your valuable time to read the above information. Follow us on Website … Read More

Oracle: Comparison between Python and Shell Script for sending email for the set of SQL commands

4 years ago by Rakesh SahuOracle DB Admin

Dear Readers, In this article, we will see the following Comparison between Python and Shell Script for sending email for the set of SQL commands. Python and Shell Script for sending email for the set of SQL commands: Shell Script:

#!/bin/bash
#Script to perform testing
## Environment variables
TODAY=`date +%d-%b-%Y-%H:%M:%S`
export ORACLE_HOME='/u01/app/oracle/product/12.1.0.2'
export ORACLE_SID='prod'
export ORACLE_BASE='/u01/app/oracle'
export PATH=$PATH:${ORACLE_HOME}/bin
export MAILIST='ktexperts@gmail.com'
export LOG='/tmp/test_mig.log'

#Function step1
function step1
{
echo " " >> $LOG
echo "Started Step 1" >> $LOG
echo " " >> $LOG
echo "Test email " >> $LOG
sqlplus vinod/vinod <<START >>$LOG
prompt updating records in EMP table
select * from emp where deptno=20;
prompt update emp set sal=sal+500 WHERE deptno=20;
update emp set sal=sal+500 WHERE deptno=20;
prompt select * from emp where deptno=20;
select * from emp;
START
echo " " >> $LOG
echo "----- END OF Step1 ------" >> $LOG
}

#Function to trigger email
function mailog
{
cat ${LOG} | mailx -s "${TODAY} - TEST Mail" ${MAILIST}
}

#Script execution is here
> $LOG
if [ "$#" -eq 0 ]
then
step1
mailog
else
$1
mailog
fi

#!/bin/bash

#Script to perform testing

## Environment variables

TODAY=`date +%d-%b-%Y-%H:%M:%S`

export ORACLE_HOME='/u01/app/oracle/product/12.1.0.2'

export ORACLE_SID='prod'

export ORACLE_BASE='/u01/app/oracle'

export PATH=$PATH:${ORACLE_HOME}/bin

export MAILIST='ktexperts@gmail.com'

export LOG='/tmp/test_mig.log'

#Function step1

function step1

{

echo " " >> $LOG

echo "Started Step 1" >> $LOG

echo " " >> $LOG

echo "Test email " >> $LOG

sqlplus vinod/vinod <<START >>$LOG

prompt updating records in EMP table

select * from emp where deptno=20;

prompt update emp set sal=sal+500 WHERE deptno=20;

update emp set sal=sal+500 WHERE deptno=20;

prompt select * from emp where deptno=20;

select * from emp;

START

echo " " >> $LOG

echo "----- END OF Step1 ------" >> $LOG

}

#Function to trigger email

function mailog

{

cat ${LOG} | mailx -s "${TODAY} - TEST Mail" ${MAILIST}

}

#Script execution is here

> $LOG

if [ "$#" -eq 0 ]

then

step1

mailog

else

mailog

Output:

updating records in EMP table
SQL>
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 4800 20
7566 JONES MANAGER 7839 02-APR-81 6975 20
7788 SCOTT ANALYST 7566 19-APR-87 7000 20
7876 ADAMS CLERK 7788 23-MAY-87 5100 20
7902 FORD ANALYST 7566 03-DEC-81 7000 20

SQL> update emp set sal=sal+500 WHERE deptno=20
SQL>
5 rows updated.

SQL> select * from emp where deptno=20
SQL>
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 5300 20
7566 JONES MANAGER 7839 02-APR-81 7475 20
7788 SCOTT ANALYST 7566 19-APR-87 7500 20
7876 ADAMS CLERK 7788 23-MAY-87 5600 20
7902 FORD ANALYST 7566 03-DEC-81 7500 20

updating records in EMP table

SQL>

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 4800 20

7566 JONES MANAGER 7839 02-APR-81 6975 20

7788 SCOTT ANALYST 7566 19-APR-87 7000 20

7876 ADAMS CLERK 7788 23-MAY-87 5100 20

7902 FORD ANALYST 7566 03-DEC-81 7000 20

SQL> update emp set sal=sal+500 WHERE deptno=20

SQL>

5 rows updated.

SQL> select * from emp where deptno=20

SQL>

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 5300 20

7566 JONES MANAGER 7839 02-APR-81 7475 20

7788 SCOTT ANALYST 7566 19-APR-87 7500 20

7876 ADAMS CLERK 7788 23-MAY-87 5600 20

7902 FORD ANALYST 7566 03-DEC-81 7500 20

Python Script:… Read More

ORACLE: PL/SQL script to rebuild Table(s) and it’s corresponding indexes

4 years ago by Vinod SiramOracle DB Admin

ORACLE:PL/SQL script to rebuild Table(s) and it’s corresponding indexes. Note : Please implement in lower environments before proceeding to Production. Pre requisites : Please download and check below file for pre-requisites. Table creation scripts The below script will be used for 1.Rebuild table(s) 2.Rebuild… Read More