5

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

4
  • 2
    What would it mean to you to report on changes to an index? An index is defined on a table so any change to an indexed column of the table would necessitate index maintenance operations. Those operations might affect the index entries of many different rows in the table (a 50/50 split of a leaf block for example). If all_tab_modifications is giving you the information you want about tables, why do you list tables again in your list of objects you're looking for more information about? Apr 24, 2015 at 21:26
  • 1
    The index its just and example what i need its when i report the LAST_CHANGE i want to show what kind of change was made for example data inserted in one table Apr 24, 2015 at 21:30
  • If you want to know what data changed in the table, you'd need to enable auditing or create triggers to track the changes depending on what exactly you're looking for. Apr 24, 2015 at 21:40
  • 1
    You realize that 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, right? The counts are approximate (though generally pretty accurate) and the data is written periodically in batch. It would seem very odd to want to get information like whether the last change was an insert or an update from a view that may not reflect a change to the table that was made half an hour ago. Apr 25, 2015 at 21:27

2 Answers 2

1

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

0

See Oracle Database 11g: Real-Time SQL Monitoring

Real-Time SQL Monitoring, introduced in Oracle Database 11g, provides a very effective way to identify run-time performance problems with resource intensive long-running and parallel SQL statements. Interactive Enterprise Manager screens display details of SQL execution using new, fine-grained SQL statistic that are tracked out-of-the-box with no performance penalty to production systems. Statistics at each step of the execution plan are tracked by key performance metrics, including elapsed time, CPU time, number of reads and writes, I/O wait time and various other wait times. This allows DBAs to analyze SQL execution more deeply than previously possible and decide on the most appropriate tuning strategies for monitored SQL statements.

1
  • very helpfull information Greycom May 12, 2015 at 17:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.