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: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 2 Feb 2003 08:33:23 GMT
Message-ID: <b1il4j$13lvls$1@ID-82536.news.dfncis.de>

> 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)
  )
)
group by rowid_;

drop table foo;
drop table foo_save;

Rene Nyffenegger

-- 
  no sig today
Received on Sun Feb 02 2003 - 02:33:23 CST

Original text of this message

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