Problem solve Get help with specific problems with your technologies, process and projects.

Comparing day-to-day changes

We are currently using the Oracle database to run batch processes with the Control-M application. Is there a way I can compare the information of day-to-day changes in the Oracle database?

I'm not familiar with an application called Control-M and don't know if it's something you may have purchased or written in house. So, I don't have any way of knowing what kind of information the application stores as it does daily transaction processing. Also, I don't know which version of Oracle you're using so I'm going to assume 8i. If it's 9i, you could use the flashback query capability and I'll leave that to you to investigate if you're using 9i.

In order to be able to compare changes from one day to the next, you'd need to have a way to maintain an audit trail of data as it was changed. This can be done via triggers that insert rows of data into audit tables as they are changed. You can also use the Log Miner tool to pull activity from the redo logs. For purposes of this answer, let's say that the application has stored logs of activity into audit tables which show changes as they are made.

To get a report on day-to-day changes, you'd need to read the audit table for a given key value and compare it to the current value in the main table.

For instance:

SELECT cur.amount, prev.amount
  FROM the_table cur, the_table_audit prev
 WHERE cur.key_value = prev.key_value ;
This is a very simplistic example, but the idea is that you'd have to write a way to look at the current contents of each table and then compare it to a previous value which would have to be stored somewhere. Unless you're storing this info, there's really not an easy way to make your comparison.

As I mentioned above, Log Miner would be another option, and you can read more about this in the Oracle Administrator's Guide.

For More Information

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.