In this example, assume you manage a direct marketing database and want to determine how productive the customer contacts have been in generating revenue for a 2-week period in January. Assume that you have already created the LogMiner dictionary and added the redo log files that you want to search (as demonstrated in the previous example). Take the following steps:
Start LogMiner and specify a range of times:
EXECUTE DBMS_LOGMNR.START_LOGMNR( -
STARTTIME => TO_DATE('07-Jan-2012 08:30:00','DD-MON-YYYY HH:MI:SS'), -
ENDTIME => TO_DATE('21-Jan-2012 08:45:00','DD-MON-YYYY HH:MI:SS'), -
DICTFILENAME => '/usr/local/dict.ora');
Query the V$LOGMNR_CONTENTS view to determine which tables were modified in the time range you specified, as shown in the following example. (This query filters out system tables that traditionally have a $ in their name.)
SELECT SEG_OWNER, SEG_NAME, COUNT(*) AS Hits FROM V$LOGMNR_CONTENTS WHERE SEG_NAME NOT LIKE '%$' GROUP BY SEG_OWNER, SEG_NAME ORDER BY Hits DESC;
The following data is displayed. (The format of your display may be different.)
SEG_OWNER SEG_NAME Hits --------- -------- ---- CUST ACCOUNT 384 UNIV EXECDONOR 325 UNIV DONOR 234 UNIV MEGADONOR 32 HR EMPLOYEES 12 SYS DONOR 12
The values in the Hits column show the number of times that the named table had an insert, delete, or update operation performed on it during the 2-week period specified in the query. In this example, the cust.account table was modified the most during the specified 2-week period, and the hr.employees and sys.donor tables were modified the least during the same time period.
End the LogMiner session.
Use the DBMS_LOGMNR.END_LOGMNR procedure to finish the LogMiner session properly:
DBMS_LOGMNR.END_LOGMNR( );