How to find out when an Oracle table was updated the last time

Question

Can I find out when the last INSERT  UPDATE or DELETE statement was performed on a table in an Oracle database and if so  how   A little background  The Oracle version is 10g  I have a batch application that runs regularly  reads data from a single Oracle table and writes it into a file  I would like to skip this if the data hasn t changed since the last time the job ran   The application is written in C   and communicates with Oracle via OCI  It logs into Oracle with a  normal  user  so I can t use any special admin stuff   Edit  Okay   Special Admin Stuff  wasn t exactly a good description  What I mean is  I can t do anything besides SELECTing from tables and calling stored procedures  Changing anything about the database itself  like adding triggers   is sadly not an option if want to get it done before 2010

User · Answer

Oracle can watch tables for changes and when a change occurs can execute a callback function in PL/SQL or OCI. The callback gets an object that's a collection of tables which changed, and that has a collection of rowid which changed, and the type of action, Ins, upd, del.

So you don't even go to the table, you sit and wait to be called. You'll only go if there are changes to write.

It's called Database Change Notification. It's much simpler than CDC as Justin mentioned, but both require some fancy admin stuff. The good part is that neither of these require changes to the APPLICATION.

The caveat is that CDC is fine for high volume tables, DCN is not.

User · Answer

Since you are on 10g  you could potentially use the ORA ROWSCN pseudocolumn   That gives you an upper bound of the last SCN  system change number  that caused a change in the row   Since this is an increasing sequence  you could store off the maximum ORA ROWSCN that you ve seen and then look only for data with an SCN greater than that   By default  ORA ROWSCN is actually maintained at the block level  so a change to any row in a block will change the ORA ROWSCN for all rows in the block   This is probably quite sufficient if the intention is to minimize the number of rows you process multiple times with no changes if we re talking about  normal  data access patterns   You can rebuild the table with ROWDEPENDENCIES which will cause the ORA ROWSCN to be tracked at the row level  which gives you more granular information but requires a one-time effort to rebuild the table   Another option would be to configure something like Change Data Capture  CDC  and to make your OCI application a subscriber to changes to the table  but that also requires a one-time effort to configure CDC

User · Answer

Could you run a checksum of some sort on the result and store that locally   Then when your application queries the database  you can compare its checksum and determine if you should import it   It looks like you may be able to use the ORA HASH function to accomplish this   Update  Another good resource  10g   s ORA HASH function to determine if two Oracle tables    data are equal

User · Answer

Since you are on 10g  you could potentially use the ORA ROWSCN pseudocolumn   That gives you an upper bound of the last SCN  system change number  that caused a change in the row   Since this is an increasing sequence  you could store off the maximum ORA ROWSCN that you ve seen and then look only for data with an SCN greater than that   By default  ORA ROWSCN is actually maintained at the block level  so a change to any row in a block will change the ORA ROWSCN for all rows in the block   This is probably quite sufficient if the intention is to minimize the number of rows you process multiple times with no changes if we re talking about  normal  data access patterns   You can rebuild the table with ROWDEPENDENCIES which will cause the ORA ROWSCN to be tracked at the row level  which gives you more granular information but requires a one-time effort to rebuild the table   Another option would be to configure something like Change Data Capture  CDC  and to make your OCI application a subscriber to changes to the table  but that also requires a one-time effort to configure CDC

User · Answer

Since you are on 10g  you could potentially use the ORA ROWSCN pseudocolumn   That gives you an upper bound of the last SCN  system change number  that caused a change in the row   Since this is an increasing sequence  you could store off the maximum ORA ROWSCN that you ve seen and then look only for data with an SCN greater than that   By default  ORA ROWSCN is actually maintained at the block level  so a change to any row in a block will change the ORA ROWSCN for all rows in the block   This is probably quite sufficient if the intention is to minimize the number of rows you process multiple times with no changes if we re talking about  normal  data access patterns   You can rebuild the table with ROWDEPENDENCIES which will cause the ORA ROWSCN to be tracked at the row level  which gives you more granular information but requires a one-time effort to rebuild the table   Another option would be to configure something like Change Data Capture  CDC  and to make your OCI application a subscriber to changes to the table  but that also requires a one-time effort to configure CDC

