Custom Procedure to clean log tables in Demantra
PROCEDURE clean_log_tab
/*
------------------------------------------------------------------
Procedure to clean log tables(audit_trail,DB_EXCEPTION_LOG)
------------------------------------------------------------------
*/
IS
vs_sql VARCHAR2(32000);
vs_proc VARCHAR2(30) := 'clean_log_tab';
vs_msg VARCHAR2(4000);
vi_months_to_keep NUMBER(5);
vd_start_date DATE;
vd_end_date DATE;
vi_delete_count_audit INTEGER := 0;
vi_delete_count_c_log INTEGER := 0;
vi_delete_count_ds_log INTEGER := 0;
vi_delete_count_e_log INTEGER := 0;
vi_delete_count_hd_log INTEGER := 0;
vi_delete_count_h_log INTEGER := 0;
vi_delete_count_i_log INTEGER := 0;
vi_delete_count_p_log INTEGER := 0;
vi_delete_count_s_log INTEGER := 0;
vi_delete_count_sql_log INTEGER := 0;
vi_delete_count_t_log INTEGER := 0;
vi_delete_count_usql_log INTEGER := 0;
vi_delete_count_w_log INTEGER := 0;
vi_loop_count INTEGER := 0;
vt_procedure NUMBER := 0;
vd_start_time NUMBER;
vd_end_time NUMBER;
v_lc_dem_schema VARCHAR2(4000);
BEGIN
pre_logon;
vd_start_time := DBMS_UTILITY.get_time;
/*
------------------------------------------------------------------
check the demantra schema global variable
------------------------------------------------------------------
*/
BEGIN
SELECT parameter_value
INTO v_lc_dem_schema
FROM apps.msd_dem_setup_parameters
WHERE parameter_name = 'MSD_DEM_SCHEMA';
EXCEPTION
WHEN NO_DATA_FOUND THEN
vs_msg := 'Demantra Clean log tables Process status:- '||SQLERRM;
WHEN OTHERS THEN
vs_msg := 'Demantra Clean log tables Process status:- '||SQLERRM;
END;
IF v_lc_dem_schema IS NOT NULL THEN
/*
------------------------------------------------------------------
Get the audit_history_length in months from sys_params
------------------------------------------------------------------
*/
get_param('sys_params','audit_history_length',vi_months_to_keep);
vs_msg := 'audit_history_length => '|| NVL(TO_CHAR( vi_months_to_keep ),'NULL');
DBEX(vs_msg, vs_proc, 'C');
IF vi_months_to_keep > 0 THEN
vi_months_to_keep := -1 * TO_NUMBER (TRIM( vi_months_to_keep ));
vd_end_date := add_months (CAST(SYSTIMESTAMP AS DATE), vi_months_to_keep);
/*
------------------------------------------------------------------
Deleting data from Log Tables based on audit_history_length
------------------------------------------------------------------
*/
IF get_is_table_exists( 'DB_CALL_LOG' ) = 1 THEN
vs_sql := 'DELETE '
||v_lc_dem_schema
||'.db_call_log WHERE msg_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';
dynamic_ddl( vs_sql );
vi_delete_count_c_log := SQL%ROWCOUNT;
END IF;
IF get_is_table_exists( 'DB_DETAILED_SECTION_LOG' ) = 1 THEN
vs_sql := 'DELETE '
||v_lc_dem_schema
||'.db_detailed_section_log WHERE msg_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';
dynamic_ddl( vs_sql );
vi_delete_count_ds_log := SQL%ROWCOUNT;
END IF;
IF get_is_table_exists( 'DB_EXCEPTION_LOG' ) = 1 THEN
vs_sql := 'DELETE '
||v_lc_dem_schema
||'.db_exception_log WHERE err_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';
dynamic_ddl( vs_sql );
vi_delete_count_e_log := SQL%ROWCOUNT;
END IF;
IF get_is_table_exists( 'DB_HEALTH_DETAILED_LOG' ) = 1 THEN
vs_sql := 'DELETE '
||v_lc_dem_schema
||'.db_health_detailed_log WHERE msg_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';
dynamic_ddl( vs_sql );
vi_delete_count_hd_log := SQL%ROWCOUNT;
END IF;
IF get_is_table_exists( 'DB_HEALTH_LOG' ) = 1 THEN
vs_sql := 'DELETE '
||v_lc_dem_schema
||'.db_health_log WHERE msg_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';
dynamic_ddl( vs_sql );
vi_delete_count_h_log := SQL%ROWCOUNT;
END IF;
IF get_is_table_exists( 'DB_INFO_LOG' ) = 1 THEN
vs_sql := 'DELETE '
||v_lc_dem_schema
||'.db_info_log WHERE msg_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';
dynamic_ddl( vs_sql );
vi_delete_count_i_log := SQL%ROWCOUNT;
END IF;
IF get_is_table_exists( 'DB_PATCH_LOG' ) = 1 THEN
vs_sql := 'DELETE '
||v_lc_dem_schema
||'.db_patch_log WHERE msg_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';
dynamic_ddl( vs_sql );
vi_delete_count_p_log := SQL%ROWCOUNT;
END IF;
IF get_is_table_exists( 'DB_SECTION_LOG' ) = 1 THEN
vs_sql := 'DELETE '
||v_lc_dem_schema
||'.db_section_log WHERE msg_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';
dynamic_ddl( vs_sql );
vi_delete_count_s_log := SQL%ROWCOUNT;
END IF;
IF get_is_table_exists( 'DB_SQL_LOG' ) = 1 THEN
vs_sql := 'DELETE '
||v_lc_dem_schema
||'.db_sql_log WHERE msg_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';
dynamic_ddl( vs_sql );
vi_delete_count_sql_log := SQL%ROWCOUNT;
END IF;
IF get_is_table_exists( 'DB_TIMING_LOG' ) = 1 THEN
vs_sql := 'DELETE '
||v_lc_dem_schema
||'.db_timing_log WHERE msg_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';
dynamic_ddl( vs_sql );
vi_delete_count_t_log := SQL%ROWCOUNT;
END IF;
IF get_is_table_exists( 'DB_UPGRADE_SQL_LOG' ) = 1 THEN
vs_sql := 'DELETE '
||v_lc_dem_schema
||'.db_upgrade_sql_log WHERE msg_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';
dynamic_ddl( vs_sql );
vi_delete_count_sql_log := SQL%ROWCOUNT;
END IF;
IF get_is_table_exists( 'DB_WARNING_LOG' ) = 1 THEN
vs_sql := 'DELETE '
||v_lc_dem_schema
||'.db_warning_log WHERE err_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';
dynamic_ddl( vs_sql );
vi_delete_count_w_log := SQL%ROWCOUNT;
END IF;
COMMIT;
END IF;
/***************************************************/
/* Record timings
/***************************************************/
vd_end_time := DBMS_UTILITY.get_time;
vt_procedure := (( vd_end_time - vd_start_time ) / 100 );
vs_msg := 'Procedure Completed in '|| LPAD(TO_CHAR(vt_procedure),8) ||' seconds';
DBEX( vs_msg, vs_proc, 'T' );
vs_msg := vs_msg ||' DELETE From '
||v_lc_dem_schema
||'.AUDIT_TRAIL ['|| TO_CHAR(NVL(vi_delete_count_audit,0)) ||']';
DBEX( vs_msg, vs_proc, 'T' );
vs_msg := 'DELETE From '
||v_lc_dem_schema
||'.DB_CALL_LOG ['|| TO_CHAR(NVL(vi_delete_count_c_log,0)) ||']';
DBEX( vs_msg, vs_proc, 'T' );
vs_msg := 'DELETE From '
||v_lc_dem_schema
||'.DB_DETAILED_SECTION_LOG ['|| TO_CHAR(NVL(vi_delete_count_ds_log,0)) ||']';
DBEX( vs_msg, vs_proc, 'T' );
vs_msg := 'DELETE From '
||v_lc_dem_schema
||'.DB_EXCEPTION_LOG ['|| TO_CHAR(NVL(vi_delete_count_e_log,0)) ||']';
DBEX( vs_msg, vs_proc, 'T' );
vs_msg := 'DELETE From '
||v_lc_dem_schema
||'.DB_HEALTH_DETAILED_LOG ['|| TO_CHAR(NVL(vi_delete_count_hd_log,0)) ||']';
DBEX( vs_msg, vs_proc, 'T' );
vs_msg := 'DELETE From '
||v_lc_dem_schema
||'.DB_HEALTH_LOG ['|| TO_CHAR(NVL(vi_delete_count_h_log,0)) ||']';
DBEX( vs_msg, vs_proc, 'T' );
vs_msg := 'DELETE From '
||v_lc_dem_schema
||'.DB_INFO_LOG ['|| TO_CHAR(NVL(vi_delete_count_i_log,0)) ||']';
DBEX( vs_msg, vs_proc, 'T' );
vs_msg := 'DELETE From '
||v_lc_dem_schema
||'.DB_PATCH_LOG ['|| TO_CHAR(NVL(vi_delete_count_p_log,0)) ||']';
DBEX( vs_msg, vs_proc, 'T' );
vs_msg := 'DELETE From '
||v_lc_dem_schema
||'.DB_SECTION_LOG ['|| TO_CHAR(NVL(vi_delete_count_s_log,0)) ||']';
DBEX( vs_msg, vs_proc, 'T' );
vs_msg := 'DELETE From '
||v_lc_dem_schema
||'.DB_SQL_LOG ['|| TO_CHAR(NVL(vi_delete_count_sql_log,0)) ||']';
DBEX( vs_msg, vs_proc, 'T' );
vs_msg := 'DELETE From '
||v_lc_dem_schema
||'.DB_TIMING_LOG ['|| TO_CHAR(NVL(vi_delete_count_t_log,0)) ||']';
DBEX( vs_msg, vs_proc, 'T' );
vs_msg := 'DELETE From '
||v_lc_dem_schema
||'.DB_UPGRADE_SQL_LOG ['|| TO_CHAR(NVL(vi_delete_count_usql_log,0)) ||']';
DBEX( vs_msg, vs_proc, 'T' );
vs_msg := 'DELETE From '
||v_lc_dem_schema
||'.DB_WARNING_LOG ['|| TO_CHAR(NVL(vi_delete_count_w_log,0)) ||']';
DBEX( vs_msg, vs_proc, 'T' );
ELSE
vs_msg := vs_msg||'- Procedure completed with Error';
DBEX( vs_msg, vs_proc,'E');
END IF;
EXCEPTION
WHEN OTHERS THEN
vs_msg := 'Demantra Clean log tables Process status:- '||SQLERRM;
DBEX( vs_msg, vs_proc, 'E' );
END clean_log_tab;
PROCEDURE clean_log_tab
/*
------------------------------------------------------------------
Procedure to clean log tables(audit_trail,DB_EXCEPTION_LOG)
------------------------------------------------------------------
*/
IS
vs_sql VARCHAR2(32000);
vs_proc VARCHAR2(30) := 'clean_log_tab';
vs_msg VARCHAR2(4000);
vi_months_to_keep NUMBER(5);
vd_start_date DATE;
vd_end_date DATE;
vi_delete_count_audit INTEGER := 0;
vi_delete_count_c_log INTEGER := 0;
vi_delete_count_ds_log INTEGER := 0;
vi_delete_count_e_log INTEGER := 0;
vi_delete_count_hd_log INTEGER := 0;
vi_delete_count_h_log INTEGER := 0;
vi_delete_count_i_log INTEGER := 0;
vi_delete_count_p_log INTEGER := 0;
vi_delete_count_s_log INTEGER := 0;
vi_delete_count_sql_log INTEGER := 0;
vi_delete_count_t_log INTEGER := 0;
vi_delete_count_usql_log INTEGER := 0;
vi_delete_count_w_log INTEGER := 0;
vi_loop_count INTEGER := 0;
vt_procedure NUMBER := 0;
vd_start_time NUMBER;
vd_end_time NUMBER;
v_lc_dem_schema VARCHAR2(4000);
BEGIN
pre_logon;
vd_start_time := DBMS_UTILITY.get_time;
/*
------------------------------------------------------------------
check the demantra schema global variable
------------------------------------------------------------------
*/
BEGIN
SELECT parameter_value
INTO v_lc_dem_schema
FROM apps.msd_dem_setup_parameters
WHERE parameter_name = 'MSD_DEM_SCHEMA';
EXCEPTION
WHEN NO_DATA_FOUND THEN
vs_msg := 'Demantra Clean log tables Process status:- '||SQLERRM;
WHEN OTHERS THEN
vs_msg := 'Demantra Clean log tables Process status:- '||SQLERRM;
END;
IF v_lc_dem_schema IS NOT NULL THEN
/*
------------------------------------------------------------------
Get the audit_history_length in months from sys_params
------------------------------------------------------------------
*/
get_param('sys_params','audit_history_length',vi_months_to_keep);
vs_msg := 'audit_history_length => '|| NVL(TO_CHAR( vi_months_to_keep ),'NULL');
DBEX(vs_msg, vs_proc, 'C');
IF vi_months_to_keep > 0 THEN
vi_months_to_keep := -1 * TO_NUMBER (TRIM( vi_months_to_keep ));
vd_end_date := add_months (CAST(SYSTIMESTAMP AS DATE), vi_months_to_keep);
/*
------------------------------------------------------------------
Deleting data from Log Tables based on audit_history_length
------------------------------------------------------------------
*/
IF get_is_table_exists( 'DB_CALL_LOG' ) = 1 THEN
vs_sql := 'DELETE '
||v_lc_dem_schema
||'.db_call_log WHERE msg_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';
dynamic_ddl( vs_sql );
vi_delete_count_c_log := SQL%ROWCOUNT;
END IF;
IF get_is_table_exists( 'DB_DETAILED_SECTION_LOG' ) = 1 THEN
vs_sql := 'DELETE '
||v_lc_dem_schema
||'.db_detailed_section_log WHERE msg_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';
dynamic_ddl( vs_sql );
vi_delete_count_ds_log := SQL%ROWCOUNT;
END IF;
IF get_is_table_exists( 'DB_EXCEPTION_LOG' ) = 1 THEN
vs_sql := 'DELETE '
||v_lc_dem_schema
||'.db_exception_log WHERE err_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';
dynamic_ddl( vs_sql );
vi_delete_count_e_log := SQL%ROWCOUNT;
END IF;
IF get_is_table_exists( 'DB_HEALTH_DETAILED_LOG' ) = 1 THEN
vs_sql := 'DELETE '
||v_lc_dem_schema
||'.db_health_detailed_log WHERE msg_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';
dynamic_ddl( vs_sql );
vi_delete_count_hd_log := SQL%ROWCOUNT;
END IF;
IF get_is_table_exists( 'DB_HEALTH_LOG' ) = 1 THEN
vs_sql := 'DELETE '
||v_lc_dem_schema
||'.db_health_log WHERE msg_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';
dynamic_ddl( vs_sql );
vi_delete_count_h_log := SQL%ROWCOUNT;
END IF;
IF get_is_table_exists( 'DB_INFO_LOG' ) = 1 THEN
vs_sql := 'DELETE '
||v_lc_dem_schema
||'.db_info_log WHERE msg_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';
dynamic_ddl( vs_sql );
vi_delete_count_i_log := SQL%ROWCOUNT;
END IF;
IF get_is_table_exists( 'DB_PATCH_LOG' ) = 1 THEN
vs_sql := 'DELETE '
||v_lc_dem_schema
||'.db_patch_log WHERE msg_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';
dynamic_ddl( vs_sql );
vi_delete_count_p_log := SQL%ROWCOUNT;
END IF;
IF get_is_table_exists( 'DB_SECTION_LOG' ) = 1 THEN
vs_sql := 'DELETE '
||v_lc_dem_schema
||'.db_section_log WHERE msg_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';
dynamic_ddl( vs_sql );
vi_delete_count_s_log := SQL%ROWCOUNT;
END IF;
IF get_is_table_exists( 'DB_SQL_LOG' ) = 1 THEN
vs_sql := 'DELETE '
||v_lc_dem_schema
||'.db_sql_log WHERE msg_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';
dynamic_ddl( vs_sql );
vi_delete_count_sql_log := SQL%ROWCOUNT;
END IF;
IF get_is_table_exists( 'DB_TIMING_LOG' ) = 1 THEN
vs_sql := 'DELETE '
||v_lc_dem_schema
||'.db_timing_log WHERE msg_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';
dynamic_ddl( vs_sql );
vi_delete_count_t_log := SQL%ROWCOUNT;
END IF;
IF get_is_table_exists( 'DB_UPGRADE_SQL_LOG' ) = 1 THEN
vs_sql := 'DELETE '
||v_lc_dem_schema
||'.db_upgrade_sql_log WHERE msg_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';
dynamic_ddl( vs_sql );
vi_delete_count_sql_log := SQL%ROWCOUNT;
END IF;
IF get_is_table_exists( 'DB_WARNING_LOG' ) = 1 THEN
vs_sql := 'DELETE '
||v_lc_dem_schema
||'.db_warning_log WHERE err_date < add_months (CAST(SYSTIMESTAMP AS DATE), '|| vi_months_to_keep ||')';
dynamic_ddl( vs_sql );
vi_delete_count_w_log := SQL%ROWCOUNT;
END IF;
COMMIT;
END IF;
/***************************************************/
/* Record timings
/***************************************************/
vd_end_time := DBMS_UTILITY.get_time;
vt_procedure := (( vd_end_time - vd_start_time ) / 100 );
vs_msg := 'Procedure Completed in '|| LPAD(TO_CHAR(vt_procedure),8) ||' seconds';
DBEX( vs_msg, vs_proc, 'T' );
vs_msg := vs_msg ||' DELETE From '
||v_lc_dem_schema
||'.AUDIT_TRAIL ['|| TO_CHAR(NVL(vi_delete_count_audit,0)) ||']';
DBEX( vs_msg, vs_proc, 'T' );
vs_msg := 'DELETE From '
||v_lc_dem_schema
||'.DB_CALL_LOG ['|| TO_CHAR(NVL(vi_delete_count_c_log,0)) ||']';
DBEX( vs_msg, vs_proc, 'T' );
vs_msg := 'DELETE From '
||v_lc_dem_schema
||'.DB_DETAILED_SECTION_LOG ['|| TO_CHAR(NVL(vi_delete_count_ds_log,0)) ||']';
DBEX( vs_msg, vs_proc, 'T' );
vs_msg := 'DELETE From '
||v_lc_dem_schema
||'.DB_EXCEPTION_LOG ['|| TO_CHAR(NVL(vi_delete_count_e_log,0)) ||']';
DBEX( vs_msg, vs_proc, 'T' );
vs_msg := 'DELETE From '
||v_lc_dem_schema
||'.DB_HEALTH_DETAILED_LOG ['|| TO_CHAR(NVL(vi_delete_count_hd_log,0)) ||']';
DBEX( vs_msg, vs_proc, 'T' );
vs_msg := 'DELETE From '
||v_lc_dem_schema
||'.DB_HEALTH_LOG ['|| TO_CHAR(NVL(vi_delete_count_h_log,0)) ||']';
DBEX( vs_msg, vs_proc, 'T' );
vs_msg := 'DELETE From '
||v_lc_dem_schema
||'.DB_INFO_LOG ['|| TO_CHAR(NVL(vi_delete_count_i_log,0)) ||']';
DBEX( vs_msg, vs_proc, 'T' );
vs_msg := 'DELETE From '
||v_lc_dem_schema
||'.DB_PATCH_LOG ['|| TO_CHAR(NVL(vi_delete_count_p_log,0)) ||']';
DBEX( vs_msg, vs_proc, 'T' );
vs_msg := 'DELETE From '
||v_lc_dem_schema
||'.DB_SECTION_LOG ['|| TO_CHAR(NVL(vi_delete_count_s_log,0)) ||']';
DBEX( vs_msg, vs_proc, 'T' );
vs_msg := 'DELETE From '
||v_lc_dem_schema
||'.DB_SQL_LOG ['|| TO_CHAR(NVL(vi_delete_count_sql_log,0)) ||']';
DBEX( vs_msg, vs_proc, 'T' );
vs_msg := 'DELETE From '
||v_lc_dem_schema
||'.DB_TIMING_LOG ['|| TO_CHAR(NVL(vi_delete_count_t_log,0)) ||']';
DBEX( vs_msg, vs_proc, 'T' );
vs_msg := 'DELETE From '
||v_lc_dem_schema
||'.DB_UPGRADE_SQL_LOG ['|| TO_CHAR(NVL(vi_delete_count_usql_log,0)) ||']';
DBEX( vs_msg, vs_proc, 'T' );
vs_msg := 'DELETE From '
||v_lc_dem_schema
||'.DB_WARNING_LOG ['|| TO_CHAR(NVL(vi_delete_count_w_log,0)) ||']';
DBEX( vs_msg, vs_proc, 'T' );
ELSE
vs_msg := vs_msg||'- Procedure completed with Error';
DBEX( vs_msg, vs_proc,'E');
END IF;
EXCEPTION
WHEN OTHERS THEN
vs_msg := 'Demantra Clean log tables Process status:- '||SQLERRM;
DBEX( vs_msg, vs_proc, 'E' );
END clean_log_tab;
No comments:
Post a Comment