[PL/SQL]保存误删除的存储或表等对象到文件

在开发环境,由于多人共享一个库,而且权限管理不合理的话,很有可能出现你删除了我的存储,我删除了你的表的情况.

一般如果发现的及时,可以使用闪回技术马上闪回相应的表及数据,但是闪回具有一定的局限性:受闪回区大小限制,如果删除对象的时间过长就很有可能闪回不了了。

所以本人写了一个存储和触发器来把DROP掉的对象全部以文件的方式保存起来(相当于做了一个备份),以方便误删除对象之后的恢复.

 

首先,需要创建目录以存放备份的文件

create directory DDIR as 'D:\drop_back';

然后,创建存储过程

CREATE OR REPLACE PROCEDURE PROC_OUTPUTDDL(pTYPE    VARCHAR2,                                           pNAME    VARCHAR2,                                           PMESSAGE VARCHAR2,                                           POWNER   VARCHAR2 DEFAULT '')  AUTHID CURRENT_USER --调用者权限 AS  /*  AUTHOR：Cryking  Created Date: 2012-12-28  Parameter: pTYPE   --  对象类型                pNAME   --  对象名称             PMESSAGE--  需要写入文件的信息             POWNER  --  用户  DESCRIPTION:输出存储、表、函数等对象的DDL语句到文件  */  V_FILE  UTL_FILE.FILE_TYPE;  V_OWNER VARCHAR2(100);BEGIN  IF POWNER IS NULL THEN    SELECT USER INTO V_OWNER FROM DUAL;  ELSE    V_OWNER := POWNER;  END IF;  --如果文件存在就追加内容,不存在就创建  IF DBMS_LOB.FILEEXISTS(BFILENAME('DDIR',                                   'DDL' || TO_CHAR(SYSDATE, 'YYYY_MM_DD') ||                                   '.LOG')) = 1 THEN    V_FILE := UTL_FILE.FOPEN('DDIR',                             'DDL' || TO_CHAR(SYSDATE, 'YYYY_MM_DD') ||                             '.LOG',                             'A');  ELSE    V_FILE := UTL_FILE.FOPEN('DDIR',                             'DDL' || TO_CHAR(SYSDATE, 'YYYY_MM_DD') ||                             '.LOG',                             'W');  END IF;  UTL_FILE.NEW_LINE(V_FILE);  UTL_FILE.PUT_LINE(V_FILE,                    'STARTTIME[' ||                    TO_CHAR(SYSDATE, 'YYYY_MM_DD HH24:MI:SS') || ']:');  UTL_FILE.PUT_LINE(V_FILE, PMESSAGE);  UTL_FILE.PUT_LINE(V_FILE, pTYPE || ' ' || pNAME || '的DDL语句为:');  for x in (SELECT DBMS_METADATA.GET_DDL(pTYPE, pNAME, V_OWNER) A FROM DUAL) LOOP    UTL_FILE.PUT_LINE(V_FILE, X.A);  END LOOP;  UTL_FILE.PUT_LINE(V_FILE,                    'ENDTIME[' || TO_CHAR(SYSDATE, 'YYYY_MM_DD HH24:MI:SS') || ']:');  UTL_FILE.FCLOSE(V_FILE);EXCEPTION  WHEN OTHERS THEN    UTL_FILE.NEW_LINE(V_FILE);    UTL_FILE.PUT_LINE(V_FILE,                      FN_GETNAME || '出现错误：' || SQLCODE || '----' || SQLERRM);    UTL_FILE.FCLOSE(V_FILE);  END PROC_OUTPUTDDL;

 其中FN_GETNAME函数为获取当前存储过程名称,实现见本博客的另一篇文章http://blog.csdn.net/edcvf3/article/details/8050978

 最后,创建系统触发器来捕捉DROP事件,注意操作需要SYSDBA权限

 

CREATE OR REPLACE TRIGGER DROP_DDLAFTER DDL ON database--将DROP掉的对象创建语句保存到文件BEGINIF UPPER(ORA_SYSEVENT)='DROP' THENPROC_OUTPUTDDL(ora_dict_obj_type,ora_dict_obj_name,'终端'||userenv('terminal')||'['||ORA_CLIENT_IP_ADDRESS||']在数据库**'||ora_database_name||'** 上删除了对象'||ora_dict_obj_name,ORA_LOGIN_USER);end if;end;