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