RE: Logminer - analyze table

From: Knight, Jon <jknight_at_concordefs.com>
Date: Tue, 30 Aug 2005 17:40:56 -0500
Message-ID: <17ECCBDCF27C544583F2CAD928F953260221FCCE@memex1.corp.cefs.int>

Peter,
  Thanks. I'll be looking into this tomorrow.

Thanks,
Jon Knight

 -----Original Message-----

From: 	Sliwinski, Peter S. [mailto:PETER.S.SLIWINSKI-CLAUSNER_at_saic.com] 
Sent:	Martes, 30 de Agosto de 2005 05:36 p.m.
To:	jknight_at_concordefs.com; 'oracle-l_at_freelists.org'
Subject:	RE: Logminer - analyze table

Jon,  

conn / as sysdba -- (on 10G R2)

drop table V$LOGMNR_CONTENTS_NEW;

analyze table xxx compute statistics;

alter system switch logfile;
/
/

CREATE OR REPLACE PROCEDURE my_add_logfiles (in_start_time IN DATE) AS   CURSOR c_log IS
    SELECT NAME FROM V$ARCHIVED_LOG
      WHERE FIRST_TIME >= in_start_time;

count pls_integer := 0;
my_option pls_integer := DBMS_LOGMNR.NEW;

BEGIN
  FOR c_log_rec IN c_log
  LOOP
    DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => c_log_rec.name,

                            OPTIONS => my_option);

    my_option := DBMS_LOGMNR.ADDFILE;
    DBMS_OUTPUT.PUT_LINE('Added logfile ' || c_log_rec.name);   END LOOP;
END;
/

EXECUTE my_add_logfiles(in_start_time => '30-Aug-2005 15:20:00');

EXECUTE DBMS_LOGMNR.START_LOGMNR(-

       OPTIONS   => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
                      DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
                      DBMS_LOGMNR.PRINT_PRETTY_SQL);

create table V$LOGMNR_CONTENTS_NEW
as select * from V_$LOGMNR_CONTENTS
/

SELECT
TIMESTAMP, USERNAME, SQL_REDO
FROM V$LOGMNR_CONTENTS_NEW
where
operation = 'DDL'
/
 

TIMESTAMP USERNAME

-------------------- ------------------------------

SQL_REDO

---

---

30-AUG-2005 15:31:57 SYS
drop table V$LOGMNR_CONTENTS_NEW;

30-AUG-2005 15:31:57 SYS
analyze table xxx compute statistics;

..............            

EXECUTE DBMS_LOGMNR.END_LOGMNR(); -----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Knight, Jon
Sent: Monday, August 29, 2005 12:02 PM
To: 'oracle-l_at_freelists.org'
Subject: Logminer - analyze table

Is there any way to use logminer (or anything else) to find out which user executed "analyze table" on a table?

TIA,
Jon Knight

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Aug 30 2005 - 17:43:15 CDT