Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!sjc70.webusenet.com!news.webusenet.com!cyclone.bc.net!newsfeed.stanford.edu!postnews1.google.com!not-for-mail
From: tommynospam@yahoo.com (tommy)
Newsgroups: comp.databases.oracle.server
Subject: Re: Oracle table snapshot
Date: 2 Feb 2003 15:18:58 -0800
Organization: http://groups.google.com/
Lines: 96
Message-ID: <4f0df91b.0302021518.92a0c3c@posting.google.com>
References: <4f0df91b.0302012129.34d529d9@posting.google.com> <b1jn8o$mh6$1$8302bc10@news.demon.co.uk>
NNTP-Posting-Host: 65.147.178.141
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1044227939 20257 127.0.0.1 (2 Feb 2003 23:18:59 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 2 Feb 2003 23:18:59 GMT
Xref: newsfeed1.easynews.com comp.databases.oracle.server:174535
X-Received-Date: Sun, 02 Feb 2003 16:18:18 MST (news.easynews.com)

"Jonathan Lewis" <jonathan@jlcomp.demon.co.uk> wrote in message news:<b1jn8o$mh6$1$8302bc10@news.demon.co.uk>...
> 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.

I forgot about the MINUS.  Incidentally, I'm working in Oracle 8i but
will try the MINUS.  I'll try the others here as well.

Thanks for the great help.