当前位置：首页 > news >正文

达梦数据库- 定时备份其他模式下的部分表

news 来源：原创 2025/9/18 14:01:27

要求：需要备份模式下有500多张表，已将需要备份的150个表整理出来，新建一个达梦用户，使用该用户每天自动备份这150个表，并保留最近30天的备份数据。

思路：创建存储过程执行备份操作，并创建定时任务，每天凌晨执行。新建一个配置表，将150个表名放到配置表中，需要备份的表从配置表中获取。

操作：

SYSDBA操作授权：

GRANT SELECT ON BJLGJ.uufr_ltx_ryxx TO BJLGJ_MRBF;

-- 授予创建和管理定时任务的权限
GRANT EXECUTE ON SYSJOB.DBMS_JOB TO BJLGJ_MRBF;

-- 授权备份用户查询需要模式下表的权限

SELECT 'GRANT SELECT ON BJLGJ.' || table_name || ' TO BJLGJ_MRBF;'
FROM MRBF_TABLE_NAME
WHERE owner = 'BJLGJ';

-- 执行上述拼接sql结果，完成查询另一个模式下表授权。

-- 创建存储过程用于备份其他模式下的指定表（表名来自配置表）并清理过期备份
CREATE OR REPLACE PROCEDURE SP_BACKUP_OTHER_SCHEMA_TABLES
AS
-- 定义变量
V_SRC_SCHEMA VARCHAR(100) := 'BJLGJ'; -- 源模式名称，需替换为实际模式名
V_DST_SCHEMA VARCHAR(100) := 'BJLGJ_MRBF'; -- 目标模式名称，备份表存放的模式
V_TABLE_LIST_TABLE VARCHAR(100) := 'MRBF_TABLE_NAME'; -- 存储需要备份的表名的表
V_TABLE_LIST_OWNER VARCHAR(100) := 'BJLGJ'; -- 存储表名的表所在的模式

V_TABLE_NAME VARCHAR(100); -- 原表名
V_BACKUP_TABLE_NAME VARCHAR(200); -- 备份表名
V_CURRENT_DATE VARCHAR(20); -- 当前日期
V_DROP_DATE VARCHAR(20); -- 需要删除的日期
V_SQL VARCHAR(1000); -- 动态SQL语句
V_TABLE_COUNT INT := 0; -- 统计需要备份的表数量

-- 从配置表获取需要备份的表名
CURSOR C_TABLES IS
SELECT TABLE_NAME
FROM BJLGJ.MRBF_TABLE_NAME
WHERE IS_ENABLED = 1;

BEGIN
-- 检查配置表是否存在
BEGIN
SELECT COUNT(*) INTO V_TABLE_COUNT
FROM ALL_TABLES
WHERE OWNER = V_TABLE_LIST_OWNER
AND TABLE_NAME = V_TABLE_LIST_TABLE;

IF V_TABLE_COUNT = 0 THEN
RAISE_APPLICATION_ERROR(-20001, '配置表 '||V_TABLE_LIST_OWNER||'.'||V_TABLE_LIST_TABLE||' 不存在');
END IF;
END;

-- 获取日期（确保格式正确）
SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') INTO V_CURRENT_DATE FROM DUAL;
SELECT TO_CHAR(SYSDATE - 30, 'YYYYMMDD') INTO V_DROP_DATE FROM DUAL;

V_TABLE_COUNT := 0;

-- 备份表处理（关键修正：添加双引号包裹表名和模式名）
FOR REC IN C_TABLES LOOP
V_TABLE_NAME := REC.TABLE_NAME;
V_BACKUP_TABLE_NAME := V_TABLE_NAME || '_' || V_CURRENT_DATE;
V_TABLE_COUNT := V_TABLE_COUNT + 1;

-- 先删除已存在的同名备份表
BEGIN
V_SQL := 'DROP TABLE "' || V_DST_SCHEMA || '"."' || V_BACKUP_TABLE_NAME || '"';
EXECUTE IMMEDIATE V_SQL;
EXCEPTION
WHEN OTHERS THEN
NULL; -- 表不存在时忽略
END;

-- 创建新备份表（关键修正：添加双引号）
V_SQL := 'CREATE TABLE "' || V_DST_SCHEMA || '"."' || V_BACKUP_TABLE_NAME || '" AS SELECT * FROM "' || V_SRC_SCHEMA || '"."' || REC.TABLE_NAME || '"';
EXECUTE IMMEDIATE V_SQL;

PRINT '备份表 "' || V_DST_SCHEMA || '"."' || V_BACKUP_TABLE_NAME || '" 创建成功';
END LOOP;

-- 检查是否有表被备份
IF V_TABLE_COUNT = 0 THEN
PRINT '警告：没有需要备份的表，请检查配置表';
ELSE
PRINT '成功备份 ' || V_TABLE_COUNT || ' 个表';
END IF;

-- 删除30天前的备份表（关键修正：添加双引号）
OPEN C_TABLES;
LOOP
FETCH C_TABLES INTO V_TABLE_NAME;
EXIT WHEN C_TABLES%NOTFOUND;

V_BACKUP_TABLE_NAME := V_TABLE_NAME || '_' || V_DROP_DATE;
BEGIN
V_SQL := 'DROP TABLE "' || V_DST_SCHEMA || '"."' || V_BACKUP_TABLE_NAME || '"';
EXECUTE IMMEDIATE V_SQL;
PRINT '已删除过期备份表 "' || V_DST_SCHEMA || '"."' || V_BACKUP_TABLE_NAME || '"';
EXCEPTION
WHEN OTHERS THEN
NULL; -- 表不存在时忽略
END;
END LOOP;
CLOSE C_TABLES;

PRINT '备份和清理操作完成';
EXCEPTION
WHEN OTHERS THEN
PRINT '操作失败: ' || SQLERRM;
RAISE;
END;
/

创建定时任务：

-- 指定存储过程的完整路径（模式名.存储过程名）
DECLARE
    JOB_ID INT;
BEGIN
    DBMS_JOB.SUBMIT(
        JOB_ID,
        'BJLGJ_MRBF.SP_BACKUP_OTHER_SCHEMA_TABLES;',  -- 明确指定存储过程所在的模式
        TRUNC(SYSDATE + 1) + 2/24,  -- 首次执行时间：明天凌晨2点
        'TRUNC(SYSDATE + 1) + 2/24'  -- 执行间隔：每天凌晨2点
    );

    DBMS_OUTPUT.PUT_LINE('定时任务创建成功，作业ID: ' || JOB_ID);
    COMMIT;
END;
/

相关文章：