Oracle 历史数据表迁移方案

当业务运行一段时间后，会出现有些表数据量很大，可能对系统性能产生不良的影响，常见的如订单表、登录log表等，这些数据很有时效性，比如我们一般很少去查上个月前的订单，最多也就是报表统计会涉及到。

解决方案：创建一个存储过程，每月定时调用，查出上个月前的数据保存到新建的表中，新表命名以月份为后缀，删除主表中上个月前的数据。

存储过程

CREATE OR REPLACE 
procedure operate_log_proc(return_code OUT VARCHAR2,
                                             return_msg  OUT VARCHAR2) 
authid current_user
is
  err_index           NUMBER;
  log_table_name      VARCHAR2(20);
  current_month_start DATE;
  create_table_cursor NUMBER(10);
  create_table_sql    VARCHAR2(1000);
  insert_data_sql     VARCHAR2(1000);
  delete_data_sql     VARCHAR2(1000);
  v_count 						NUMBER(10);
begin
  return_msg  := '执行[OPERATE_LOG_PROC]成功';
  return_code := '1';

  err_index := 1;

  -- 生成分表的表名
  -- 保存上个月第一天前的记录，表名应该从上两个月开始算的月份
  -- 比如上个月第一天是2024年1月1日，这时候查出的数据还是2023年12月的数据，上两个月2023年12月1日所属的月份刚好对上那个月的数据
  SELECT 'OPERATE_LOG_' ||
         TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -2), 'yyyymm')
    INTO log_table_name
    FROM DUAL;
  
  --表存在不用创建
	select count(1) into v_count from user_tables where table_name = log_table_name;

	if v_count <= 0 THEN
		-- 打开游标
		create_table_cursor := DBMS_SQL.OPEN_CURSOR;
		-- 拼接创建表的SQL语句，并执行
		create_table_sql := 'CREATE TABLE "' || log_table_name || '" (
		"Num_ID" NUMBER(32) NOT NULL ,
		"Reg_ID" CHAR(12 BYTE) NOT NULL ,
		"User_ID" CHAR(18 BYTE) NOT NULL ,
		"Organization" VARCHAR2(100 BYTE) NOT NULL ,
		"Organization_ID" CHAR(12 BYTE) NOT NULL ,
		"User_Name" VARCHAR2(30 BYTE) NOT NULL ,
		"Operate_Time" CHAR(14 BYTE) NOT NULL ,
		"Terminal_ID" VARCHAR2(40 BYTE) NOT NULL ,
		"Operate_Type" NUMBER(1) NOT NULL ,
		"Operate_Result" CHAR(1 BYTE) NOT NULL ,
		"Error_Code" CHAR(4 BYTE) NULL ,
		"Operate_Name" VARCHAR2(30 BYTE) NOT NULL ,
		"Operate_Condition" VARCHAR2(200 BYTE) NOT NULL ,
		"Insert_Time" DATE NOT NULL ,
		"Foreign_Reg_ID" VARCHAR2(36 BYTE) NOT NULL ,
		"Operate_Display" VARCHAR2(2000 BYTE) NULL
		)';
		DBMS_SQL.PARSE(create_table_cursor, create_table_sql, DBMS_SQL.V7);
		DBMS_SQL.CLOSE_CURSOR(create_table_cursor);
	end if; 

  err_index := 2;

	-- 上个月第一天，如2019-09-01
  SELECT TO_CHAR(trunc(ADD_MONTHS(SYSDATE, -1), 'MM'), 'YYYY-MM-DD') INTO current_month_start FROM DUAL;
  -- 将Operate_Log表中上月记录添加到新创建的表中
  insert_data_sql := 'INSERT INTO ' || log_table_name ||
                     ' (SELECT * FROM OPERATE_LOG WHERE to_char(INSERT_TIME, ''YYYY-MM-DD'') < ''' || current_month_start || ''')';
  EXECUTE IMMEDIATE insert_data_sql;

  err_index := 3;

  -- 从Operate_Log表中删除上月记录
  delete_data_sql := 'DELETE FROM OPERATE_LOG WHERE to_char(INSERT_TIME, ''YYYY-MM-DD'') < ''' || current_month_start || '''';
  EXECUTE IMMEDIATE delete_data_sql;

  err_index := 4;

  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    return_msg  := '过程[OPERATE_LOG_PROC]出错，' || '第[' || err_index ||
                   ']块语句出错';
    return_code := '0';
    ROLLBACK;

end operate_log_proc;

说明：operate_log_proc存储过程名称，OPERATE_LOG表名。authid current_user关键字给存储过程授权，不然报错找不到表。

-- 上个月第一天
SELECT TO_CHAR(trunc(ADD_MONTHS(SYSDATE, -1), 'MM'), 'YYYY-MM-DD') FROM DUAL;

定时任务

BEGIN
 dbms_scheduler.create_job(
	 job_name        => 'my_job_test',	--定时任务名称
	 job_type        => 'STORED_PROCEDURE',	--类型存储过程
	 job_action      => 'declare return_code VARCHAR2(200);return_msg VARCHAR2(200);begin OPERATE_LOG_PROC(return_code,return_msg); end;',	--存储过程名称
	 start_date      => sysdate,	--立刻执行
	 repeat_interval => 'TRUNC(LAST_DAY(SYSDATE))+1+1/24',    -- 每月1号凌晨1点执行一次
	 enabled         => TRUE,	--是否启用
	 comments        => 'test');	--备注
 end;

说明：没有参数的存储过程，可以直接用存储过程名称加上";"分号。需要权限才能创建。

分割线

---

如果不想用存储过程，也可以用代码的方式实现，使用Spring Boot + Mybatis + Oracle 实现。

思路：使用scheduling定时任务，每天定时触发，动态创建新表，备份历史数据。

关键代码：

SysLogService.java

/**
 * 系统日志接口
 */
public interface SysLogService {

    /**
     * 备份日志
     * @param createTime	时间
     * @param timeSuffix	后缀
     */
    void backupLog(String createTime, String timeSuffix);
    
    /**
     * 表是否存在
     * @param tableName	表名
     * @return	true存在，false不存在
     */
    boolean existTable(String tableName);
    
    /**
     * 创建表
     * @param tableName	表名
     */
    void createTable(String tableName);
}

SysLogServiceImpl.java

/**
 * 系统日志实现类
 */
@Service("sysLogService")
public class SysLogServiceImpl implements SysLogService {

	/** 日志表前缀 */
    private final static String TABLE_PREFIX = "SYS_LOG_";

	/**
     * 备份日志
     * @param createTime	时间
     * @param timeSuffix	后缀
     */
	@Override
	public void backupLog(String createTime, String timeSuffix) {
		String tableName = TABLE_PREFIX + timeSuffix;
		boolean flag = existTable(tableName);	//判断表是否存在
		if(!flag){
			//表不存在，先创建表
			createTable(tableName);
		}
		
		baseMapper.backupLog(createTime, tableName);
	}
	
	/**
     * 表是否存在
     * @param tableName	表名
     * @return	true存在，false不存在
     */
	@Override
	public boolean existTable(String tableName){
		int count = baseMapper.existTable(tableName);
		if(count > 0){
			return true;
		}
		return false;
	}
	
	/**
     * 创建表
     * @param tableName	表名
     */
	@Override
	public void createTable(String tableName){
		baseMapper.createTable(tableName);
	}
}

SysLogDAO.java

/**
 * 系统日志Mapper
 */
public interface SysLogDAO{
	/**
     * 备份日志
     * @param createTime	时间
     * @param tableName	表名
     */
	void backupLog(@Param("createTime")String createTime, @Param("tableName")String tableName);
	
	/**
     * 表是否存在
     * @param tableName	表名
     * @return	记录数
     */
    int existTable(@Param("tableName")String tableName);
    
    /**
     * 创建表
     * @param tableName	表名
     */
    void createTable(@Param("tableName")String tableName);
}

SysLogDAO.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.xinghuo.modules.sys.dao.SysLogDAO">
	<!-- 备份日志 -->
	<insert id="backupLog">
		INSERT INTO ${tableName} 
		SELECT * FROM SYS_LOG 
		WHERE CREATE_TIME <![CDATA[ < ]]> #{createTime} 
	</insert>
	
	<!-- 查看表是否存在 -->
	<select id="existTable" parameterType="String" resultType="Integer">
		select count(1) from user_tables where table_name = #{tableName} 
	</select>
	
	<!-- 创建表 -->
	<update id="createTable" parameterType="String">
		CREATE TABLE ${tableName}(
			ID VARCHAR(36) NOT NULL,
			USER_ID VARCHAR(50),
			OPERATION VARCHAR(50),
			METHOD VARCHAR(200),
			PARAMS TEXT,
			TIME BIGINT NOT NULL,
			IP VARCHAR(64),
			CREATE_TIME TIMESTAMP(0),
			USER_NAME VARCHAR(100),
			TENANT_ID VARCHAR(36),
			RESULTS TEXT,
			PRIMARY KEY(ID)
		)
	</update>
	
</mapper>

QuartzService.java

/**
 * 定时任务
 */
@Component
@Slf4j
public class QuartzService {
	
	@Autowired
	private SysLogService sysLogService;
	
	// 每月1号凌晨1点执行一次
	@Scheduled(cron = "0 0 1 1 * ?")
	public void deleteScheduled() {
		log.info("==================== 删除三个月前的日志数据开始 ====================");
		this.delMailLog();
		log.info("==================== 删除三个月前的日志数据结束 ====================");
	}
	
	/**
	 * 删除三个月前的日志数据
	 */
	private void delMailLog() {
		try {
			Calendar c = Calendar.getInstance();
	        c.add(Calendar.MONTH, - 3);  //设置为三个月前
            c.set(Calendar.DAY_OF_MONTH, 1);  //设置为该月的第一天
	        Date dateBefore = c.getTime();   //得到三个月前的时间
	        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
	        String time = simpleDateFormat.format(dateBefore) + " 00:00:00";
			
            /*
			 * 保存三个月前的第一天前的记录，表名应该从那个月前一天开始算的年份
			 * 比如三个月前的第一天是2024年1月1日，这时候查出的数据还是2023年的数据，三个月前第一天的前一天是2023年12月31日所属的年份刚好对上那个月的数据
			 */
			Calendar c1 = Calendar.getInstance();
	        c1.setTime(dateBefore);
	        c1.add(Calendar.DAY_OF_MONTH, - 1);  //设置为三个月前第一天的前一天
			SimpleDateFormat sf = new SimpleDateFormat("yyyy");
	        String timeSuffix = sf.format(c1.getTime());
			//操作日志需要备份，因为可能需要统计登录次数
			sysLogService.backupLog(time, timeSuffix);
			
			//删除操作日志
	        QueryWrapper<SysLogEntity> qw = new QueryWrapper<>();
			qw.lt("CREATE_TIME", time);
	        sysLogService.remove(qw);
		} catch (Exception e) {
			log.error("日志删除异常:{}",e);
		}
	}
}

注意：

1.需要在启动类增加注解@EnableScheduling

2.${tableName} 动态表名这里是美元符号$