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

I would believe that INTERNAL is a good fit for this kind of operation, but I wouldn't assign it all to just DDL. (For example, would creation of a Tablespace be considered DDL, although it would update TS$?)

Yes - you can audit DDL. And by 'this vendor' you mean SAP? The question is this: Would creation of a SYS owned database level trigger and a SYSTEM tablespace located table (for keeping the DDL change audit trail) interfere with SAP or worse, take you out of 'supported' configuration? I would assume not, but then....

Jared may able to address the SAP bit. (if it is SAP).

Cheers,
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: Khemmanivanh, Somckit
[mailto:somckit.khemmanivanh_at_weyerhaeuser.com] Sent: Tuesday, December 13, 2005 4:02 PM To: John Kanagaraj; oracle-l_at_freelists.org Subject: RE: Can't I mine DDL statements from LogMiner?

Thanks for the info.

I do see INTERNAL in the OPERATION column of v$logmnr_contents -- is that a DDL change?

I'm not sure what kind of database triggers would be possible and supported (by the vendor) on this database.

Can you audit DDL?

Thanks!
-----Original Message-----

From: John Kanagaraj [mailto:john.kanagaraj_at_hds.com] Sent: Tuesday, December 13, 2005 3:57 PM To: Khemmanivanh, Somckit; oracle-l_at_freelists.org Subject: 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_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 - 18:18:13 CST