PL/SQL stores objects such as accidentally deleted storage or tables to files

PL/SQL stores objects such as accidentally deleted storage or tables to files in the development environment. because many people share a library and the permission management is unreasonable, it is very likely that you have deleted my storage, I deleted your table. generally, if www.2cto.com is found to be timely, you can use the flash back technology to flash back the corresponding table and data immediately. However, flash back has certain limitations: limited by the size of the flash back area, if you delete an object for a long time, it is likely that the object cannot be flashed back. Therefore, I wrote a storage and trigger to save all the dropped objects as files (equivalent to a backup) to facilitate restoration after objects are deleted by mistake. first, you need to create a directory to store the backup file [SQL] create directory DDIR as 'd: \ drop_back '; then, CREATE a stored PROCEDURE www.2cto.com [SQL] CREATE OR REPLACE PROCEDURE PROC_OUTPUTDDL (pTYPE VARCHAR2, pNAME VARCHAR2, PMESSAGE VARCHAR2, POWNER VARCHAR2 DEFAULT '') AUTHID CURRENT_USER -- caller permission AS/* AUTHOR: cryking Created Date: 2012-12-28 Parameter: pTYPE -- Object Type pNAME -- Object Name PMESSAGE -- Information about the file to be written POWNER -- User DESCRIPTION: output DDL statements for objects such as storage, tables, and functions to the file */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 the file exists, append the content. IF the file does not exist, create IF DBMS_LOB.FILEEXISTS (BFILENAME ('ddir', 'ddls' | 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 statement: '); 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 | 'error: '| SQLCODE |' ---- '| SQLERRM ); UTL_FILE.FCLOSE (V_FILE); END PROC_OUTPUTDDL; The FN_GETNAME function is used to obtain the name of the current stored procedure. http://www.bkjia.com/database/201301/181638.html Finally, create a system trigger to capture the DROP event, note that the operation requires the SYSDBA permission [SQL] CREATE OR REPLACE TRIGGER DROP_DDL AFTER DDL ON database -- save the DROP object creation statement to the file BEGIN IF UPPER (ORA_SYSEVENT) = 'drop' THEN PROC_OUTPUTDDL (ora_dict_obj_type, ora_dict_obj_name, 'terminal' | userenv ('terminal ') | '[' | ORA_CLIENT_IP_ADDRESS | '] The database **' | ora_database_name | '** deletes the object' | ora_dict_obj_name, ORA_LOGIN_USER); end if; end;