Code Example. Keep History of a Table Split into Several Tables

===============================
General
===============================
Purpose: Keep history of a fast growing table with performing DELETE.
This is a LOG table, so not important to keep all records, thus ROWNUM < 10001 limit was used per day.
Under normal execution - the number of entries in LOG tables is small.
It is when system encounter some unexpected behavior, is when the LOG table is overloaded with error messages having same text over and over again.


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

PL/SQL Code

CREATE OR REPLACE PACKAGE BODY ADMIN_UTIL IS

PROCEDURE move_tb_2_tb  (p_source_table_name IN VARCHAR2,
                         p_target_table_name IN VARCHAR2) IS

  v_module_name  VARCHAR2(100);
  v_sql_str      VARCHAR2(1000);
  v_msg_str      VARCHAR2(1000);
  
BEGIN

  v_module_name := 'ADMIN_UTIL.move_tb_2_tb';
  
  v_sql_str := 'TRUNCATE TABLE '||p_target_table_name;
  EXECUTE IMMEDIATE v_sql_str;

  v_sql_str :=   'INSERT /*+ APPEND */ INTO '||p_target_table_name||' SELECT * FROM '||p_source_table_name||' WHERE rownum < 10001';
  EXECUTE IMMEDIATE v_sql_str;
  COMMIT;

  EXCEPTION
    WHEN OTHERS THEN
      v_msg_str := 'Unexpected Error: '||SQLERRM;
      SGA_PKG.write_sga_w_log(v_module_name, v_msg_str);
END move_tb_2_tb;

PROCEDURE purge_sga_w_log IS

  v_module_name  VARCHAR2(100);
  v_msg_str      VARCHAR2(1000);  
  v_origin_table VARCHAR2(30);
  v_source_table VARCHAR2(30);
  v_target_table VARCHAR2(30);  
  v_index        NUMBER;
  v_max_index    NUMBER;
BEGIN

  v_module_name := 'ADMIN_UTIL.purge_sga_w_log';
  v_origin_table := 'SGA_W_LOG';

  v_msg_str := 'Starting';
  SGA_PKG.write_sga_w_log(v_module_name, v_msg_str);
  v_max_index := 7;
  v_index := v_max_index;

  
  WHILE v_index > 0 LOOP
    IF v_index = 1 THEN
      v_source_table := v_origin_table;      
    ELSE            
      v_source_table := v_origin_table||'_'||TO_CHAR(v_index-1);
    END IF;  
    v_target_table := v_origin_table||'_'||TO_CHAR(v_index);  
    move_tb_2_tb(v_source_table, v_target_table);
    v_index := v_index -1;
  END LOOP;  
  
  EXECUTE IMMEDIATE 'TRUNCATE TABLE '||v_origin_table;

  v_msg_str := 'Finished';
  SGA_PKG.write_sga_w_log(v_module_name, v_msg_str);
  
  EXCEPTION
     WHEN OTHERS THEN
        v_msg_str := 'Unexpected error: '||SUBSTR(SQLERRM, 1, 900);
        BEGIN
          SGA_PKG.write_sga_w_log(v_module_name, v_msg_str);
        EXCEPTION
          WHEN OTHERS THEN
            NULL;
        END;      
  END purge_sga_w_log;        
END  ADMIN_UTIL;


View on top of the partial tables.
CREATE OR REPLACE VIEW SGA_W_LOG_VW
AS SELECT log_table, PROCEDURE_NAME, data, TO_CHAR(ts_last_modified,'YYYYMMDD hh24:mi:ss') AS ts_last_modified FROM (
SELECT 'SGA_W_LOG' AS log_table, procedure_name, data, ts_last_modified FROM SGA_W_LOG  
UNION ALL
SELECT 'SGA_W_LOG_1' AS log_table, procedure_name, data, ts_last_modified FROM SGA_W_LOG_1
UNION ALL
SELECT 'SGA_W_LOG_2' AS log_table, procedure_name, data, ts_last_modified FROM SGA_W_LOG_2
UNION ALL
SELECT 'SGA_W_LOG_3' AS log_table, procedure_name, data, ts_last_modified FROM SGA_W_LOG_3
UNION ALL
SELECT 'SGA_W_LOG_4' AS log_table, procedure_name, data, ts_last_modified FROM SGA_W_LOG_4
UNION ALL
SELECT 'SGA_W_LOG_5' AS log_table, procedure_name, data, ts_last_modified FROM SGA_W_LOG_5
UNION ALL
SELECT 'SGA_W_LOG_6' AS log_table, procedure_name, data, ts_last_modified FROM SGA_W_LOG_6
UNION ALL
SELECT 'SGA_W_LOG_7' AS log_table, procedure_name, data, ts_last_modified FROM SGA_W_LOG_7
)
ORDER BY ts_last_modified DESC

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 

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

Code Example. Truncate Table

============================================
General - Code Example for Truncating table

============================================
Code Example for Truncating table

============================================
Not Partitioned Table


============================================
Code Example for Truncating regular table

FUNCTION TRUNCATE_TABLE(p_table_name IN VARCHAR,

                        p_rerun_limit IN NUMBER, 
                        p_sleep_sec IN NUMBER) RETURN NUMBER IS

  v_status        NUMBER;
  v_rerun_ind     NUMBER;  
  v_rerun_counter NUMBER;  
  v_sql_str       VARCHAR2(1000);
  v_msg_str       VARCHAR2(1000);
  v_module_name   VARCHAR2(30);
