Call to TRUNCATE table from crontab

oracle@my_server:~/scripts>% crontab -l
0 5 * * * /usr/local/etc/starhome/oracle_purge/oracle_purge.sh igt
5 5 * * * /software/oracle/oracle/scripts/clean_IPN_IBR_CELL_REPORT_DATA.sh
6 6 * * * /software/oracle/oracle/scripts/clean_IPN_SUBSCRIBER_USAGE.sh

======================================
Example A - Truncate non active table
======================================

clean_IPN_SUBSCRIBER_USAGE.sh
#!/bin/bash
. /etc/sh/orash/oracle_login.sh igt
cd /software/oracle/oracle/scripts/

echo ===================
echo RUS_BEELN_IPNQQ
echo ===================
sqlplus RUS_BEELN_IPNQQ/RUS_BEELN_IPNQQ@igt @call_to_handle_ipn_subs_usage_local.sql

echo ===================
echo UKR_KYIVS_IPNQQ
echo ===================
sqlplus UKR_KYIVS_IPNQQ/UKR_KYIVS_IPNQQ@igt @call_to_handle_ipn_subs_usage_local.sql

echo ===================
echo KAZ_KARTE_IPNQQ
echo ===================
sqlplus KAZ_KARTE_IPNQQ/KAZ_KARTE_IPNQQ@igt @call_to_handle_ipn_subs_usage_local.sql

echo ===================
echo KGZ_SKYMO_IPNQQ
echo ===================
sqlplus KGZ_SKYMO_IPNQQ/KGZ_SKYMO_IPNQQ@igt @call_to_handle_ipn_subs_usage_local.sql

echo ===================
echo PAK_MOBIL_IPNQQ
echo ===================
sqlplus PAK_MOBIL_IPNQQ/PAK_MOBIL_IPNQQ@igt @call_to_handle_ipn_subs_usage_local.sql

echo ===================
echo BGD_BANGA_IPNQQ
echo ===================
sqlplus BGD_BANGA_IPNQQ/BGD_BANGA_IPNQQ@igt @call_to_handle_ipn_subs_usage_local.sql

echo ===================
echo DONE
echo ===================


call_to_handle_ipn_subs_usage_local.sql
BEGIN
  handle_ipn_subs_usage_local;
END;
/
EXIT;


handle_ipn_subs_usage_local;
CREATE OR REPLACE PROCEDURE handle_ipn_subs_usage_local IS
  v_table_name  VARCHAR2(30);
  v_sql_str     VARCHAR2(1000);
  v_module_name VARCHAR2(100);
  v_msg_text    VARCHAR2(1000);
BEGIN
  v_module_name := 'handle_ipn_subs_usage_local';

  v_msg_text := 'Starting';
  INSERT INTO SGA_W_LOG(PROCEDURE_NAME, DATA, TS_LAST_MODIFIED)
  VALUES (v_module_name,v_msg_text, SYSDATE);
  commit;

 v_sql_str := 'ALTER SESSION SET DDL_LOCK_TIMEOUT = 600';
  EXECUTE IMMEDIATE v_sql_str;

  v_sql_str := 'TRUNCATE TABLE TABLE_XXX';
  SELECT table_name INTO v_table_name FROM USER_SYNONYMS WHERE synonym_name = 'SUBSCRIBER_USAGE_INACTIVE';
  v_sql_str := REPLACE(v_sql_str,'TABLE_XXX',v_table_name);

  v_msg_text := 'Running SQL : '||v_sql_str;
  INSERT INTO SGA_W_LOG(PROCEDURE_NAME, DATA, TS_LAST_MODIFIED)
  VALUES (v_module_name,v_msg_text, SYSDATE);
  commit;

  EXECUTE IMMEDIATE v_sql_str;

  v_msg_text := 'Cleanup Finished Successfully. Run SQL : '||v_sql_str;
  INSERT INTO SGA_W_LOG(PROCEDURE_NAME, DATA, TS_LAST_MODIFIED)
  VALUES (v_module_name,v_msg_text, SYSDATE);
  commit;

EXCEPTION
  WHEN OTHERS THEN
    v_msg_text := 'Unexpected Error: '||SQLERRM;
    INSERT INTO SGA_W_LOG(PROCEDURE_NAME, DATA, TS_LAST_MODIFIED)
    VALUES (v_module_name,v_msg_text, SYSDATE);
    commit;
END;
/


======================================
Example B - Delete old data from a table
======================================

clean_IPN_IBR_CELL_REPORT_DATA.sh

#!/bin/bash
. /etc/sh/orash/oracle_login.sh igt
cd /software/oracle/oracle/scripts/
sqlplus system/Xen86Pga@igt @clean_IPN_IBR_CELL_REPORT_DATA.sql

