Oracle 历史数据表迁移方案

秒速五厘米 2024-04-18 12:44 85阅读 0赞

当业务运行一段时间后，会出现有些表数据量很大，可能对系统性能产生不良的影响，常见的如订单表、登录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;
    
      -- 生成分表的表名
      SELECT 'Operate_Log_' ||
             TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1), '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 00:00:00
      SELECT TRUNC(SYSDATE, 'MM') 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'') < to_char(to_date(''' ||
                         current_month_start || ''',''YYYY-MM-DD''), ''YYYY-MM-DD''))';
      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'') < to_char(to_date(''' ||
                         current_month_start || ''',''YYYY-MM-DD''), ''YYYY-MM-DD'')';
      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关键字给存储过程授权，不然报错找不到表。

定时任务

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+5/24',    -- 每月1号5点执行
    	 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;
    	
    	// 每天晚上10点启动
    	@Scheduled(cron = "0 0 22 * * ?")
    	public void deleteScheduled() {
    		log.info("==================== 删除三个月前的日志数据开始 ====================");
    		this.delMailLog();
    		log.info("==================== 删除三个月前的日志数据结束 ====================");
    	}
    	
    	/**
    	 * 删除三个月前的日志数据
    	 */
    	private void delMailLog() {
    		try {
    			Calendar c = Calendar.getInstance();
    	        c.setTime(new Date());
    	        c.add(Calendar.MONTH, - 3);  //设置为三个月前
    	        Date dateBefore = c.getTime();   //得到三个月前的时间
    	        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    	        String time = simpleDateFormat.format(dateBefore);
    			
    			SimpleDateFormat sf = new SimpleDateFormat("yyyy");
    	        String timeSuffix = sf.format(dateBefore);
    			//操作日志需要备份，因为可能需要统计登录次数
    			sysLogService.backupLog(time, timeSuffix);
    			
    			//删除操作日志
    	        QueryWrapper<SysLogEntity> qw = new QueryWrapper<>();
    			qw.le("CREATE_TIME", time);
    	        sysLogService.remove(qw);
    		} catch (Exception e) {
    			log.error("日志删除异常:{}",e);
    		}
    	}
    }

注意：

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

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