+++++++++++++longtx_12HR.sh
#!/bin/sh
. /opt/app/oracle/oracle_env
NOTIFY_LIST=dbaoracle@1800flowers.com
$ORACLE_HOME/bin/sqlplus -s “/ as sysdba” @/opt/app/oracle/scripts/longtx_12HR.sql
if [ `cat /opt/app/oracle/scripts/log/long_transaction_list.lst | grep -i “*****ATLAS NOTIFICATION REQUIRED*****” | wc -l` -n
e 0 ]; then
mailx -s “LONG RUNNING TRANSACTION ON ATLAS `hostname` on `date` ” $NOTIFY_LIST < /opt/app/oracle/scripts/log/long_transactio
n_list.lst
fi
+++++++++++++longtx_12HR.sql
set lines 120 pages 300
set serveroutput on
exec dbms_output.enable(NULL);
set echo off
spool /opt/app/oracle/scripts/log/long_transaction_list.lst
declare
cursor c1 is
select a.sid, a.serial#, substr(a.machine,1,22) machine, substr(a.osuser,1,15) osuser,
substr(a.username,1,22) username, a.sql_hash_value, a.sql_address, a.prev_hash_value,
a.prev_sql_addr, round((sysdate – to_date(b.start_time, ‘MM/DD/YY HH24:MI:SS’)) * 24, 2 ) hours1,
a.status sess_status, b.status tx_status
from v$session a , v$transaction b
where a.SADDR = b.SES_ADDR
and (sysdate – to_date(b.start_time, ‘MM/DD/YY HH24:MI:SS’)) * 24 > 12
order by (sysdate – to_date(b.start_time, ‘MM/DD/YY HH24:MI:SS’)) * 24;
last_sqltext varchar2(4000);
count1 number := 0;
begin
for cur in c1 loop
if (c1%rowcount = 1) then
dbms_output.put_line(‘=============================================================================’);
dbms_output.put_line(‘ LONG RUNNING TRANSACTION REPORT ‘);
dbms_output.put_line(‘ List of tranactions running for more than 12 Hours ‘);
dbms_output.put_line(‘ Script: longtx_12HR.sql ‘);
dbms_output.put_line(‘=============================================================================’);
end if;
count1 := count1 + 1;
dbms_output.put_line(‘Transactin# ‘ || count1 || ‘ Running for last ‘ || cur.hours1 || ‘ hours’);
dbms_output.put_line(‘SID=’||cur.sid ||’ ‘|| ‘SERIAL#=’ || cur.serial# || ‘ ‘ || ‘MACHINE=’ || cur.machine );
dbms_output.put_line(‘OSUSER=’|| cur.osuser || ‘ USERNAME=’ || cur.username || ‘ SESSION_STATUS=’||cur.sess_status || ‘ ‘ |
| ‘TX_STATUS=’ || cur.tx_status );
begin
select substr(sql_text, 1, 255) into last_sqltext
from v$sql
where hash_value = cur.sql_hash_value
and address = cur.sql_address
and rownum = 1;
exception
when no_data_found then
begin
select substr(sql_text,1,255) into last_sqltext
from v$sql
where hash_value = cur.prev_hash_value
and address = cur.prev_sql_addr
and rownum = 1;
exception
when no_data_found then last_sqltext := null;
end;
end;
dbms_output.put_line(last_sqltext);
dbms_output.put_line(‘——————————————————-‘);
end loop;
if count1 != 0 then
dbms_output.put_line(‘*****ATLAS NOTIFICATION REQUIRED*****’);
end if;
end;
/
spool off
exit