All about Database Administration, Tips & Tricks

I have got a request from the team asking to get the DML statements that ran against a table for a particular period of time

For this, I have three options
1) If auditing is enabled, get the statements from the audit trail, but the problem will be no bind variable replacement and also the object should be audited

2) Get those from the dba_hist_sql_stat and dba_hist_sql_plan by filtering out the object_name, but again bind variable replacement will be problem and sql text will not be accurate

3) Use log miner, could be very effective but procedural way, still we can get what we want.

Let's see, one by one for the same.
1) Using audit trail method,

	SQL> SELECT username,obj_name,action_name, sql_text
	  FROM   dba_audit_trail
	  WHERE  username = 'OWNER' and obj_name='TEST'
	  ORDER BY timestamp;

	USERNAME OBJ_NAME   ACTION_NAME  SQL_TEXT
	-------- ---------- ------------ -------------------------------------
	OWNER	  TEST 		INSERT  

2) Using dba_hist_sql_stat and dba_hist_sql_plan
No different than this http://db.geeksinsight.com/2013/03/26/quick-question-when-was-the-table-last-modified/

3) Using Log miner, and this post, what it meant to be,
Before starting please keep in mind log miner has this restrictions on tables
Data types LONG and LOB
Simple and nested abstract data types ( ADTs)
Collections (nested tables and VARRAYs)
Object Refs
Index Organized Tables (IOTs)

Steps to perform log miner

	a) Add the archives you want to mine to log miner
	b) Build the data dictionary, if you dont do this, your sql statements will not have original object 
	   names nor the data, since log miner has to replace the object name and 
	   the data values by reading dictionary build content
	
	   There are many ways to build the data dictionary
		a) STORE_IN_FLAT_FILE, Need utl_file_dir set
		b) STORE_IN_REDO_LOGS, Need utl_file_dir set and supplimental loggin must be enabled
		c) DICT_FROM_ONLINE _CATALOG, does not need any, and very easy to use.
	c) Start log miner
	d) End Log miner
	e) Verify the v$logminer_contents to extract your statements

In action:-

Test 1:-
I will use the dict_from_online_catalog only as I dont have utl_file_dir set,

	SQL> execute dbms_logmnr.add_logfile(logfilename=>'/oradata/TESTArchives/TEST_01_.ARC',options => dbms_logmnr.new);

	PL/SQL procedure successfully completed.

	SQL> execute dbms_logmnr.add_logfile(logfilename=>'/oradata/TESTArchives/TEST_02.ARC',options => dbms_logmnr.addfile);

	PL/SQL procedure successfully completed.

	SQL> execute dbms_logmnr.add_logfile(logfilename=>'/oradata/TESTArchives/TEST_03_.ARC',options => dbms_logmnr.addfile);

	PL/SQL procedure successfully completed.

Let's start the log miner without any options and verify the data,

	SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR();

Extract your statements:-

Note:- Do not use obj_name in logminer_contents as it contains only object ID
	SQL> SELECT OPERATION, SQL_REDO, SQL_UNDO, TIMESTAMP
	FROM V$LOGMNR_CONTENTS
	WHERE table_name='&table_name' and
	TIMESTAMP BETWEEN TO_DATE('04-05-2013 03:00:00 am','mm-dd-yyyy hh:mi:ss am') 
	AND TO_DATE('04-05-2013 05:15:00 am','mm-dd-yyyy hh:mi:ss am')
	ORDER BY TIMESTAMP;

	Operation	SQL_REDO 
	-----------	----------
	INSERT		Insert into "UNKNOWN"."OBJ# 81122" values (HEXTORAW('78710405041d1036b79180'),
			NULL,NULL,HEXTORAW('78710405041d1036b79180'));

Have you noticed the insert statement, This is how the output comes you do not provide data dictionary to log miner

a) object named as unknwn and object_id has shown
b) insert values are represented as hexadecimal

Now, stop the log miner and let' start over.

	SQL> EXECUTE DBMS_LOGMNR.STOP_LOGMNR();

