Oracle备份表

1、手动备份到表

---创建备份表
create table bosdata.user_info_bak as select * from bosdata.user_info;
---将备份表恢复到主表
truncate table bosdata.user_info;
insert into bosdata.user_info select * from bosdata.user_info_bak;

2、定时自动备份

实际上就是在存储过程中备份，然后用job任务定时去调存储过程

create or replace procedure acct_limit_bak authid current_user is
begin
    execute immediate 'drop table tspdata.acct_bank_card_limit_bak';
    execute immediate 'create table tspdata.acct_bank_card_limit_bak as select * from tspdata.acct_bank_card_limit';
    execute immediate 'CREATE INDEX bank_limit_bak_INDEX1 ON tspdata.acct_bank_card_limit_bak (trans_acctid ASC)';
    execute immediate 'grant select, insert, update, delete on tspdata.acct_bank_card_limit_bak to r_tspdata_dml';
end acct_limit_bak;

注意： authid current_user is很重要，不然在执行Create table时会抛异常：权限不足

先call一下试试存储过程是否可以执行

然后执行JOB定时任务

declare
  v_name number:=99;
begin
  sys.dbms_job.submit(job => v_name,
                      what => 'tspdata.acct_limit_bak();',
                      next_date => sysdate+1/(24*60),---一分钟后开始执行
                      interval => 'trunc(sysdate,''mi'')+2/(24*60)');---两分钟执行一次
  commit;
end;

查看Jobs任务列表和执行情况

SELECT * FROM USER_JOBS;