Four years ago, i was in a project that was mainly focused
on coparing table data between two different oracle databases. Actually, the table
data was very important for customer. The customer has over 100 tables for configuration
and these tables have at least 100.000 rows, in total. The Data Model has very
complex relationships between entities. There were lots of bla bla Id and bla
bla description columns J.
Because of the errors that data operators
have done was unacceptable,the customer has set up two different databases with
exact the same configurational data. Two data operators have input the same
configuration data via complex GUI screens, independently. At the end, a pl/sql
procedure has been executed and generates an alarm if there is an inequality between
the configuration tables. By the help of this routine, the customer intends to
avoid undesirable data configuration errors.
For this part of project, we have created a general data
comparison utility that takes some complex queries as input parameters and
compares these resultset. It was something difficult to do all these jobs
on-the-fly. We have spent lots of time
in order to fulfill customer requirements by developing and testing some pl/sql
procedures. The business logic behind these configurational data was also very
hard to understand. ..Finally, we did it J
….
Why i told you this story? A few days ago, i had time to
analyze DBMS_COMPARISON package. I admit that, i like it so much. When i play
with procedures of this package, i remembered my old days J If this package has
been released four years ago, may be, we would fulfill customer needs more
easily.
Follow demostration below:
---base table
CREATE TABLE
YASEMIN.BASE_TABLE(
ID
NUMBER PRIMARY KEY,
DES VARCHAR2(10)
);
INSERT INTO YASEMIN.BASE_TABLE(ID,DES) VALUES(1,'TEST1');
INSERT INTO YASEMIN.BASE_TABLE(ID,DES) VALUES(2,'TEST2');
INSERT INTO YASEMIN.BASE_TABLE(ID,DES) VALUES(3,'TEST3');
---compare table
CREATE TABLE
PAPATYA.COMP_TABLE(
ID NUMBER PRIMARY KEY,
DES VARCHAR2(10)
);
INSERT INTO PAPATYA.COMP_TABLE(ID,DES) VALUES(1,'TEST1');
INSERT INTO PAPATYA.COMP_TABLE(ID,DES) VALUES(4,'TEST4');
COMMIT;
SQL> SELECT * FROM YASEMIN.BASE_TABLE;
ID DES
---------- ----------
1 TEST1
2 TEST2
3 TEST3
SQL> SELECT * FROM PAPATYA.COMP_TABLE;
ID DES
---------- ----------
1 TEST1
4 TEST4
SQL>
--create a comparison for two tables
BEGIN
DBMS_COMPARISON.create_comparison(comparison_name => 'COMPARISON_FOR_BASE_TABLE',
schema_name => 'YASEMIN',
object_name => 'BASE_TABLE',
dblink_name => NULL,
remote_schema_name => 'PAPATYA',
remote_object_name => 'COMP_TABLE');
END;
/
--- make comparison
DECLARE
vt_ComparisonType
dbms_comparison.comparison_type;
vb_Result BOOLEAN;
BEGIN
vb_Result :=
dbms_comparison.compare(comparison_name => 'COMPARISON_FOR_BASE_TABLE',
scan_info =>
vt_ComparisonType,
perform_row_dif => TRUE);
IF vb_Result THEN
dbms_output.put_line('no difs are
found');
ELSE
dbms_output.put_line('difs are found');
END IF;
dbms_output.put_line('scan_id = ' ||
vt_ComparisonType.scan_id);
dbms_output.put_line('loc_rows_merged = ' || vt_ComparisonType.loc_rows_merged);
dbms_output.put_line('rmt_rows_merged = ' || vt_ComparisonType.rmt_rows_merged);
dbms_output.put_line('loc_rows_deleted
= ' ||
vt_ComparisonType.loc_rows_deleted);
dbms_output.put_line('rmt_rows_deleted
= ' ||
vt_ComparisonType.rmt_rows_deleted);
END;
/
difs are found
scan_id = 28
loc_rows_merged = 0
rmt_rows_merged = 0
loc_rows_deleted = 0
rmt_rows_deleted
= 0
--converge, apply diff results
DECLARE
vt_ComparisonType
dbms_comparison.comparison_type;
vb_Result BOOLEAN;
BEGIN
dbms_comparison.converge(comparison_name
=> 'COMPARISON_FOR_BASE_TABLE',
scan_id => 28,
scan_info =>
vt_ComparisonType,
converge_options => dbms_comparison.CMP_CONVERGE_LOCAL_WINS,
perform_commit => FALSE);
dbms_output.put_line('scan_id = ' ||
vt_ComparisonType.scan_id);
dbms_output.put_line('loc_rows_merged = ' || vt_ComparisonType.loc_rows_merged);
dbms_output.put_line('rmt_rows_merged = ' || vt_ComparisonType.rmt_rows_merged);
dbms_output.put_line('loc_rows_deleted
= ' ||
vt_ComparisonType.loc_rows_deleted);
dbms_output.put_line('rmt_rows_deleted
= ' ||
vt_ComparisonType.rmt_rows_deleted);
END;
/
scan_id = 28
loc_rows_merged = 0
rmt_rows_merged = 2
loc_rows_deleted = 0
rmt_rows_deleted
= 1
--data has been fixed, but not committed
SQL> SELECT * FROM YASEMIN.BASE_TABLE;
ID DES
---------- ----------
1 TEST1
2 TEST2
3 TEST3
SQL> SELECT * FROM PAPATYA.COMP_TABLE;
ID DES
---------- ----------
1 TEST1
2 TEST2
3 TEST3
SQL>
SQL>
SQL>
SQL>
SQL> rollback;
Rollback complete
SQL>
SQL> SELECT * FROM YASEMIN.BASE_TABLE;
ID DES
---------- ----------
1 TEST1
2 TEST2
3 TEST3
SQL> SELECT * FROM PAPATYA.COMP_TABLE;
ID DES
---------- ----------
1 TEST1
4 TEST4
SQL>
update YASEMIN.BASE_TABLE set des = 'asdf' where id = 1;
---recheck
DECLARE
vb_Result BOOLEAN;
BEGIN
vb_Result :=
dbms_comparison.recheck(comparison_name => 'COMPARISON_FOR_BASE_TABLE',
scan_id => 28,
perform_row_dif => TRUE);
IF vb_Result THEN
dbms_output.put_line('no difs are
found');
ELSE
dbms_output.put_line('difs are found');
END IF;
END;
/
---finally drop comparison
BEGIN
dbms_comparison.drop_comparison(comparison_name => 'COMPARISON_FOR_BASE_TABLE');
END;
/
DROP TABLE
YASEMIN.BASE_TABLE;
DROP TABLE
PAPATYA.COMP_TABLE;
---other useful tables to check
select * from user_comparison;
select * from
user_comparison_columns;
select * from
user_comparison_row_dif;
select * from
user_comparison_scan;
select * from
user_comparison_scan_summary;
select * from
user_comparison_scan_values;
No comments:
Post a Comment