Oracle Shell script for wait capture

#!/bin/bash

ORACLE_HOME=/opt/oracle/product/10.2; export ORACLE_HOME
ORACLE_SID=cccdb; export ORACLE_SID

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

sqlplus -s /nolog <connect / as sysdba

set serveroutput on
set pagesize 100 lines 130
set feed on heading off
spool orapinx.alert

-- execute immediate dbms_output.put_line('see oracle dump');

declare

x number(7);

begin

select sysdate from dual;
select count(*) into x from v\$session_wait where event like '\%wait on X\%';

-- dbms_output.put_line('see oracle dump');

if x>0 then

execute immediate 'ALTER SESSION SET max_dump_file_size = UNLIMITED' ;
execute immediate 'ALTER SESSION SET EVENTS ''10998 trace name context forever,level 1''';
execute immediate 'ALTER SYSTEM SET EVENTS ''immediate trace name systemstate level 10''';
execute immediate 'ALTER SYSTEM SET EVENTS ''immediate trace name systemstate level 10''';
execute immediate 'ALTER SYSTEM SET EVENTS ''immediate trace name systemstate level 10''';

dbms_output.put_line('see oracle dump');
else
null;
end if;
end;

spool off
exit
EOF

# /opt/oracle/admin/cccdb/scripts

touch /opt/oracle/admin/cccdb/orapinx1.tmp

if [ `cat orapinx.alert|wc -l` -gt 0 ]

then
# echo "test"
cat /opt/oracle/admin/cccdb/scripts/orapinx.alert > /opt/oracle/admin/cccdb/scripts/orapinx.tmp

# to remove blank space
# cat tablespace.tmp | awk '$0!~/^$/ {print $0}' > xyz.tmp
# ^L= ctrl+L <<<<<<<<<--------


cat /opt/oracle/admin/cccdb/scripts/orapinx.tmp | sed 's/^L//g' > /opt/oracle/admin/cccdb/scripts/orapinx1.tmp

mv /opt/oracle/admin/cccdb/orapinx1.tmp /opt/oracle/admin/cccdb/orapinx.tmp



# mailx -s " Ora-error wait PIN X for APTUS (Novagenesis) Database" -r oracle@joshua anuj.singh@XXXX.co.uk < orapinx.tmp

fi