Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Can't I mine DDL statements from LogMiner?
Somckit,
DDL statements actually translate into DML against internal tables, so you will not be able to see them in the redolog, ever. The way to *detect* changes (i.e. determine the who/what/when of a DDL change) is using a database level DDL trigger. This is triggered off whenever a DDL statement is executed and using a combination of inbuilts such as ORA_SYSEVENT, ORA_DICT_OBJ_TYPE, ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_OWNER and SYS_CONTEXT values, you can get all the information you want.
Regards,
John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)
Co-Author: Oracle Database 10g Insider Solutions http://www.samspublishing.com/title/0672327910
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Khemmanivanh,
Somckit
Sent: Tuesday, December 13, 2005 2:54 PM
To: oracle-l_at_freelists.org
Subject: Can't I mine DDL statements from LogMiner?
Oracle version is 9206.
I'm tracking DDL changes by running such a query:
select object_name,to_char(last_ddl_time,'YYYY-MM-DD-HH24:MI:SS'),
timestamp from dba_objects
where last_ddl_time > sysdate-4;
Produces some output like the following:
OBJECT_NAME TO_CHAR(LAST_DDL_TI TIMESTAMP
-------------------- ------------------- -------------------
OBJ1 2005-12-11-03:01:16 2001-02-01:01:07:39 OBJ2 2005-12-11-03:01:16 2001-01-29:20:38:59
I then fire up logminer and load all the logs (3 of them) from before and after 3:01.
I then run this query.
select sql_redo,to_char(timestamp, 'YYYY-MM-DD-HH24:MI:SS'), operation, sql_undo from v$logmnr_contents where seg_name like '%OBJ1%';
Produces no output for DDL (there is some for DML though).
I then try this, which produces no output as well.
select sql_redo,to_char(timestamp, 'YYYY-MM-DD-HH24:MI:SS'), operation, sql_undo from v$logmnr_contents where operation = 'DDL'
Am I missing something? I thought Logminer in 9i could report DDL changes?
The other question is, is someone already tracking something similar to this (DDL changes) -- how are you accomplishing this?
Thanks much!!
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Dec 13 2005 - 17:57:16 CST