I have been chasing history in an Oracle database since 1994 when on a job, my client had a problem with a BIG customer. In my travels I have read many a paper on what would be cool, what might be doable, what people actually do, and what Oracle provides for solutions. I have concluded that for the time being the best solution is an audit style whole row copy aka. Tom's Shadow Table.
There will always be people who resist the idea. I sometimes think people like to argue simply because they did not think of the idea, or becuase it is something they have never done before, or because they don't want to change anything, or maybe just because they like to hear the sound of their own voices. The generic solution desribed is an old one. It works. But if you want to talk about practicality, my experience is, it is practically useless for anything real. Tom gave some excellent examples of why.
For those who don't know what a shadow table is, here are two basic shadow tables one might create depending upon need. Obviosly the second has slightly more code involved in maintaining, but it offers correspondingly more fuctionality and ease of use at query time.
--
-- root table
--
create table dept
(
dept_id number not null
, dept_name varchar2(30) not null
)
/
--
-- shadow table example 1
--
create table dept_a
(
dept_id number not null
, dept_name varchar2(30) not null
--
, change_user varchar2(30) not null
, change_date date not null
)
/
--
-- shadow table example 2
--
create table dept_a
(
dept_id number not null
, dept_name varchar2(30) not null
--
, change_user varchar2(255) not null
, start_dt date not null
, end_dt date not null
, operation varchar2(1) not null
--
, transaction_no not null
, seq_no not null
--
, any other crap stuff you might thing would help you do something interesting
)
/
You should consider the following when doing a history implementation:
1) the need to recreate prior results. For example, the ability in July-2009 to recreate the January-2009 month end report. Indeed, you should be able to rerun any query with a date, and get the same answer you got at the time you originally ran the query. This includes selecting of views, and complex queries with many joins/subqueries/function calls.
2) the opportunity to use code generators to create your history tables and any trigger or instead-of-trigger and view components. It is much easier and more reliable to write a generator to create these things. With a correct generator, there are fewer mistakes, everything looks consistent, and it takes no longer to produce 2000 of something than it does to produce 2.
3) more tables does mean more work for you DBA. But hey, that is their job so tell them to stop their whining and get working.
4) how to deal with nay-sayers. Whenever you are doing anything good with Oracle, there will always be someone who steps forward to point out you are wrong, or you will have x,y,z problems, or something else. I always reply with this... "thanks, I appreciate anyone who can help me make a better mouse trap. So what should I do instead? What is your better mousttrap and why is it better?". Usually they have little of offer. Do not ever let someone tell you your stuff sucks, without forcing them to put something of their own on the table too. People who consistently put down working solutions without having an alternative are are useless. If they have nothing to offer as an alternative, then I say whatever you got is infinitely better than what they got.
5) listen to your users, but remember, you are the IT sepcialist. They are suposed to tell you what they need, you are supposed to go get it for them. But they don't do design. They don't tell you how to solve a technical problem. That is your job.
So in the end, my experience says go with the Shadow Table. What you will hear about the generic solution may be true, but for most people the bottom line is, it just won't give you what you want, and finding this out eighteen months down the road will be a bad deal.
Good luck, Kevin