clean_IPN_IBR_CELL_REPORT_DATA.sql
CREATE TABLE KAZ_KARTE_IPNQQ.IPN_IBR_CELL_REPORT_DATA_BAK AS SELECT * FROM KAZ_KARTE_IPNQQ.IPN_IBR_CELL_REPORT_DATA WHERE 1=2;
ALTER TABLE KAZ_KARTE_IPNQQ.IPN_IBR_CELL_REPORT_DATA_BAK NOLOGGING;
INSERT /*+ APPEND */INTO KAZ_KARTE_IPNQQ.IPN_IBR_CELL_REPORT_DATA_BAK
SELECT * FROM KAZ_KARTE_IPNQQ.IPN_IBR_CELL_REPORT_DATA WHERE TS_START > SYSDATE -2;
commit;
TRUNCATE TABLE KAZ_KARTE_IPNQQ.IPN_IBR_CELL_REPORT_DATA;
INSERT /*+ APPEND */INTO KAZ_KARTE_IPNQQ.IPN_IBR_CELL_REPORT_DATA
SELECT * FROM KAZ_KARTE_IPNQQ.IPN_IBR_CELL_REPORT_DATA_BAK;
commit;
DROP TABLE KAZ_KARTE_IPNQQ.IPN_IBR_CELL_REPORT_DATA_BAK;

CREATE TABLE UKR_KYIVS_IPNQQ.IPN_IBR_CELL_REPORT_DATA_BAK AS SELECT * FROM UKR_KYIVS_IPNQQ.IPN_IBR_CELL_REPORT_DATA WHERE 1=2;
ALTER TABLE UKR_KYIVS_IPNQQ.IPN_IBR_CELL_REPORT_DATA_BAK NOLOGGING;
INSERT /*+ APPEND */INTO UKR_KYIVS_IPNQQ.IPN_IBR_CELL_REPORT_DATA_BAK
SELECT * FROM UKR_KYIVS_IPNQQ.IPN_IBR_CELL_REPORT_DATA WHERE TS_START > SYSDATE -2;
commit;
TRUNCATE TABLE UKR_KYIVS_IPNQQ.IPN_IBR_CELL_REPORT_DATA;
INSERT /*+ APPEND */INTO UKR_KYIVS_IPNQQ.IPN_IBR_CELL_REPORT_DATA
SELECT * FROM UKR_KYIVS_IPNQQ.IPN_IBR_CELL_REPORT_DATA_BAK;
commit;
DROP TABLE UKR_KYIVS_IPNQQ.IPN_IBR_CELL_REPORT_DATA_BAK;


CREATE TABLE KGZ_SKYMO_IPNQQ.IPN_IBR_CELL_REPORT_DATA_BAK AS SELECT * FROM KGZ_SKYMO_IPNQQ.IPN_IBR_CELL_REPORT_DATA WHERE 1=2;
ALTER TABLE KGZ_SKYMO_IPNQQ.IPN_IBR_CELL_REPORT_DATA_BAK NOLOGGING;
INSERT /*+ APPEND */INTO KGZ_SKYMO_IPNQQ.IPN_IBR_CELL_REPORT_DATA_BAK
SELECT * FROM KGZ_SKYMO_IPNQQ.IPN_IBR_CELL_REPORT_DATA WHERE TS_START > SYSDATE -2;
commit;
TRUNCATE TABLE KGZ_SKYMO_IPNQQ.IPN_IBR_CELL_REPORT_DATA;
INSERT /*+ APPEND */INTO KGZ_SKYMO_IPNQQ.IPN_IBR_CELL_REPORT_DATA
SELECT * FROM KGZ_SKYMO_IPNQQ.IPN_IBR_CELL_REPORT_DATA_BAK;
commit;
DROP TABLE KGZ_SKYMO_IPNQQ.IPN_IBR_CELL_REPORT_DATA_BAK;


======================================
Example B - Delete old data from a table
======================================
GA_W_COUNTERS_HISTORY

crontab
6 6 * * * /software/oracle/oracle/scripts/clean_ga_w_counters_history.sh

clean_ga_w_counters_history.sh
#!/bin/bash
. /etc/sh/orash/oracle_login.sh igt
cd /software/oracle/oracle/scripts/
echo ===================
echo CAN_ROGER_OVMDQ
echo ===================
sqlplus CAN_ROGER_OVMDQ/CAN_ROGER_OVMDQ@igt @call_to_clean_ga_w_counters_history.sql

call_to_clean_ga_w_counters_history.sql
CREATE TABLE CAN_ROGER_OVMDQ.GA_W_COUNTERS_HISTORY_BAK AS SELECT * FROM CAN_ROGER_OVMDQ.GA_W_COUNTERS_HISTORY WHERE 1=2;
ALTER TABLE CAN_ROGER_OVMDQ.GA_W_COUNTERS_HISTORY_BAK NOLOGGING;
INSERT /*+ APPEND */INTO CAN_ROGER_OVMDQ.GA_W_COUNTERS_HISTORY_BAK
SELECT * FROM CAN_ROGER_OVMDQ.GA_W_COUNTERS_HISTORY WHERE ts_last_modified > SYSDATE -5;
commit;
TRUNCATE TABLE CAN_ROGER_OVMDQ.GA_W_COUNTERS_HISTORY;
INSERT /*+ APPEND */INTO CAN_ROGER_OVMDQ.GA_W_COUNTERS_HISTORY
SELECT * FROM CAN_ROGER_OVMDQ.GA_W_COUNTERS_HISTORY_BAK;
commit;
DROP TABLE CAN_ROGER_OVMDQ.GA_W_COUNTERS_HISTORY_BAK;
EXIT;


