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;