Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I would like to know the total number of operations (DDL and DDL) performed on a table in oracle dabatase since it was created.

Is there a query for it?

share|improve this question
    
dba.stackexchange.com/a/10118/120 –  Sathya May 11 '12 at 8:19
add comment

4 Answers

up vote 3 down vote accepted

If your database has been running in ARCHIVELOG mode and supplemental logging is enabled, you can historically mine the archive logs using Log Miner.

There's a nice demo on this page.

May I ask why you want the total number of operations? The numbers by themselves are meaningless.

share|improve this answer
    
Yes, we are running it in archivelog. I am developing an application that will retrieve this number each day and save it, so we have an statistic of db use. –  Eugenio Cuevas May 11 '12 at 8:40
    
I think you'd be better off defining other metrics, as these don't really mean much. Things like "Table growth in rows/blocks/extents" –  FreshPhilOfSO May 11 '12 at 8:55
    
Yes, that's what we want, but couldn't table growth in rows be (rows after - rows before)? –  Eugenio Cuevas May 11 '12 at 9:07
add comment

No, there is no simple query for this, if you did not setup auditing for this.

You could use auditing to track ddl that changes tables. You could create a procedure that tracks the # of rows affected by dml operations by reading dba_tab_modifications, before the table is analyzed, and store that in an own table to keep a historical view on it. Not for the # of dml. If you have ASH available you could also find out the # of dml on a table and record those statistics in own tables. ASH tends to grow a lot so there is a purging policy on it.

So, the simple answer is no but as is the case with many things in Oracle database, with a little creative thinking, it can be made available.

share|improve this answer
add comment

Based on your comments, it sounds like you are really interested in knowing the growth of the object over time. You could create a table to store this data and as frequently as you want to see changes, insert into the table the results of the this query:

SELECT owner, segment_name, sum(blocks) BlockCount, sysdate GatherDate
FROM dba_extents WHERE segment_name='**Your Table Name Here***' GROUP BY owner, segment_name;

You could then create queries to show differences between different time periods or even graph the results.

share|improve this answer
add comment

SQL> execute dbms_stats.flush_database_monitoring_info;

SQL> select table_owner,table_name,inserts,updates,deletes from dba_tab_modifications ;

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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