Re: Activate SQL logging

From: MCArch <spamfree_at_thankyou.net>
Date: Sun, 13 Feb 2005 07:17:27 GMT
Message-ID: <baDPd.5159$QH2.1030@newssvr31.news.prodigy.com>

"Mark Townsend" <markbtownsend_at_comcast.net> wrote in message news:vdWdnQzhZZ-ZeZPfRVn-jw_at_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');
>

I can't seem to get the logminer started. I tried following the example in the docs:

SQL> execute dbms_logmnr.start_logmnr( -
> starttime => '11-Feb-2005 18:00:00', -
> endtime => '13-Feb-2005 02:00:00', -
> options => dbms_logmnr.dict_from_online_catalog + -
> dbms_logmnr.continuous_mine) ;

BEGIN dbms_logmnr.start_logmnr( starttime => '11-Feb-2005 18:00:00', endtime =
> '13-Feb-2005 02:00:00', options => dbms_logmnr.dict_from_online_catalog +
db
ms_logmnr.continuous_mine) ; END;

                                      *

ERROR at line 1:
ORA-06550: line 1, column 119:
PLS-00201: identifier 'DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

and:

SQL> execute dbms_logmnr.start_logmnr(); BEGIN dbms_logmnr.start_logmnr(); END;

      *
ERROR at line 1:

ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_LOGMNR.START_LOGMNR' must be declared
ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

What is supposed to declare those identifiers? Received on Sun Feb 13 2005 - 01:17:27 CST