Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!newshosting.com!news-xfer1.atl.newshosting.com!btnet-peer0!btnet!kibo.news.demon.net!news.demon.co.uk!demon!not-for-mail
From: "Jonathan Lewis" <jonathan@jlcomp.demon.co.uk>
Newsgroups: comp.databases.oracle.server
Subject: Re: Oracle table snapshot
Date: Sun, 2 Feb 2003 18:13:09 -0000
Lines: 93
Message-ID: <b1jn8o$mh6$1$8302bc10@news.demon.co.uk>
References: <4f0df91b.0302012129.34d529d9@posting.google.com>
NNTP-Posting-Host: jlcomp.demon.co.uk
X-Trace: news.demon.co.uk 1044209752 23078 158.152.75.41 (2 Feb 2003 18:15:52 GMT)
X-Complaints-To: abuse@demon.net
NNTP-Posting-Date: Sun, 2 Feb 2003 18:15:52 +0000 (UTC)
X-MIMEOLE: Produced By Microsoft MimeOLE V4.72.3110.3
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
Xref: newsfeed1.easynews.com comp.databases.oracle.server:174513
X-Received-Date: Sun, 02 Feb 2003 11:15:12 MST (news.easynews.com)


Sensible Idea

For development only, and if you are running
Oracle 9, you could look at flashback. The
following sort of thing should work.

set serveroutput on size 1000000 format wrapped

variable v_flash number;

begin
 :v_flash := dbms_flashback.get_system_change_number;
 dbms_output.put_line(:v_flash);
end;
/

rem
rem    Do some work on tableX at this point
rem

select * from
(
select rowid rid, col1, col2 ....
from tableX
MINUS
select rowid rid, col1, col2 ...
from tableX as of SCN :v_flash
)
UNION ALL
(
select rowid rid, col1, col2 ....
from tableX  as of SCN :v_flash
MINUS
select rowid rid, col1, col2 ...
from tableX
)
order by rid;
-- might have to be "order by 1".


Note - this will cause an error if you start
experimenting with data less than five minutes
after you create the table - the error will be
something like 'table structure has changed'.
This can be avoided in 9.0 by setting event
10311.


The ability to query a table 'as of SCN' allows you
to query previous versions of the data.  The
combination of MINUS and UNION ALL identifies
all changes.



--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____USA_(FL)_May 2nd


Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


tommy wrote in message
<4f0df91b.0302012129.34d529d9@posting.google.com>...
>New to Oracle and was wondering if Oracle has any kind of built-in
>utility that can take a snapshot of a table or set of tables and
>compare to a second snapshot.  I would use this to determine exactly
>what changed in a table once I run a procedure in development.  So
>many times I have run in to the situation that I write a procedure to
>change one thing and although it changed it, I didn't know that it
>changed 30 others.
>
>Thanks.  Any help would be appreciated.