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

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

SELECT   FROM all tab modifications

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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 the auditing is enabled on the server  just simply use  SELECT   FROM ALL TAB MODIFICATIONS WHERE TABLE NAME IN

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

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

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 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

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

Examples related to oracle

Examples related to oracle-call-interface