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

Source: Internet
Author: User


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;
 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.