User · Answer

You would need to add a trigger on insert  update  delete that sets a value in another table to sysdate   When you run application  it would read the value and save it somewhere so that the next time it is run it has a reference to compare   Would you consider that  Special Admin Stuff    It would be better to describe what you re actually doing so you get clearer answers

User · Answer

If the auditing is enabled on the server  just simply use  SELECT   FROM ALL TAB MODIFICATIONS WHERE TABLE NAME IN

User · Answer

Could you run a checksum of some sort on the result and store that locally   Then when your application queries the database  you can compare its checksum and determine if you should import it   It looks like you may be able to use the ORA HASH function to accomplish this   Update  Another good resource  10g   s ORA HASH function to determine if two Oracle tables    data are equal

User · Answer

Oracle can watch tables for changes and when a change occurs can execute a callback function in PL/SQL or OCI. The callback gets an object that's a collection of tables which changed, and that has a collection of rowid which changed, and the type of action, Ins, upd, del.

So you don't even go to the table, you sit and wait to be called. You'll only go if there are changes to write.

It's called Database Change Notification. It's much simpler than CDC as Justin mentioned, but both require some fancy admin stuff. The good part is that neither of these require changes to the APPLICATION.

The caveat is that CDC is fine for high volume tables, DCN is not.

User · Answer

Oracle can watch tables for changes and when a change occurs can execute a callback function in PL/SQL or OCI. The callback gets an object that's a collection of tables which changed, and that has a collection of rowid which changed, and the type of action, Ins, upd, del.

So you don't even go to the table, you sit and wait to be called. You'll only go if there are changes to write.

It's called Database Change Notification. It's much simpler than CDC as Justin mentioned, but both require some fancy admin stuff. The good part is that neither of these require changes to the APPLICATION.

The caveat is that CDC is fine for high volume tables, DCN is not.

User · Answer

I m really late to this party but here s how I did it   SELECT SCN TO TIMESTAMP MAX ora rowscn   from myTable    It s close enough for my purposes

User · Answer

How long does the batch process take to write the file   It may be easiest to let it go ahead and then compare the file against a copy of the file from the previous run to see if they are identical

User · Answer

SELECT   FROM all tab modifications

User · Answer

You would need to add a trigger on insert  update  delete that sets a value in another table to sysdate   When you run application  it would read the value and save it somewhere so that the next time it is run it has a reference to compare   Would you consider that  Special Admin Stuff    It would be better to describe what you re actually doing so you get clearer answers

User · Answer

You would need to add a trigger on insert  update  delete that sets a value in another table to sysdate   When you run application  it would read the value and save it somewhere so that the next time it is run it has a reference to compare   Would you consider that  Special Admin Stuff    It would be better to describe what you re actually doing so you get clearer answers

User · Answer

Could you run a checksum of some sort on the result and store that locally   Then when your application queries the database  you can compare its checksum and determine if you should import it   It looks like you may be able to use the ORA HASH function to accomplish this   Update  Another good resource  10g   s ORA HASH function to determine if two Oracle tables    data are equal

User · Answer

How long does the batch process take to write the file   It may be easiest to let it go ahead and then compare the file against a copy of the file from the previous run to see if they are identical

User · Answer

If any one is still looking for an answer they can use Oracle Database Change Notification feature coming with Oracle 10g  It requires CHANGE NOTIFICATION system privilege  You can register listeners when to trigger a notification back to the application

User · Answer

If the auditing is enabled on the server  just simply use  SELECT   FROM ALL TAB MODIFICATIONS WHERE TABLE NAME IN

User · Answer

Please use the below statement  select   from all objects ao where ao OBJECT TYPE    TABLE   and ao OWNER    YOUR SCHEMA NAME

