You Asked

Hi,

One of our customer wants to know how to get the DDL DML count happened on application tables with timestamp details before 60 or 90 days.

Please provide some Query to get these details. They dont want to enable Auditing, is that possible without enabling auditing.

Thanks in Advance,

and Chris said...

You can view changes to a table in a period of time using flashback query. The versions between clause allows you to view changes to the table in the defined period:

create table t (
  c1 int
);

exec dbms_lock.sleep ( 5 );

insert into t values ( 1 );
insert into t values ( 2 );

commit;

update t set c1 = 3;

commit;

exec dbms_lock.sleep ( 5 );

delete t;

commit;

exec dbms_lock.sleep ( 5 );

select t.*, versions_operation, versions_starttime, versions_endtime
from   t versions between timestamp sysdate - interval '15' second and systimestamp
order  by versions_starttime;

C1   VERSIONS_OPERATION   VERSIONS_STARTTIME     VERSIONS_ENDTIME       
   2 I                    29-AUG-2018 05.57.16   29-AUG-2018 05.57.19   
   1 I                    29-AUG-2018 05.57.16   29-AUG-2018 05.57.19   
   3 U                    29-AUG-2018 05.57.19   29-AUG-2018 05.57.25   
   3 U                    29-AUG-2018 05.57.19   29-AUG-2018 05.57.25   
   3 D                    29-AUG-2018 05.57.25   <null>                 
   3 D                    29-AUG-2018 05.57.25   <null>

BUT!

This uses undo to show the changes. So you're only guaranteed to be able to view back in time as long as your undo_retention parameter is set to. Which defaults to 900 seconds (15 minutes). So it's unlikely you'll be able to see 60 days back...

If you need a longer retention, you can use Flashback Data Archive. This allows you to configure how long you store history for. You can read more about this at:

https://oracle-base.com/articles/12c/flashback-data-archive-fda-enhancements-12cr1

Or you can roll your own solution.

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Hariharan Senthil pandiyan, August 30, 2018 - 7:47 am UTC

Thanks So much Chris,

:)

user_tab_modifications

Rajeshwaran, Jeyabal, September 03, 2018 - 10:44 am UTC

....
how to get the DDL DML count happened on application tables with timestamp details before 60 or 90 days
....

how about user_tab_modifications?

but that shows the cumulative value for insert/update/delete - but not over the period of time.

something like this.

demo@ORA12C> create table t as select * from all_objects;

Table created.

demo@ORA12C> select table_name,inserts,updates,deletes,truncated
  2  from user_tab_modifications
  3  where table_name ='T';

no rows selected

demo@ORA12C> insert into t select * from t where owner ='SCOTT';

7 rows created.

demo@ORA12C> commit;

Commit complete.

demo@ORA12C> update t set created = sysdate
  2  where owner ='SCOTT';

14 rows updated.

demo@ORA12C> commit;

Commit complete.

demo@ORA12C> delete from t where owner ='DEMO';

361 rows deleted.

demo@ORA12C> commit;

Commit complete.

demo@ORA12C> select table_name,inserts,updates,deletes,truncated
  2  from user_tab_modifications
  3  where table_name ='T';

TABLE_NAME              INSERTS    UPDATES    DELETES TRU
-------------------- ---------- ---------- ---------- ---
T                             7         14        361 NO

demo@ORA12C> truncate table t;

Table truncated.

demo@ORA12C> select table_name,inserts,updates,deletes,truncated
  2  from user_tab_modifications
  3  where table_name ='T';

TABLE_NAME              INSERTS    UPDATES    DELETES TRU
-------------------- ---------- ---------- ---------- ---
T                             7         14      72338 YES

demo@ORA12C>

September 03, 2018 - 4:24 pm UTC

Gathering stats resets the statistics...

create table t as select * from all_objects;

insert into t select * from t where owner ='SCOTT';

commit;

update t set created = sysdate
where owner ='SCOTT';

commit;

delete from t where owner ='DEMO';

commit;

select table_name,inserts,updates,deletes,truncated
from   user_tab_modifications
where  table_name ='T';

TABLE_NAME   INSERTS   UPDATES   DELETES   TRUNCATED   
T                    4         8         0 NO     

exec dbms_stats.gather_table_stats ( user, 'T' ) ;

select table_name,inserts,updates,deletes,truncated
from   user_tab_modifications
where  table_name ='T';

no rows selected

So there's no guarantee this will help.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

user_tab_modifications

More to Explore

Backup/Recovery

Need a query to identify the number of selects, inserts, updates and deletes from an application table

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

user_tab_modifications

More to Explore

Backup/Recovery