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.
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
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.