User · Answer

If any one is still looking for an answer they can use Oracle Database Change Notification feature coming with Oracle 10g  It requires CHANGE NOTIFICATION system privilege  You can register listeners when to trigger a notification back to the application

User · Answer

Ask your DBA about auditing   He can start an audit with a simple command like    AUDIT INSERT ON user table   Then you can query the table USER AUDIT OBJECT to determine if there has been an insert on your table since the last export   google for Oracle auditing for more info

User · Answer

Ask your DBA about auditing   He can start an audit with a simple command like    AUDIT INSERT ON user table   Then you can query the table USER AUDIT OBJECT to determine if there has been an insert on your table since the last export   google for Oracle auditing for more info

User · Answer

Oracle can watch tables for changes and when a change occurs can execute a callback function in PL/SQL or OCI. The callback gets an object that's a collection of tables which changed, and that has a collection of rowid which changed, and the type of action, Ins, upd, del.

So you don't even go to the table, you sit and wait to be called. You'll only go if there are changes to write.

It's called Database Change Notification. It's much simpler than CDC as Justin mentioned, but both require some fancy admin stuff. The good part is that neither of these require changes to the APPLICATION.

The caveat is that CDC is fine for high volume tables, DCN is not.

User · Answer

Please use the below statement  select   from all objects ao where ao OBJECT TYPE    TABLE   and ao OWNER    YOUR SCHEMA NAME

User · Answer

How long does the batch process take to write the file   It may be easiest to let it go ahead and then compare the file against a copy of the file from the previous run to see if they are identical

User · Answer

Ask your DBA about auditing   He can start an audit with a simple command like    AUDIT INSERT ON user table   Then you can query the table USER AUDIT OBJECT to determine if there has been an insert on your table since the last export   google for Oracle auditing for more info

User · Answer

You would need to add a trigger on insert  update  delete that sets a value in another table to sysdate   When you run application  it would read the value and save it somewhere so that the next time it is run it has a reference to compare   Would you consider that  Special Admin Stuff    It would be better to describe what you re actually doing so you get clearer answers

User · Answer

I m really late to this party but here s how I did it   SELECT SCN TO TIMESTAMP MAX ora rowscn   from myTable    It s close enough for my purposes

User · Answer

SELECT   FROM all tab modifications

User · Answer

Since you are on 10g  you could potentially use the ORA ROWSCN pseudocolumn   That gives you an upper bound of the last SCN  system change number  that caused a change in the row   Since this is an increasing sequence  you could store off the maximum ORA ROWSCN that you ve seen and then look only for data with an SCN greater than that   By default  ORA ROWSCN is actually maintained at the block level  so a change to any row in a block will change the ORA ROWSCN for all rows in the block   This is probably quite sufficient if the intention is to minimize the number of rows you process multiple times with no changes if we re talking about  normal  data access patterns   You can rebuild the table with ROWDEPENDENCIES which will cause the ORA ROWSCN to be tracked at the row level  which gives you more granular information but requires a one-time effort to rebuild the table   Another option would be to configure something like Change Data Capture  CDC  and to make your OCI application a subscriber to changes to the table  but that also requires a one-time effort to configure CDC

User · Answer

Could you run a checksum of some sort on the result and store that locally   Then when your application queries the database  you can compare its checksum and determine if you should import it   It looks like you may be able to use the ORA HASH function to accomplish this   Update  Another good resource  10g   s ORA HASH function to determine if two Oracle tables    data are equal

User · Answer

How long does the batch process take to write the file   It may be easiest to let it go ahead and then compare the file against a copy of the file from the previous run to see if they are identical

User · Answer

Ask your DBA about auditing   He can start an audit with a simple command like    AUDIT INSERT ON user table   Then you can query the table USER AUDIT OBJECT to determine if there has been an insert on your table since the last export   google for Oracle auditing for more info

[oracle] How to find out when an Oracle table was updated the last time

Examples related to oracle

Examples related to oracle-call-interface