业务数据备份功能

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

需求：

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

实现：

1.创建DML备份表空间

create tablespace tbs_dmlbak datafile 'D:\APP\TECH\ORADATA\ORCL\tbs_dmlbak01.dbf' size 100m autoextend  onextent management localsegment 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 tablecreate 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');