Database Log cleanup-how to efficiently clean up logs in projects

In a project, some data must be recorded in the log table for audit or statistics. However, after a log table is saved for a period of time, it needs to be cleared regularly. In many projects, database jobs are used to clean each log table separately. disadvantages of this method: 1) new log table cleanup is a huge workload; 2) There are a large number of identical codes due to the same logic processing, but different cleanup cycles; 3) if a function is blocked, no log cleanup is required, blocking scheduled tasks also requires a large amount of development workload...

Solution: You can save the dynamic parameters such as the table name and cleanup Cycle Related to the cleanup log table in the separate log cleanup configuration table of the database. The same logic processing is implemented in the Oracle storage process. Benefits of this implementation: 1) adding a log table cleanup requires only adding a log cleanup configuration table initialization script; 2) putting the log cleanup logic into a stored procedure for implementation, adding, modifying, and deleting logs can minimize the risk. 3) Clear Data in the log Clearing table and dynamically configure the log clearing time period...

See the data model of the log Clearing table, t_clearlogconfig.

Column	Field Length	Nullable	Comment
Tablename	Varchar2 (30)	No	Clear log table name
Timecolumn	Varchar2 (30)	No	Time Field identifier for clearing the log table
Savedays	Number	No	Log Retention days
Timetype	Varchar2 (2)	No	Log table time type 1: date type 2: date type
Timeformat	Varchar2 (30)	Yes	If the time type is dense, this field stores the specific time format such as: YYYY-MM-DD hh24: MI: SS
Description	Varchar2 (600)	Yes	This field is not used. Only the description is saved for your convenience.

Configure the log table information to be deleted in the preceding table, add a stored procedure, and perform cleanup based on the preceding table configuration. Then configure the scheduled task to trigger the stored procedure every morning.

The implementation method is as follows (Note: The exception information is not recorded in the instance and must be added in the project; otherwise, the execution of the stored procedure is abnormal and the problem is hard to be found and located)

Create or replace procedure p_job_clearlogas v_tablename t_clearlogconfig.tablename % type; -- other variables are similar to the definition, and the cursor c_clearlog is select t is not listed one by one. tablename, T. timecolumn, T. savedays, T. timetype, T. timeformat from t_clearlogconfig t; begin open c_clearlog; loop begin -- Exception capture is performed for each log cleanup to prevent log Cleanup from failing and other logs from being cleanup. The fetch into and exit statements should be executed outside the in, sorry. Fetch c_clearlog into v_tablename, v_timecolumn, v_savedays, v_timetype, v_timeformat; exit when c_clearlog % notfound; If v_timetype = '1' then -- if the time type is delayed type v_ SQL: = 'delete from' | v_tablename | 'where: timecolumn <to_char (trunc (sysdate-: savedays),: timeformat) '; execute immediate v_ SQL using v_timecolumn, v_savedays, v_timeformat; commit; elsif v_timetype = '2' then -- the time type is date type v_ SQL: = 'delete from' | v_tablename | 'where: timecolumn <sysdate-: savedays '; execute immedidate v_ SQL using v_timecolumn, v_savedays; commit; end if; Exception when others then rollback; -- records the exception log end loop; close c_clearlog; Exception rollback; If c_clearlog % isopen then close c_clearlog; end if; -- Record the log end p_job_clearjob;

Think about other issues:

1) The above logs are cleared on a daily basis, but generally the log is cleaned up for a long period of time. It is cleared once every six months or one year or longer. What if it is modified to be executed on a monthly basis?

2) When many logs are stored in a log table in a project, the log table name is dynamic and often changes in time. For example, the log name is t_log_201212, how can I modify the implementation in this case?

Note:

1) You can add a clear flag in the log Clearing table. If it is a clear ID, the log is cleared. Otherwise, you can dynamically configure whether to clean the log.

2) This article is just a mix of personal thoughts. If you have any good ideas or comments, please contact us.