If the tables are large you may want to use oracle's hash function to hash
the entire row into a number then subtract this number from the other
table's hashed value. I had to use this method when subtracting two tables
that were 6,500,000 records in size.
"Joe" <RappaJ_at_nycha.nyc.gov> wrote in message
news:ab8e8633.0303041437.22a21eb5_at_posting.google.com...
> Rene,
>
> Thanks a lot, I like that one. When I get select rights to their
> table, I will try your way. As a test, I tried it on 2 small tables.
> I'll find a way for it to display MINE, then THEIRS directly
> underneath it. As it is below, row3 should follow row1. I'll work it
> out...but THANKS again !!
>
> select
> 'theirs: ' whos, t.ss_number, t.position_number from
> (select ss_number,position_number
> from zztbpsb_assignmentb
> minus
> select ss_number,position_number
> from zztbpsb_assignment) t
> union
> select
> 'mine: ', u.ss_number,u.position_number from
> (select ss_number,position_number
> from zztbpsb_assignment
> minus
> select ss_number,position_number
> from zztbpsb_assignmentb) u
>
> WHOS SS_NUMBER POSITION_NUMBER
> -------- --------------- ---------------
> mine: 012-40-2918 041487
> mine: 017-40-5133 010219
> theirs: 012-40-2911 041487
> theirs: 017-40-5133 010218
>
>
> Thanks again,
> Joe
>
> Rene Nyffenegger <rene.nyffenegger_at_gmx.ch> wrote in message
news:<b42s5p$1qjku2$1_at_ID-82536.news.dfncis.de>...
> > > Hello,
> > >
> > > Can someone please give me some tips on a simple way to compare all
> > > columns and data on 2 tables in separate schemas in the same instance?
> > >
> > > I want to create a comparison report of, for example, MY.TABLE_SAL
> > > -vs- THEIR.TABLE_SAL. I want to make sure that when I load Joe
> > > Millionaires annual salary of $19,000 in MY.TABLE_SAL via sqlldr, they
> > > also take the full $19000 and not just $1900 when they extracted the
> > > data to be loaded into production.
> > >
> > > P.S. I'm not a DBA, so if some kind of DBA privies are needed for some
> > > kind of tool or Data Dictionary table, please let me know.
> > >
> > > Any tips provided is appreciated.
> >
> >
> > Something like
> >
> >
> > select
> > 'theirs: ' whose, t.* from
> > (select * from their.table_sal minus select * from my.table_sal) t
> > union
> > select
> > 'mine: ', u.* from
> > (select * from my.table_sal minus select * from their.tablep) u;
> >
> > hth
> >
> > Rene Nyffenegger
Received on Tue Mar 04 2003 - 23:29:15 CST
