Re: Activate SQL logging

"MCArch" <spamfree_at_thankyou.net> a écrit dans le message de news:baDPd.5159$QH2.1030_at_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?
|
|

You must have execute privilege on dbms_logmnr. Moreover, iirc, "dbms_logmnr.dict_from_online_catalog" is 9i not 8i.

Have a look at:
"Using LogMiner to Analyze Online and Archived Redo Logs" in Administrator's Guide: http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76956/archredo.htm#12680 DBMS_LOGMNR and DBMS_LOGMNR_D packages in "Supplied PL/SQL Packages Reference": http://download-west.oracle.com/docs/cd/A87860_01/doc/appdev.817/a76936/dbms_log.htm http://download-west.oracle.com/docs/cd/A87860_01/doc/appdev.817/a76936/dbms_loa.htm

Regards
Michel Cadot Received on Sun Feb 13 2005 - 01:43:54 CST