要求：需要备份模式下有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;
/