crontab
6 6 * * * /software/oracle/oracle/scripts/clean_ga_w_counters_history.sh


clean_ga_w_counters_history.sh
#!/bin/bash
. /etc/sh/orash/oracle_login.sh igt
cd /software/oracle/oracle/scripts/
echo ===================
echo CAN_ROGER_OVMDQ
echo ===================
sqlplus CAN_ROGER_OVMDQ/CAN_ROGER_OVMDQ@igt @clean_ga_w_counters_history.sql


CREATE TABLE GA_W_COUNTERS_HISTORY_BAK AS SELECT * FROM GA_W_COUNTERS_HISTORY WHERE 1=2;
ALTER TABLE GA_W_COUNTERS_HISTORY_BAK NOLOGGING;
INSERT /*+ APPEND */INTO GA_W_COUNTERS_HISTORY_BAK
SELECT * FROM GA_W_COUNTERS_HISTORY WHERE ts_last_modified > SYSDATE -5;
commit;
TRUNCATE TABLE GA_W_COUNTERS_HISTORY;
INSERT /*+ APPEND */INTO GA_W_COUNTERS_HISTORY
SELECT * FROM GA_W_COUNTERS_HISTORY_BAK;
commit;
DROP TABLE GA_W_COUNTERS_HISTORY_BAK;

EXIT;


======================================
Example C - Delete old data from a table - basic
======================================
CREATE TABLE XXX_BAK  AS SELECT * FROM XXX WHERE 1=2;
ALTER TABLE XXX_BAK NOLOGGING;
INSERT /*+ APPEND */ INTO XXX_BAK SELECT * FROM XXX WHERE tmtst > TRUNC(SYSDATE) -10;
commit;
ALTER SESSION SET DDL_LOCK_TIMEOUT = 600;
TRUNCATE TABLE XXX;
INSERT /*+ APPEND */ INTO XXX SELECT * FROM XXX_BAK;
commit;

DROP TABLE XXX_BAK;


======================================
Example D - TRUNCATE Table of UserA from crontab of UserB 

======================================

As user A:

CREATE OR REPLACE PROCEDURE TRUNCATE_AUD AS

BEGIN

  EXECUTE IMMEDIATE 'TRUNCATE TABLE AUD$';

END;

/

GRANT EXECUTE ON TRUNCATE_AUD TO SYSTEM;

As user B

crontab

/software/oracle/oracle/scripts/remote_db/bgd_robi/clean_aud.sh

clean_aud.sh

#!/bin/bash

export ORA_VER=920

export ORACLE_SID=igt

export ORACLE_BASE=/software/oracle

export ORACLE_HOME=/software/oracle/920

export PATH=${PATH}:${ORACLE_HOME}/bin

. /etc/profile

. /software/oracle/oracle/.bash_profile

. /etc/sh/orash/oracle_login.sh igt

RUN_DATE=`date "+%Y%m%d_%H%M%S"`

PWD=/software/oracle/oracle/scripts/remote_db/bgd_robi

cd ${PWD}

LOG_FILE=/software/oracle/oracle/scripts/remote_db/bgd_robi/clean_aud.log

touch ${LOG_FILE}

echo =================== >> ${LOG_FILE}

echo "Starting at ${RUN_DATE} " >> ${LOG_FILE}

${PWD}/clean_aud_impl.sh 2>> ${LOG_FILE}

cat truncate_aud.log >> ${LOG_FILE}

echo =================== >> ${LOG_FILE}

clean_aud_impl.sh

#!/bin/bash

export ORA_VER=920

export ORACLE_SID=igt

export ORACLE_BASE=/software/oracle

export ORACLE_HOME=/software/oracle/920

export PATH=${PATH}:${ORACLE_HOME}/bin

. /etc/sh/orash/oracle_login.sh igt

sqlplus system/Xen86Pga@BGD_ROBI @/software/oracle/oracle/scripts/remote_db/bgd_robi/truncate_aud.sql

truncate_aud.sql

SET HEADING OFF

SET VERIFY OFF

SET PAGESIZE 0

SET LINESIZE 80

SET NEWPAGE NONE

SET FEEDBACK OFF

spool truncate_aud.log

SELECT 'Start TRUNCATE SYS.AUD$ at '||TO_CHAR(SYSDATE,'YYYYMMDD hh24:mi:ss') FROM DUAL;

alter session set ddl_lock_timeout=600;

BEGIN

  SYS.TRUNCATE_AUD;

END;

/

SELECT 'Finished TRUNCATE SYS.AUD$ at '||TO_CHAR(SYSDATE,'YYYYMMDD hh24:mi:ss') FROM DUAL;

spool off

exit