业务数据备份功能

有了这个功能，妈妈再也不用担心我丢数据拉！(￣︶￣)↗

需求：

在drop、truncate、update、delete操作之前，把需要备份的表或数据备份起来，然后定期删除备份数据。

实现：

1.创建DML备份表空间

create tablespace tbs_dmlbak 
datafile 'D:\APP\TECH\ORADATA\ORCL\tbs_dmlbak01.dbf' 
size 100m autoextend  on
extent management local
segment space management auto;
           

 2.创建DML备份用户

create user DMLBAK identified by oracle default tablespace tbs_dmlbak;
           

 3.赋权

grant resource,connect to dmlbak;
grant drop any table to dmlbak;
grant select any table to dmlbak;
grant update any table to dmlbak;
grant delete any table to dmlbak;
grant insert any table to dmlbak;
grant create any table to dmlbak;
grant create public synonym to dmlbak;
grant create any synonym to dmlbak;
           

 4.创建备份信息表

DMLBAK用户下创建

-- Create table
create table T_BAKTAB_INFO
(
  baktab_name   VARCHAR2(30),
  origtab_name  VARCHAR2(30),
  orig_owner    VARCHAR2(30),
  bak_time      DATE,
  bak_condition VARCHAR2(2000),
  keep          VARCHAR2(1),
  comments      VARCHAR2(2000)
)
tablespace TBS_DMLBAK
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns 
comment on column T_BAKTAB_INFO.baktab_name
  is '备份表名';
comment on column T_BAKTAB_INFO.origtab_name
  is '原表名';
comment on column T_BAKTAB_INFO.orig_owner
  is '原表用户';
comment on column T_BAKTAB_INFO.bak_time
  is '备份时间';
comment on column T_BAKTAB_INFO.bak_condition
  is '条件';
comment on column T_BAKTAB_INFO.keep
  is '是否保存备份表';
comment on column T_BAKTAB_INFO.comments
  is '备注';
           

 5.备份过程

DMLBAK用户下创建

create or replace procedure p_dmlbak(vi_tab_name      in varchar2, --原表名
                                     vi_bak_condition in varchar2 default null)
  is
  --条件
  tab_t_baktab_info t_baktab_info%rowtype; --映射表记录
  v_bak             varchar2(2000); --备份语句
begin
  --生成备份表表名
  select dbms_random.string('a',25) 
    into tab_t_baktab_info.baktab_name
    from dual;
  --获取原表名
  tab_t_baktab_info.origtab_name := upper(vi_tab_name);
  --获取当前SESSION_USER
  select SYS_CONTEXT('USERENV', 'SESSION_USER')
    into tab_t_baktab_info.orig_owner
    from dual;
  --获取操作时间
  tab_t_baktab_info.bak_time := sysdate;
  --获取where条件
  tab_t_baktab_info.bak_condition := vi_bak_condition;
  --是否保留备份表
  tab_t_baktab_info.keep := 'Y';
  --备份表备注
  tab_t_baktab_info.comments := '';

  --备份符合条件的表
  v_bak := 'create table ' || tab_t_baktab_info.baktab_name ||
           ' as select * from ' || tab_t_baktab_info.orig_owner || '.' ||
           upper(vi_tab_name) || ' ' || vi_bak_condition;
  execute immediate v_bak;
  insert into t_baktab_info values tab_t_baktab_info;
  commit;
end p_dmlbak;
           

 6.给使用这个过程的用户赋权

grant execute on p_dmlbak to scott;
           

 7.创建同义词

create or replace public synonym P_DMLBAK for DMLBAK.P_DMLBAK;
           

 8.SCOTT下调用验证

sql>exec P_DMLBAK('t1');