?洢???????????????????
???????????? ???????[ 2013/1/6 10:05:02 ] ????????
????????????????????????????????????????????????????п????????????????洢?????????????????.
?????????????????????????????????????????????????????????????????????????????????????С?????????????????????????п?????????????
???????????д??????洢???????????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;
??????
???·???
??????????????????
2023/3/23 14:23:39???д?ò??????????
2023/3/22 16:17:39????????????????????Щ??
2022/6/14 16:14:27??????????????????????????
2021/10/18 15:37:44???????????????
2021/9/17 15:19:29???·???????·
2021/9/14 15:42:25?????????????
2021/5/28 17:25:47??????APP??????????
2021/5/8 17:01:11