Code Example. crontab task to TRUNCATE table on ongoing basis

====================================
General
====================================
Due to a bad application design, debug tables are constantly being written into, without a process that is cleaning these tables.

This example, is a ctrontab task, that activates sh script, that is calling sql script, that generates actual truncate statements, and then executes them.

====================================
Code 
====================================

oracle@my_server:~/scripts>% crontab -l
15 4 * * * /software/oracle/oracle/scripts/delete_old_trace_files.sh
20 4 * * * /software/oracle/oracle/scripts/truncate_sga_w_events.sh

less /software/oracle/oracle/scripts/truncate_sga_w_events.sh

#!/bin/bash
HOME_DIR=/software/oracle/oracle/scripts

cd $HOME_DIR
. $HOME_DIR/.set_profile

export LOG_FILE=${HOME_DIR}/truncate_sga_w_events.log
touch $LOG_FILE
export RUN_TIME=`date "+%Y%m%d"_"%H%M%S"`

echo "----------------------------------------------" >> $LOG_FILE
echo "Starting Truncate at $RUN_TIME" >> $LOG_FILE
echo "----------------------------------------------" >> $LOG_FILE

sqlplus system/xen86pga@igt @truncate_sga_w_events.sql

less TRUNCATE_SGA_W_EVENTS_SQL.sql >> $LOG_FILE
echo "Done " >> $LOG_FILE
echo "----------------------------------------------" >> $LOG_FILE

rm TRUNCATE_SGA_W_EVENTS_SQL.sql

less /software/oracle/oracle/scripts/truncate_sga_w_events.sql

SET HEADING OFF

SET VERIFY OFF 

SET TERMOUT OFF

SET PAGESIZE 0

SET FEEDBACK OFF

spool TRUNCATE_SGA_W_EVENTS_SQL.sql

SELECT 'TRUNCATE TABLE '||OWNER||'.'||SEGMENT_NAME||';' 

  FROM DBA_SEGMENTS 

 WHERE segment_name = 'SGA_W_EVENTS' 

  HAVING ROUND(SUM(bytes)/1024/1024) > 100

  GROUP BY OWNER,SEGMENT_NAME;

spool off

@TRUNCATE_SGA_W_EVENTS_SQL.sql

exit;

TRUNCATE_SGA_W_EVENTS_SQL.sql

TRUNCATE TABLE OWNER1.SGA_W_EVENTS;

TRUNCATE TABLE OWNER2.SGA_W_EVENTS;

TRUNCATE TABLE OWNER3.SGA_W_EVENTS;

TRUNCATE TABLE OWNER4.SGA_W_EVENTS;

truncate_sga_w_events.log

----------------------------------------------
Starting Truncate at 20161124_122549
----------------------------------------------
TRUNCATE TABLE ALB_VODAF_SPARX.SGA_W_EVENTS;                                    
TRUNCATE TABLE MLT_VODAF_SPARX.SGA_W_EVENTS;                                    
TRUNCATE TABLE ROM_VODAF_SPARX.SGA_W_EVENTS;                                    
TRUNCATE TABLE ZAF_VODAC_SPARX.SGA_W_EVENTS;                                    
TRUNCATE TABLE NZL_VODAF_SPARX.SGA_W_EVENTS;                                    
TRUNCATE TABLE GRC_VODAF_SPARX.SGA_W_EVENTS;                                    
TRUNCATE TABLE GHA_VODAF_SPARX.SGA_W_EVENTS;                                    
Done 

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

Starting Truncate at 20161124_123603

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

Done 

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