Re: Oracle table snapshot

"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.

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. Received on Sun Feb 02 2003 - 17:18:58 CST