通过job和procedure来实现Oracle数据库中表的备份

job和procedure来实现Oracle数据库中表的备份

文章出处：http://www.cnblogs.com/stoneblog/archive/2009/09/23/1572548.html
1、创建存储过程
create or replace procedure data_auto_backup as
  v_tablename varchar2(200);
  v_year varchar2(10);
  v_month varchar2(10);
  v_bakdate varchar2(50);
  v_maxdate date;
  v_tablecount integer;
  v_recordcount integer;
begin
  select max(t.field1) - 30 into v_maxdate from table1 t; 
  v_year := to_char(v_maxdate,'yyyy');
  v_month := to_char(v_maxdate,'MM');
  v_bakdate := to_char(v_maxdate,'yyyy-MM-dd');
   
  -- 检查将要使用的年月表是否存在
  v_tablename := 'table1' || v_year || v_month;
   
  SELECT COUNT(TABLE_NAME) INTO v_tablecount FROM USER_TABLES ut WHERE ut.table_name = v_tablename;
   
  if v_tablecount>0 then 
  dbms_output.put_line('该表存在！'); 
  else begin
  dbms_output.put_line('该表不存在或当前用户无权访问！'); 
  execute immediate 'CREATE TABLE ' || v_tablename || ' AS SELECT * FROM table1 sr WHERE sr.field2=''''';
  end;
  end If; 

  -- table1表备份
  execute immediate 'DELETE FROM ' || v_tablename || ' sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''';
  execute immediate 'INSERT INTO ' || v_tablename || ' SELECT * FROM table1 sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''';
   
  --dbms_output.put_line('DELETE FROM ' || v_tablename || ' sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''');
  --dbms_output.put_line('INSERT INTO ' || v_tablename || ' SELECT * FROM table1 sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''');
  --dbms_output.put_line('SELECT COUNT(sr.field2) FROM ' || v_tablename || ' sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''');
   
  execute immediate 'SELECT COUNT(sr.field2) FROM ' || v_tablename || ' sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''' into v_recordcount;
  if v_recordcount>0 then begin
  execute immediate 'DELETE FROM table1 sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''';
  --dbms_output.put_line('DELETE FROM table1 sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''');
  end;
  end If; 
   
  commit;
   
   
  -- 检查将要使用的年月表是否存在
  v_tablename := 'table2' || v_year || v_month;
   
  SELECT COUNT(TABLE_NAME) INTO v_tablecount FROM USER_TABLES ut WHERE ut.table_name = v_tablename;
   
  if v_tablecount>0 then 
  dbms_output.put_line('该表存在！'); 
  else begin
  dbms_output.put_line('该表不存在或当前用户无权访问！'); 
  execute immediate 'CREATE TABLE ' || v_tablename || ' AS SELECT * FROM table2 cpi WHERE cpi.field3=''''';
  end;
  end If; 

  -- table2表备份
  execute immediate 'DELETE FROM ' || v_tablename || ' sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''';
  execute immediate 'INSERT INTO ' || v_tablename || ' SELECT * FROM table2 sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''';
   
  --dbms_output.put_line('DELETE FROM ' || v_tablename || ' sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''');
  --dbms_output.put_line('INSERT INTO ' || v_tablename || ' SELECT * FROM table2 sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''');
  --dbms_output.put_line('SELECT COUNT(sr.field3) FROM ' || v_tablename || ' sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''');
   
  execute immediate 'SELECT COUNT(sr.field3) FROM ' || v_tablename || ' sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''' into v_recordcount;
  if v_recordcount>0 then begin
  execute immediate 'DELETE FROM table2 sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''';
  --dbms_output.put_line('DELETE FROM table2 sr WHERE TO_CHAR(sr.field1,''yyyy-MM-dd'') = ''' || v_bakdate || '''');
  end;
  end If; 
   
  commit;
  dbms_output.put_line('Ok' || v_recordcount);
end;


2、创建JOB

--1天运行一次，当前运行第一次
begin
  sys.dbms_job.submit(job => :job,
  what => 'data_auto_backup;',
  next_date => sysdate,
  interval => 'sysdate+1');
  commit;
end;
/