Test 2:-

	SQL> execute dbms_logmnr.add_logfile(logfilename=>'/oradata/TEST/Archives/TEST_01_.ARC',options => dbms_logmnr.new);

	PL/SQL procedure successfully completed.

	SQL> execute dbms_logmnr.add_logfile(logfilename=>'/oradata/TEST/Archives/TEST_02.ARC',options => dbms_logmnr.addfile);

	PL/SQL procedure successfully completed.

	SQL> execute dbms_logmnr.add_logfile(logfilename=>'/oradata/TEST/Archives/TEST_03_.ARC',options => dbms_logmnr.addfile);

	PL/SQL procedure successfully completed.

	SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);  --> Provided dict online catalog option

	PL/SQL procedure successfully completed.

Extract the sql statements:-

	SQL> SELECT OPERATION, SQL_REDO, SQL_UNDO, TIMESTAMP
	FROM V$LOGMNR_CONTENTS
	WHERE table_name='&table_name' and
	TIMESTAMP BETWEEN TO_DATE('04-05-2013 03:00:00 am','mm-dd-yyyy hh:mi:ss am') 
	AND TO_DATE('04-05-2013 05:15:00 am','mm-dd-yyyy hh:mi:ss am')
	ORDER BY TIMESTAMP;

	Operation	SQL_REDO 
	-----------	----------
	INSERT		insert into "OWNER"."TEST"("ID","ID_NAME","MSG_CODE","CREATED") values 
		('######','#######',NULL,TO_TIMESTAMP('05-APR-13 03.19.51.689000 AM'));
## Notice and compare with above output, now you have the object name and the values also displayed, i haved replaced the original values with # for safe

Now, stop the log miner

	SQL> EXECUTE DBMS_LOGMNR.STOP_LOGMNR();

Other Queries:-

	SQL> COL table_name FORMAT a20
	
	SQL> SELECT sql_redo FROM SYS.V$LOGMNR_CONTENTS;

Query the V$LOGMNR_CONTENTS view to see changes done by a specific user:

	SQL> SELECT sql_redo, sql_undo FROM V$LOGMNR_CONTENTS    WHERE USERNAME = 'SAASUB' AND TABLE_NAME = 'EVENT';

	SQL> SELECT rownum, sql_redo     FROM V$LOGMNR_CONTENTS      WHERE sql_redo like '%SAABUD%' and            

sql_redo NOT like '%SYS%' and rownum < 10; Query with time stamp

	SQL> SELECT 'Row Number: ' || rownum,            'Date-Time: ' || to_char(timestamp,'DD-MM HH24:MI:SS'),            
	    'Transaction on table: ' ||            table_name || '--->' ||            SUBSTR(sql_redo,1,20)       FROM V$LOGMNR_CONTENTS
	     WHERE sql_redo like '%SAABUD%' AND           sql_redo NOT like '%SYS%' AND            rownum < 10;

Query to determine which tables were modified in the range of time.

	SQL> SELECT seg_owner, seg_name, count(*) AS Hits      
	      FROM V$LOGMNR_CONTENTS WHERE seg_name NOT LIKE '%$'     
	      GROUP BY seg_owner, seg_name;

	SQL> SELECT rownum, to_char(timestamp,'DD-MM HH24:MI:SS')             
	     as "Date/Time",             table_name,             SUBSTR(sql_redo,1,40)        
	     FROM V$LOGMNR_CONTENTS      WHERE sql_redo like '%SAABUD%' AND            sql_redo NOT like '%SYS%';

To determine who drop any objects.

	SQL> SELECT rownum, to_char(timestamp,'DD-MM HH24:MI:SS')
	as "Date/Time",
	table_name,
	SUBSTR(sql_redo,1,40)  
	FROM V$LOGMNR_CONTENTS
	WHERE sql_redo like '%SAABUD%' AND
	sql_redo NOT like '%SYS%' AND
	UPPER(sql_redo) like '%DROP%';