Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle table snapshot
> 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.
Hello
I don't know if there is something that is built-in. But maybe the following scripts helps?
set feedback off
set pages 5000
create table foo (
a number,
b varchar2(20)
);
insert into foo values (1,'one'); insert into foo values (2,'two'); insert into foo values (3,'three'); insert into foo values (4,'four'); insert into foo values (5,'five'); insert into foo values (6,'six'); insert into foo values (7,'seven'); insert into foo values (8,'eight'); insert into foo values (9,'nine');
commit;
create table foo_save as select rowid rowid_,foo.* from foo;
insert into foo values (10,'ten');
update foo set b = 'Nine' where a = 9;
delete from foo where a = 4;
update foo set b = 'Sixty six', a=66 where a=6;
delete from foo where a=2;
insert into foo values(20,'twenty');
update foo set b='Twenty' where a = 20;
select
max(case when c_ = 1 then s_ else 'upd' end) s_, max(case when c_ = 1 then a
else case when r_ = 1 then a else null end end) a, max(case when c_ = 1 then b
else case when r_ = 1 then b else null end end) b, max(case when c_ = 1 then null
else case when r_ = 2 then a else null end end) a_changed, max(case when c_ = 1 then null
else case when r_ = 2 then b else null end end) b_changed
from (
select
row_number() over (partition by rowid_ order by s_ desc) r_,
count(*) over (partition by rowid_) c_,rowid_, s_, a,b from ( select * from (select 'del' s_, foo_save.* from foo_save minus select 'del' s_, rowid rowid_, foo.* from foo) union select * from (select 'add' s_,rowid rowid_, foo.* from foo minus select 'add' s_,foo_save.* from foo_save))
drop table foo;
drop table foo_save;
Rene Nyffenegger
-- no sig todayReceived on Sun Feb 02 2003 - 02:33:23 CST