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
 
** The opinions and facts contained in this message are entirely mine
and do not reflect those of my employer or customers **

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Khemmanivanh,
Somckit
Sent: Tuesday, December 13, 2005 2:54 PM
To: oracle-l@xxxxxxxxxxxxx
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

Other related posts: