Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Activate SQL logging

Re: Activate SQL logging

From: Mark Townsend <markbtownsend_at_comcast.net>
Date: Sat, 12 Feb 2005 21:22:11 -0800
Message-ID: <vdWdnQzhZZ-ZeZPfRVn-jw@comcast.com>

 > Just trying to figure out what an application is doing to the database. I
 > thought there would be some way to activate a log, that would capture the  > SQL. Would mining the redo log and then querying V$LOGMNR_CONTENTS give you what you want - see
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10825/logminer.htm#sthref1988

Note that you can get stuff out of it like the following, which could be easily spooled to a text file if you must have it in text format

(Apologies if this wraps ugly)

SQL> SELECT TIMESTAMP,

      (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,
      SQL_REDO
      FROM V$LOGMNR_CONTENTS
      WHERE SEG_OWNER = 'OE';

TIMESTAMP              XID          SQL_REDO
---------------------  -----------  --------------------------------
13-jan-2003 15:29:31   1.17.2376    update "OE"."PRODUCT_INFORMATION"
                                       set
                                        "WARRANTY_PERIOD" =
                                              TO_YMINTERVAL('+05-00')
                                       where

"PRODUCT_ID" = 3399 and
"WARRANTY_PERIOD" =
TO_YMINTERVAL('+02-00') and ROWID = 'AAAHTKAABAAAY9TAAE'; 13-jan-2003 15:29:34 1.17.2376 insert into
"OE"."PRODUCT_TRACKING"
values
"PRODUCT_ID" = 3399,
"MODIFIED_TIME" =
TO_DATE('13-jan-2003 15:29:34', 'dd-mon-yyyy hh24:mi:ss'),
"OLD_LIST_PRICE" = 815,
"OLD_WARRANTY_PERIOD" =
TO_YMINTERVAL('+02-00'); 13-jan-2003 15:52:43 1.15.1756 update "OE"."PRODUCT_INFORMATION" set "WARRANTY_PERIOD" = TO_YMINTERVAL('+05-00') where "PRODUCT_ID" = 1768 and "WARRANTY_PERIOD" = TO_YMINTERVAL('+02-00') and ROWID = 'AAAHTKAABAAAY9UAAB'; 13-jan-2003 15:52:43 1.15.1756 insert into "OE"."PRODUCT_TRACKING" values
"PRODUCT_ID" = 1768,
"MODIFIED_TIME" =
TO_DATE('13-jan-2003 16:52:43', 'dd-mon-yyyy hh24:mi:ss'),
"OLD_LIST_PRICE" = 715,
"OLD_WARRANTY_PERIOD" =
TO_YMINTERVAL('+02-00');
Received on Sat Feb 12 2005 - 23:22:11 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US