????????????????????????????????????????????????????п????????????????洢?????????????????.

?????????????????????????????????????????????????????????????????????????????????????С?????????????????????????п?????????????

???????????д??????洢???????????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
/*
????:????洢????????????????????????
*/
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;
 


?????????????????????DROP??????????????SYSDBA???


CREATE OR REPLACE TRIGGER DROP_DDL
AFTER DDL ON database
--??DROP????????????浽???
BEGIN
IF UPPER(ORA_SYSEVENT)='DROP' THEN
PROC_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;