Saturday, 20 May 2017

Spooling Data from Multiple query and creating in a Single file

 Spooling Data from Multiple query and creating in a Single file

Note:- username/password are credential for Oracle Database

#!/usr/bin/bash

sqlplus -s username/password <<EOF
set feed off head off echo off  verify OFF wrap OFF trimspool ON;
set linesize 30000;
set pagesize 50000;
set serveroutput on size 10000;
spool /oracle/scripts/KPIS/C2S_TRANSFERS.txt;

--Sample Query1
SELECT TRANSFER_DATE "DATE",count(1) TOTAL_REQUEST, COUNT(DECODE(TRANSFER_STATUS,200,1,NULL)) SUCCESS_REQUEST,
COUNT(DECODE(TRANSFER_STATUS,206,1,NULL)) FAIL_REQUEST
from C2S_TRANSFERS
WHERE TRANSFER_DATE >= trunc(trunc(sysdate,'MM')-1,'MM') AND TRANSFER_DATE <= last_day(trunc(sysdate,'MM')-1)
GROUP BY TRANSFER_DATE ORDER BY TRANSFER_DATE;

spool off;
spool /oracle/scripts/KPIS/SUBSCRIBER_TRANSFERS.txt;

--Sample Query2
SELECT TRANSFER_DATE "DATE",count(1) TOTAL_REQUEST,
COUNT(DECODE(TRANSFER_STATUS,200,1,NULL)) SUCCESS_REQUEST,
COUNT(DECODE(TRANSFER_STATUS,206,1,NULL))FAIL_REQUEST
from SUBSCRIBER_TRANSFERS
WHERE TRANSFER_DATE >= trunc(trunc(sysdate,'MM')-1,'MM') AND TRANSFER_DATE <= last_day(trunc(sysdate,'MM')-1)
GROUP BY TRANSFER_DATE ORDER BY TRANSFER_DATE;

spool off;
spool /oracle/scripts/KPIS/CHANNEL_TRANSFERS.txt;

--Sample Query3
select transfer_date,COUNT(DECODE(TYPE,'O2C',1,NULL)) O2C_COUNT,COUNT(DECODE(TYPE,'C2C',1,NULL)) C2C_COUNT
from channel_transfers where
transfer_date>=trunc(trunc(sysdate,'MM')-1,'MM') and transfer_date<=last_day(trunc(sysdate,'MM')-1) group by transfer_date order by 1,2;

spool off;
spool /oracle/scripts/KPIS/C2S_TRANSFERS1.txt;

--Sample Query4
select TRANSFER_DATE "DATE",to_char(TRANSFER_DATE_TIME,'hh24') "HOUR",count(1)"COUNT" from C2S_TRANSFERS
where TRANSFER_DATE=(select TRANSFER_DATE from C2S_TRANSFERS where transfer_date>=trunc(trunc(sysdate,'MM')-1,'MM')
and transfer_date<=last_day(trunc(sysdate,'MM')-1) group by TRANSFER_DATE having count(1)=(
select max(count(1)) from C2S_TRANSFERS where transfer_date>=trunc(trunc(sysdate,'MM')-1,'MM')
and transfer_date<=last_day(trunc(sysdate,'MM')-1) group by TRANSFER_DATE )) group by TRANSFER_DATE,to_char(TRANSFER_DATE_TIME,'hh24') order by 1;

spool off;

Below script will help to collect the data from all reports to one report

!cd /oracle/scripts/KPIS
!join -t" " /oracle/scripts/KPIS/C2S_TRANSFERS.txt /oracle/scripts/KPIS/CHANNEL_TRANSFERS.txt>/oracle/scripts/KPIS/Final.txt
!echo "DATE\tTOTAL_REQUEST\tSUCCESS_REQUEST\tFAIL_REQUEST\tP2P_TOTAL\tP2P_SUCC\tP2P_FAIL\tO2C_COUNT\tC2C_COUNT">/oracle/scripts/KPIS/MONTHLY_KPI.txt
!cat /oracle/scripts/KPIS/Final.txt>>/oracle/scripts/KPIS/MONTHLY_KPI.txt
!echo "MAX_TXN_DATE\tHOUR\t\t\t\tCOUNT">>/oracle/scripts/KPIS/MONTHLY_KPI.txt
!cat /oracle/scripts/KPIS/C2S_TRANSFERS1.txt>>/oracle/scripts/KPIS/MONTHLY_KPI.txt
!rm -rf /oracle/scripts/KPIS/C2S_TRANSFERS.txt /oracle/scripts/KPIS/SUBSCRIBER_TRANSFERS.txt /oracle/scripts/KPIS/CHANNEL_TRANSFERS.txt
!rm -rf /oracle/scripts/KPIS/C2S_TRANSFERS1.txt /oracle/scripts/KPIS/Final.txt
disconnect;
exit;
EOF

No comments:

Post a Comment

Cloning database with RMAN

Below are the steps to clone production database(primedb) to UAT database(clonedb)   STEP1 :- Check the both servers are able to connect ...