RE: Can't I mine DDL statements from LogMiner?
- From: "John Kanagaraj" <john.kanagaraj@xxxxxxx>
- To: "Khemmanivanh, Somckit" <somckit.khemmanivanh@xxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 13 Dec 2005 16:18:01 -0800
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@xxxxxxxxxxxxxxxx]
Sent: Tuesday, December 13, 2005 4:02 PM
To: John Kanagaraj; oracle-l@xxxxxxxxxxxxx
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@xxxxxxx]
Sent: Tuesday, December 13, 2005 3:57 PM
To: Khemmanivanh, Somckit; oracle-l@xxxxxxxxxxxxx
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@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!!
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
