Oracle Monitoring – How to Track DML and DDL Daily Events

activity-monitormonitoringoracle

I have the following query to track what tables are modified in the current date:

SELECT TABLE_OWNER, 
       TABLE_NAME, 
       INSERTS,
       UPDATES,
       DELETES,
       TIMESTAMP AS `LAST_CHANGE`
FROM  ALL_TAB_MODIFICATIONS
WHERE TO_CHAR(TIMESTAMP,'DD/MM/YYYY') = TO_CHAR(sysdate,'DD/MM/YYYY') 
ORDER BY LAST_CHANGE DESC;`

Result

enter image description here

As you can see the query works fine , but I want to add more complexity like adding a field "LAST_CHANGE_TYPE" showing what was the last transanction type (addded, deleted or modified) in the lastest process.

It would be good too another query that can be usefull to monitoring my DW.

Like monitor all posible actions , to track what user made for example:

  • drop constraints if its posible specify what kind of constraint
  • Modify Packages Body

When the actions are listed, its necessary to see the user and date of each action.

| Object |Type | Source | Operation Type | User | Date |

| Constraint | PK | Table A | Drop | Scott | 11/11/2011|

| Table | Table | Table B | Alter | HR | 05/05/2002|

Currently open to all suggestions

Best Answer

As Justin said, all_tab_modifications is not a real-time accurate count of the number of modifications to a table since the last time statistics were gathered

I recommend you to track this kind of events, see the following Link

Related Question