Hey, I'm trying to figure out if dbms_comparison is useful for me or not.
I'm read through the docs, tried a number of examples, however, all I get is:
"Yep, there were 132 differences."
Ok, so show me the rows that are different!
Can it not do that?
I have the following test:
drop table junk1;
drop table junk2;
create table junk1 ( id number primary key,
name varchar2(10),
name2 varchar2(10) );
insert into junk1
select level lvl,
dbms_random.string('X',8),
dbms_random.string('X',8)
from dual
connect by level <= 100;
create table junk2
as select * from junk1;
alter table junk2 add constraint junk2_pk primary key (id );
update junk2
set name = dbms_random.string('X',7),
name2 = dbms_random.string('X',7)
where id = trunc(dbms_random.value(1,100));
update junk2
set name = dbms_random.string('X',7),
name2 = dbms_random.string('X',7)
where id = trunc(dbms_random.value(1,100));
update junk2
set name = dbms_random.string('X',7),
name2 = dbms_random.string('X',7)
where id = trunc(dbms_random.value(1,100));
commit;
-- SETUP
--
-- PK on both tables
--
-- grant execute on dbms_comparison to gregs;
-- grant select on comparison_scan$ to gregs;
-- grant select on comparison_row_dif$ to gregs;
set serverout on
var lscanid number;
DECLARE
l_scan_info dbms_comparison.comparison_type;
BEGIN
dbms_comparison.drop_comparison ( 'GREGC1' );
dbms_comparison.create_comparison (
comparison_name => 'GREGC1',
schema_name => 'GREGS',
object_name => 'JUNK1',
dblink_name => NULL,
remote_schema_name => 'GREGS',
remote_object_name => 'JUNK2'
);
if ( dbms_comparison.compare (
comparison_name => 'GREGC1',
scan_info => l_scan_info,
perform_row_dif => TRUE
)
)
then
dbms_output.put_line ( 'TRUE: same' );
else
dbms_output.put_line ( 'FALSE: Differences found' );
end if;
dbms_output.put_line ( 'Scan id: ' || l_scan_info.scan_id );
:lscanid := l_scan_info.scan_id;
END;
/
set linesize 2000
SELECT *
FROM user_comparison_scan
where scan_id = :lscanid;
SELECT comparison_name, scan_id, local_rowid, index_value, status,
last_update_time
FROM user_comparison_row_dif
where scan_id = :lscanid;
and that generates the following:
Table dropped.
Table dropped.
Table created.
100 rows created.
Table created.
Table altered.
1 row updated.
1 row updated.
1 row updated.
Commit complete.
FALSE: Differences found
Scan id: 73
PL/SQL procedure successfully completed.
COMPARISON_NAME SCAN_ID PARENT_SCAN_ID ROOT_SCAN_ID STATUS CURRENT_DIF_COUNT INITIAL_DIF_COUNT COUNT_ROWS SCAN_NULLS LAST_UPDATE_TIME
------------------------------ ---------- -------------- ------------ ---------------- ----------------- ----------------- ---------- ---------- -------------------------------
GREGC1 73 73 BUCKET DIF 3 3 100 N 08-JUL-13 10.01.50.674251 AM
1 row selected.
no rows selected.
So, it shows the number of rows, but no details in the 2nd view: user_comparison_row_dif
What am I doing wrong?
(Oracle 11.2.0.1.0)