BEGIN
  v_module_name := 'TRUNCATE_TABLE';
  v_rerun_counter := 1;
  v_rerun_ind := 1;  
  v_sql_str := 'TRUNCATE TABLE '||p_table_name;
  write_sga_w_log(v_module_name,'Before Execution of :'||v_sql_str);
  
  WHILE v_rerun_ind = 1 LOOP
  
    BEGIN
      EXECUTE IMMEDIATE v_sql_str;
 v_rerun_ind := 0;
 v_status := 0;
    EXCEPTION
      WHEN OTHERS THEN
   v_msg_str := 'Attempt #'||TO_CHAR(v_rerun_counter)||' Failed. Oracle Error: '||SQLERRM;
        write_sga_w_log(v_module_name,v_msg_str);
DBMS_LOCK.sleep(p_sleep_sec);
   v_rerun_counter := v_rerun_counter + 1;
v_status := -1;
IF v_rerun_counter > p_rerun_limit THEn
 v_rerun_ind := 0;
END IF;
    END;
  END LOOP;

  v_msg_str := 'Procedure Finished with Status : '||TO_CHAR(v_status) ||' After 'TO_CHAR(v_rerun_counter)||' Attempts.';
  write_sga_w_log(v_module_name,v_msg_str);
  RETURN v_status;

EXCEPTION
  v_msg_str := 'Unexpected Exception in Procedure '||v_module_name||'. Error Details: '||SQLERRM;
  write_sga_w_log(v_module_name,v_msg_str);
  v_status := -1;
  RETURN v_status;
  
END TRUNCATE_TABLE;

============================================
Partitioned Table


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

Code Example for Truncating partitioned table

  PROCEDURE TRUNCATE_SGA_W_EVENTS(p_days_to_keep IN NUMBER) IS

    v_sql_str      VARCHAR2(1000);

    v_base_sql_str VARCHAR2(1000);    

    v_module_name  VARCHAR2(30);

    v_msg_text     VARCHAR2(1000);

    v_max_days     NUMBER(2);

    CURSOR get_partitions_cur (pc_max_days IN NUMBER, pc_days_to_keep IN NUMBER) IS

    SELECT partition_name 

      FROM USER_TAB_PARTITIONS

     WHERE table_name = 'SGA_W_EVENTS'     

       AND  (TO_NUMBER(SUBSTR(partition_name,3)) > TO_NUMBER(TO_CHAR(SYSDATE,'DD'))) AND (TO_NUMBER(SUBSTR(partition_name,3)) < TO_NUMBER(TO_CHAR(SYSDATE,'DD'))+(pc_max_days)) 

     UNION ALL

     SELECT partition_name

       FROM USER_TAB_PARTITIONS 

      WHERE table_name = 'SGA_W_EVENTS'     

       AND  (TO_NUMBER(SUBSTR(partition_name,3)) < TO_NUMBER(TO_CHAR(SYSDATE,'DD')) -pc_days_to_keep);

  BEGIN

    v_module_name := 'TRUNCATE_SGA_W_EVENTS';

    

    v_msg_text  := 'Procudure Starting';

    WRITE_SGA_W_LOG(v_module_name,v_msg_text);    

    v_max_days := TO_NUMBER(TO_CHAR(LAST_DAY(SYSDATE),'DD'))-p_days_to_keep;

    

    v_base_sql_str := 'ALTER TABLE SGA_W_EVENTS TRUNCATE PARTITION XXX';    

    v_msg_text  := 'Base SQL: '||v_base_sql_str;

    WRITE_SGA_W_LOG(v_module_name,v_msg_text);

    

    FOR get_partitions_rec IN get_partitions_cur(v_max_days,p_days_to_keep) LOOP

      v_sql_str := REPLACE(v_base_sql_str,'XXX',get_partitions_rec.partition_name);

      EXECUTE IMMEDIATE v_sql_str;

      v_msg_text := 'Partition  '||get_partitions_rec.partition_name||' Truncated.';

      WRITE_SGA_W_LOG(v_module_name,v_msg_text);

    END LOOP;

    v_msg_text  := 'Procudure Finished Successfully';

    WRITE_SGA_W_LOG(v_module_name,v_msg_text);

  EXCEPTION

    WHEN OTHERS THEN

      v_msg_text := 'Error in procedure '||v_module_name||'. Error Details: '|| SUBSTR(SQLERRM, 1, 900);

      WRITE_SGA_W_LOG(v_module_name,v_msg_text);

      DBMS_OUTPUT.put_line(v_msg_text );

  END TRUNCATE_SGA_W_EVENTS;

SELECT 'ALTER TABLE '||table_name||' TRUNCATE SUBPARTITION '||subpartition_name||';' FROM USER_TAB_SUBPARTITIONS WHERE table_name = 'TABLE_NAME';

SELECT 'ALTER TABLE '||table_name||' TRUNCATE PARTITION '||partition_name||';' 

FROM USER_TAB_PARTITIONS WHERE table_name = 'TABLE_NAME'

For Example:

ALTER TABLE TABLE_NAME TRUNCATE SUBPARTITION P_65_S_20230605;

ALTER TABLE TABLE_NAME TRUNCATE PARTITION P_65;