RE: Can't I mine DDL statements from LogMiner?

  • To: <somckit.khemmanivanh@xxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 13 Dec 2005 15:57:05 -0800

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: