在Oracle中实现每日表备份并删除7天前的备份表

不用闪回技术，因为业务想眼睁睁的看到备份表，而不是让DBA搞一通之后，才能看到备份数据表

OK，那好办了，写个存储过程解决你的需求，每天建个新表，把数据备份进去，业务人员可以看到这些每天的备份表

然后只保留7天，7天前的表给删掉

 

创建存储过程

create or replace PROCEDURE DAILY_BACKUP AS
v_now VARCHAR2(8);
v_seven varchar2(8);
v_7tab1_has int;
v_7tab2_has int;
v_7tab3_has int;
v_table_name_1 CONSTANT VARCHAR2(20) := 'TAB_TEST1_';
v_table_name_2 CONSTANT VARCHAR2(20) := 'TAB_TEST2_';
v_table_name_3 CONSTANT VARCHAR2(20) := 'TAB_TEST3_';

BEGIN
--获取当天日期
select to_char(SYSDATE, 'YYYYMMDD')
into v_now
from dual;

--获取7天前日期
select to_char(TRUNC(SYSDATE -7), 'YYYYMMDD')
into v_seven
from dual;

--删除7天前的表
select count(1) into v_7tab1_has from user_tables where TABLE_NAME = upper(v_table_name_1  || v_seven);
if v_7tab1_has=1 then
--如果存在，则执行drop table
execute immediate 'drop table TBS.'|| v_table_name_1  || v_seven;
end if;

select count(1) into v_7tab2_has from user_tables where TABLE_NAME = upper(v_table_name_2 || v_seven);
if v_7tab2_has=1 then
--如果存在，则执行drop table
execute immediate 'drop table TBS.'|| v_table_name_2 || v_seven;
end if;

select count(1) into v_7tab3_has from user_tables where TABLE_NAME = upper(v_table_name_3 || v_seven);
if v_7tab3_has=1 then
--如果存在，则执行drop table
execute immediate 'drop table TBS.'|| v_table_name_3  || v_seven;
end if;

--创建当天的数据备份
EXECUTE IMMEDIATE 'CREATE TABLE TBS.' || v_table_name_1  || v_now || ' AS SELECT * FROM TAB_TEST1';
EXECUTE IMMEDIATE 'CREATE TABLE TBS.' || v_table_name_2 || v_now || ' AS SELECT * FROM TAB_TEST2';
EXECUTE IMMEDIATE 'CREATE TABLE TBS.' || v_table_name_3 || v_now || ' AS SELECT * FROM TAB_TEST3';

 

END DAILY_BACKUP;

 

创建作业（每天中午12点执行，注意，你用图形界面创建作业更方便 ，不一定要用语句）

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => '"TBS"."DAILYBACKDATA"',
job_type => 'STORED_PROCEDURE',
job_action => 'TBS.DAILY_BACKUP',
number_of_arguments => 0,
start_date => NULL,
repeat_interval => 'FREQ=DAILY;BYHOUR=12;BYMINUTE=0;BYSECOND=0',
end_date => NULL,
enabled => FALSE,
auto_drop => FALSE,
comments => 'DAILYBACKDATA');

DBMS_SCHEDULER.SET_ATTRIBUTE(
name => '"TBS"."DAILYBACKDATA"',
attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);



DBMS_SCHEDULER.enable(
name => '"TBS"."DAILYBACKDATA"');
END;

 

Enjoy ：）