Data differences in two schemas

Hi All,

Is there any clean method of getting differences between two Schemas in the database? I saw one post in IOUG some time back but it needs several Oracle 8.x related libraries.

thanks,
Srinivas

Forum: [IOUG-A] Database: Administration, Performance and Networking

Thread: Data difference between two schemas

Author: Indy Johal

Subject: Compare Data Dicrpancies between Two Schemas

URL: http://mondrian.sba.com/forums/Index.cfm?CFApp=21&Message_ID=45021

NOTE: Please visit the address above to continue discussion on this topic.

Replying via e-mail does NOT relay your comments to the discussion

forum.

MESSAGE You can use the following sql as I am using it some time back. The script was posted by Oracle person on some Web site.

/*

The script iterates through all the tables existing in both schemas,

and identifies all the records missed in any of those tables. Record

comparison is based upon table columns common in both schemas; - it is

assumed that those column are of the same datatype (though, their size

might be different).

Database link setup is borrowed from O.Sorland's script.

*/

set serveroutput on

set arraysize 1

set maxdata 32000

begin

dbms_output.enable(999999);

end;

/

undef prex

undef prefx

undef a

undef thisuser

undef b

undef REMOTESCHEMA

undef REMOTEPASSW

undef connstring

undef c

undef todaysdate

variable prefx varchar2(3)

declare

i number ;

j number ;

k number ;

cnt number;

begin

for i in ascii('A') .. ascii('Z') loop

for j in ascii('A') .. ascii('Z') loop

for k in ascii('A') .. ascii('Z') loop

select count(*) into cnt from user_objects where object_name like

chr(i)||chr(j)||chr(k)||'%';

if cnt = 0 then

:prefx := chr(i)||chr(j)||chr(k);

return;

end if;

end loop;

end loop;

end loop;

end;

/

column a new_val prex

set verify off

set linesize 132

set feedback off

select :prefx a from dual;

column b new_val thisuser

select user b from dual;

column c new_val todaysdate

select to_char(sysdate,'DD-MON-YYYY HH24:MI') c from dual;

accept REMOTESCHEMA char prompt 'Enter remote username:'

accept REMOTEPASSW char prompt 'Enter remote password:' hide

accept connstring char prompt 'Enter remote connectstring:'

spool tblsdiff

PROMPT PROMPT PROMPT

PROMPT TABLE DATA DIFFERENCES &todaysdate

PROMPT PROMPT this schema: &thisuser

PROMPT remote schema: &remoteschema.@&connstring

PROMPT

PROMPT PROMPT create database link &prex.lnk connect to &REMOTESCHEMA identified

by &REMOTEPASSW using '&CONNSTRING';

PROMPT PROMPT declare

cursor ctbl is

select table_name from user_tables

intersect

select table_name from user_tables@&prex.lnk

;

tblname varchar2(30);

dblink varchar2(50);

begin

dblink := '&prex.lnk';

open ctbl;

loop

fetch ctbl into tblname;

exit when ctbl%notfound;

declare

cursor ccol is

select cname from col where tblname = tname

and cname <> 'CREDATE'

and cname <> 'CREUSER'

and cname <> 'UPDDATE'

and cname <> 'UPDUSER'

intersect

select cname from col@&prex.lnk where tblname = tname;

colname varchar2(100);

cnames varchar2(4000);

i integer;

query varchar2(4000);

rec_tab dbms_sql.desc_tab;

rec varchar2(4000);

rec_col varchar2(2000);

isdiff boolean;

procedure tbl_diff(query in varchar2, msg in varchar2) is

cursor_name INTEGER;

ret INTEGER;

begin

cursor_name := dbms_sql.open_cursor;

dbms_sql.parse(cursor_name, query, DBMS_SQL.native);

FOR j IN 1..i LOOP

DBMS_SQL.DEFINE_COLUMN(cursor_name, j, rec_col, 30);

END LOOP; ret := dbms_sql.execute(cursor_name);

--dbms_sql.describe_columns(cursor_name, i, rec_tab);

LOOP IF DBMS_SQL.FETCH_ROWS(cursor_name)>0 THEN

• get column values of the row

rec := '';

FOR j IN 1..i LOOP

DBMS_SQL.COLUMN_VALUE(cursor_name, j, rec_col);

rec := rec || SUBSTR(rec_col,1,30) || ',';

END LOOP; if isdiff=false then

dbms_output.put_line('-------------------------------------');

dbms_output.put_line(tblname || msg);

dbms_output.put_line('-------------------------------------');

isdiff:=true;

end if;

dbms_output.put_line(rec);

ELSE

• no more row to copy

EXIT; END IF; END LOOP; dbms_sql.close_cursor(cursor_name);

exception

WHEN OTHERS THEN dbms_sql.close_cursor(cursor_name);

raise;

end;

begin

open ccol;

cnames := '';

i := 0;

loop

fetch ccol into colname;

exit when ccol%notfound;

if i<>0 then

cnames := cnames || ',';

end if;

cnames := cnames || colname;

i := i+1;

end loop;

close ccol;

--EXECUTE IMMEDIATE isdiff := false;

query :=

' select ' || cnames || ' from ' || tblname || '@' || dblink ||

' minus ' ||

' select ' || cnames || ' from ' || tblname ;

tbl_diff(query, ' - local data missed:');

query :=

' select ' || cnames || ' from ' || tblname ||

' minus ' ||

' select ' || cnames || ' from ' || tblname || '@' || dblink ;

tbl_diff(query, ' - remote data missed:');

end;

end loop;

end;

/

drop database link &prex.lnk;

--drop table &prex.comcod;

spool off

set verify on

set feedback on

undef prex

undef prefx

undef a

undef thisuser

undef b

undef REMOTESCHEMA

undef REMOTEPASSW

undef connstring

undef c

undef todaysdate

SO if you want to compare Scott of Database 1 with Scott of Database2 then connect to Scott of Database1 and run the sql Received on Sun Jun 02 2002 - 09:30:29 CDT