Tracking changes to tables in Oracle

On my story about using the minus SQL operator to compare tables, Blake asked what other more efficient methods could be used for tracking changes between tables.

Here are a couple options you could consider to compare table contents instead of the ‘minus’ operator:

Oracle change tables are probably the best for tracking changes internal to the database. You can see an example of them here.

This is what most Oracle data warehouse ETL is built with.

Custom triggers on insert, update and delete can enable similar functionality to change tables (change tables actually work with internal triggers.) You can have the triggers insert your data into another table or just keep some metadata in a separate table to indicate which rows have changed.

Simply adding an activity date column to your tables, populated with a trigger may be enough to track changes and keep databases in sync. If you know, for example, that the database was cloned at midnight on the 1st of the month, then to see what has changed since then you just need to examine all rows with an activity date after that. The activity date can be useful for several other things as well making this a fairly desirable approach.

Blake has an intriguingly eclectic blog himself which is well worth a visit. Thanks for the question Blake!

oracle, database, dba, database development, database administration

Compare table contents with the ‘minus’ SQL operator

Here’s a quick and dirty method to compare the contents of a couple tables:

See all the rows in hr.employees which do not appear in oe.employees

SQL> select * from hr.employees
2 minus
3 select * from oe.employees;

EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
210 Groucho Marx
gm@lifeaftercoffee.com 26-JUL-07 ST_MAN

And the converse can be done by switching the order of the tables:

See all the rows in oe.employees which do not appear in hr.employees

SQL> select * from oe.employees
2 minus
3 select * from hr.employees;

EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
207 Jon Emmons
jon@lifeaftercoffee.com 26-JUL-07 AD_VP

208 Gwen Emmons
gwen@lifeaftercoffee.com 26-JUL-07 AD_VP

This can be very useful if you need to, say, determine what has changed between two different databases. I have been using this over a database link recently to compare tables in a development instance to the tables in a production instance.

The caveats: The two tables must have the exact same column layout. If they are close but not exact you should still be able to make this work by specifying all the fields which are the same instead of ‘*’.

If you want to exclude a field (like an activity date) you will need to specify all the fields you want to include from both tables. The types and order of the fields must be identical between the two select statements.

I describe this as “quick and dirty” because it is. You should not use a query like this in a production database without first looking at how it will affect your database. This will cause full table scans and could kill performance.

oracle, database, dba, database administration, sql

Where’s my cardboard laptop?

Don Burleson points out that Oracle has sent out some Cardboard laptops!

Oracle cardboard laptop


The outside of the laptop which showed up in Andy Armstrong’s mail July 5th read “We’ve taken the idea that the outside world is a dangerous place for unprotected content.” and the inside reads “And shredded it.”

Thanks to Zach for posting the full text of the interior which reads:

“To derive maximum benefit from your business critical content, you need to share it across a wide user base. But the more people who have access to it, the greater the threat of sensitive information leaking to your competitors. That’s just for starters; content proliferation also raises the risk of regulatory non-compliance and escalating management costs. You know you can’t live without your information, but you’d be forgiven for wondering how to live with it.

Oracle’s recently acquired Information Rights Management solution can help. A key component of our Document and Records Management portfolio, it enables you to share your information when and with whom you want – without fear of the outside world.

But it doesn’t stop there. Should the worst happen – and your laptop falls into unsafe hands – we can even scamble your content before anyone works out how to access it.

We’ll be in touch shortly with more details of how to shred your content management worries.”

So what’s the story? What bandwagon is Oracle getting on here? Only time will tell. Burleson thinks it may be another step in their “unbreakable” theme. I think it may be something with Application Express as a content management system. Something to do with enterprise blogs or wiki or some other web 2.0 kind of content management.

wiki, blog, web, web20, web 2.0, oracle, dba, rdbms, dbms, marketing, laptop

Oracle database 9i desupported as of June 2007

In case you haven’t heard, Oracle database version 9i will be desupported as of June of 2007. That’s right, next month!

If this is the first you’ve heard of this, don’t panic. A Don Burleson points out you should be concerned if your shop is required to be on a fully supported Oracle release, but if that’s not a concern for you then you have a little leeway.

As described in this message from Oracle and metalink note 161818.1 extended support will be available through July of 2010 with the first year of extended support being at no additional cost.

If you don’t have a plan to get onto 10g (preferably 10gR2) it’s time to start getting one together.

oracle, database, database administration, dba

Two Oracle user group events in California this week

Donald Burleson points out two Oracle user group events happening in California this week. There’s the Northern California Oracle Users Group Spring Conference and the Los Angeles Oracle Users Group Spring Conference.

Unfortunately I’m on the wrong coast to take advantage of these, but if you are in or near California check it out. It’s important to take advantage of these conferences when they’re close to home!

dba, database administration, database, oracle, conference