Dear Readers,
In this article, we will see the following Check Partition Summary More than N Days (60 Days).
Script :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 |
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 |
Out Put :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
################ 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 : www.ktexperts.com
Facebook Page : KTexperts
Linkedin Page : KT EXPERTS
Note: Please test scripts in Non Prod before trying in Production.
Loading...
