From jonathan@jlcomp.demon.co.uk Wed, 08 Aug 2001 08:31:17 -0700 From: "Jonathan Lewis" Date: Wed, 08 Aug 2001 08:31:17 -0700 Subject: Re: How to locate who dropped a view using log miner? Message-ID: MIME-Version: 1.0 Content-Type: text/plain Do you mean the bit about OBJ$ ? Anything you create in the database will have its name and type stored in OBJ$. This is defined in the database creation script sql.bsq, which happens to include the list of types and codes, as follows: /* 1 = INDEX, 2 = TABLE, 3 = CLUSTER, 4 = VIEW, 5 = SYNONYM, 6 = SEQUENCE, */ /* 7 = PROCEDURE, 8 = FUNCTION, 9 = PACKAGE, 10 = NON-EXISTENT, */ /* 11 = PACKAGE BODY, 12 = TRIGGER, 13 = TYPE, 14 = TYPE BODY, */ /* 19 = TABLE PARTITION, 20 = INDEX PARTITION, 21 = LOB, 22 = LIBRARY, */ /* 23 = DIRECTORY */ Or the bit about columns ? dbms_logminer can be started with the USE_COLMAP option, which makes Oracle read a file called something like logminer.opt from the same directory as it reads the generated dictionary. The file describes a mapping for the five sets of PH columns in log_miner , so you can specify, for example that the PH1 set should map to the sys.obj$.type# column, then query v$logmnr_contents for: seg_name = 'OBJ$' and ph1_name = 'TYPE#' and ph1_redo = 4 Jonathan Lewis Seminars on getting the best out of Oracle Last few places available for Sept 10th/11th See http://www.jlcomp.demon.co.uk/seminar.html -----Original Message----- To: Multiple recipients of list ORACLE-L Date: 08 August 2001 15:32 |Can you elaborate a little bit more please. | |Alex Hillman | |-----Original Message----- |Sent: Wednesday, August 08, 2001 4:11 AM |To: Multiple recipients of list ORACLE-L | | | |Life isn't made any easier by the fact that it is apparently |a deliberate decision the part of Oracle Corp to leave |the undo/redo for various dictionary tables uninterpreted, |so that SQl against TAB$ reads: | |delete from UNKNOWN.objn:4 |where col[1] = hextoraw('c20301') ... | | |I would look only at the OBJ$ code, and set up the |column search section of log miner to search for |the TYPE# column, specifying the value as per the |list in $ORACLE_HOME/rdbms/admin/sql.bsq | | | |Jonathan Lewis | |Seminars on getting the best out of Oracle |Last few places available for Sept 10th/11th |See http://www.jlcomp.demon.co.uk/seminar.html | | | | |-----Original Message----- |To: Multiple recipients of list ORACLE-L |Date: 08 August 2001 03:22 | | ||and you might not find anything. || ||I'll reiterate, logminer in 8.1.5-.7 is version 1.0, anything of |version ||1.0 is full of bugs(and logminer has its own share), and does not |have ||full functionality. || ||here is the short version. || ||a DDL statement like drop table, does do DML against underlying |tables ||that end in $. || ||tab$, col$, etc. || ||In an advanced logminer class i teach, you can SOMETIMES find when a ||table was dropped based on DML against the data dictionary but NOT ||always, why?? || ||well chained/migrated rows is only noted as operation = INTERNAL and |you ||get NO redo or undo SQL. || ||I'm not saying logminer is hard to use, but its a bit more than, || ||start logminer and look for undo SQL in the v$logmnr_contents view. || ||joe || ||-- ||Joe Testa ||Performing Remote DBA Services, need some backup DBA support? ||For Sale: Oracle-dba.com domain, its not going cheap but feel free to ||ask :) ||-- ||Please see the official ORACLE-L FAQ: http://www.orafaq.com ||-- ||Author: Joe Testa || INET: teci@the-testas.net || ||Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 ||San Diego, California -- Public Internet access / Mailing |Lists ||-------------------------------------------------------------------- ||To REMOVE yourself from this mailing list, send an E-Mail message ||to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in ||the message BODY, include a line containing: UNSUB ORACLE-L ||(or the name of mailing list you want to be removed from). You may ||also send the HELP command for other information (like subscribing). | |-- |Please see the official ORACLE-L FAQ: http://www.orafaq.com |-- |Author: Jonathan Lewis | INET: jonathan@jlcomp.demon.co.uk | |Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 |San Diego, California -- Public Internet access / Mailing Lists |-------------------------------------------------------------------- |To REMOVE yourself from this mailing list, send an E-Mail message |to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in |the message BODY, include a line containing: UNSUB ORACLE-L |(or the name of mailing list you want to be removed from). You may |also send the HELP command for other information (like subscribing). |-- |Please see the official ORACLE-L FAQ: http://www.orafaq.com |-- |Author: Hillman, Alex | INET: Alex.Hillman@usmint.treas.gov | |Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 |San Diego, California -- Public Internet access / Mailing Lists |-------------------------------------------------------------------- |To REMOVE yourself from this mailing list, send an E-Mail message |to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in |the message BODY, include a line containing: UNSUB ORACLE-L |(or the name of mailing list you want to be removed from). You may |also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: jonathan@jlcomp.demon.co.uk Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).