Re: Oracle table snapshot

Hi Jonathan...

Not only do you need 9, you actually need 9i R2 (correct me if I'm wrong) because the 'as of' syntax only became available then.

Nice to know incidentally that you like Flashback, albeit only in development!

Regards
HJR "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:b1jn8o$mh6$1$8302bc10_at_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_at_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.
>
>
Received on Sun Feb 02 2003 - 12:33:44 CST