Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle table snapshot

Re: Oracle table snapshot

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 2 Feb 2003 18:13:09 -0000
Message-ID: <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_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:13:09 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US