It can use this package for several reason: if checking or replicatiion between production and test databases data etc.
For all scan modes to be supported by the DBMS_COMPARISON package, the database objects must have one of the following types of indexes:
* single-column index on a number, timestamp, interval, or DATE datatype column
* A composite index that only includes number, timestamp, interval, or DATE datatype columns. Each column in the composite index must either have a NOT NULL constraint or be part of the primary key.
The DBMS_COMPARISON package supports all Oracle Data Types (VARCHAR2, NVARCHAR2, NUMBER, FLOAT, DATE, TIMESTAMP etc) except the following:
LONG
LONG RAW
ROWID
UROWID
CLOB
NCLOB
BLOB
BFILE
There are some useful data dictionary views
* DBA/USER_COMPARISON_SCAN
* DBA/USER_COMPARISON_SCAN_SUMMARY
* DBA/USER_COMPARISON_SCAN_VALUES
* DBA/USER_COMPARISON_ROW_DIF
Now,Let us proceed
DECLARE
scan_info dbms_comparison.comparison_type;
scan_id INTEGER;
consistent BOOLEAN;
BEGIN
-- dbms_comparison.drop_comparison(comparison_name => 'empcompare');
dbms_comparison.create_comparison(comparison_name => 'empcompare',
schema_name => 'TEST',
object_name => 'compareclone',
dblink_name => null,
remote_schema_name => 'TEST_REMOTE',
remote_object_name => 'EMP'
);
consistent := dbms_comparison.compare(comparison_name => 'empcompare', scan_info => scan_info);
IF consistent = TRUE THEN
dbms_output.put_line('No differences were found.');
ELSE
dbms_output.put_line('Differences were found.');
END IF;
dbms_comparison.converge(comparison_name => 'empcompare',
scan_id => scan_info.scan_id,
scan_info => scan_info,
converge_options => dbms_comparison.CMP_CONVERGE_REMOTE_WINS);
dbms_output.put_line('Scan ID: ' || scan_info.scan_id);
dbms_output.put_line('Local Rows Merged:' || scan_info.loc_rows_merged);
dbms_output.put_line('Remote Rows Merged:' || scan_info.rmt_rows_merged);
dbms_output.put_line('Local Rows Deleted:' || scan_info.loc_rows_deleted);
dbms_output.put_line('Remote Rows Deleted:' ||scan_info.rmt_rows_deleted);
END;
Now ,create a new table with structure only
17:00:47 TEST_REMOTE> create table emp1 as select * from TEST.emp where 1=2;
after delete run the below procedure
====================================
DECLARE
scan_info dbms_comparison.comparison_type;
scan_id INTEGER;
consistent BOOLEAN;
BEGIN
-- dbms_comparison.drop_comparison(comparison_name => 'empcompare_new');
dbms_comparison.create_comparison(comparison_name => 'empcompare_new',
schema_name => 'TEST',
object_name => 'compareclone',
dblink_name => null,
remote_schema_name => 'TEST_REMOTE',
remote_object_name => 'EMP'
);
consistent := dbms_comparison.compare(comparison_name => 'empcompare_new', scan_info => scan_info);
IF consistent = TRUE THEN
dbms_output.put_line('No differences were found.');
ELSE
dbms_output.put_line('Differences were found.');
END IF;
dbms_comparison.converge(comparison_name => 'empcompare_new',
scan_id => scan_info.scan_id,
scan_info => scan_info,
converge_options => dbms_comparison.CMP_CONVERGE_REMOTE_WINS);
dbms_output.put_line('Scan ID: ' || scan_info.scan_id);
dbms_output.put_line('Local Rows Merged:' || scan_info.loc_rows_merged);
dbms_output.put_line('Remote Rows Merged:' || scan_info.rmt_rows_merged);
dbms_output.put_line('Local Rows Deleted:' || scan_info.loc_rows_deleted);
dbms_output.put_line('Remote Rows Deleted:' ||scan_info.rmt_rows_deleted);
END;
/
DECLARE
*
ERROR at line 1:
ORA-23626: No eligible index on table TEST.compareclone
ORA-06512: at "SYS.DBMS_COMPARISON", line 4793
ORA-06512: at "SYS.DBMS_COMPARISON", line 448
ORA-06512: at line 7
17:04:47 TEST_REMOTE>alter table emp1 add CONSTRAINT "emp_ID_PK" PRIMARY KEY (EMP_CODE);
Table altered.
Elapsed: 00:00:00.03
17:05:46 TEST_REMOTE>DECLARE
scan_info dbms_comparison.comparison_type;
scan_id INTEGER;
consistent BOOLEAN;
BEGIN
-- dbms_comparison.drop_comparison(comparison_name => 'empcompare');
dbms_comparison.create_comparison(comparison_name => 'empcompare',
schema_name => 'TEST',
object_name => 'compareclone',
dblink_name => null,
remote_schema_name => 'TEST_REMOTE',
remote_object_name => 'EMP'
);
consistent := dbms_comparison.compare(comparison_name => 'empcompare', scan_info => scan_info);
IF consistent = TRUE THEN
dbms_output.put_line('No differences were found.');
ELSE
dbms_output.put_line('Differences were found.');
END IF;
dbms_comparison.converge(comparison_name => 'empcompare',
scan_id => scan_info.scan_id,
scan_info => scan_info,
converge_options => dbms_comparison.CMP_CONVERGE_REMOTE_WINS);
dbms_output.put_line('Scan ID: ' || scan_info.scan_id);
dbms_output.put_line('Local Rows Merged:' || scan_info.loc_rows_merged);
dbms_output.put_line('Remote Rows Merged:' || scan_info.rmt_rows_merged);
dbms_output.put_line('Local Rows Deleted:' || scan_info.loc_rows_deleted);
dbms_output.put_line('Remote Rows Deleted:' ||scan_info.rmt_rows_deleted);
END;17:05:47 2 17:05:47 3 17:05:47 4 17:05:47 5 17:05:47 6 17:05:47 7 17:05:47 8 17:05:47 9 17:05:47 10 17:05:47 11 17:05:47 12 17:05:47 13 17:05:47 14 17:05:47 15 17:05:47 16 17:05:47 17 17:05:47 18 17:05:47 19 17:05:47 20 17:05:47 21 17:05:47 22 17:05:47 23 17:05:47 24 17:05:47 25 17:05:47 26 17:05:47 27 17:05:47 28 17:05:47 29 17:05:47 30 17:05:47 31 17:05:47 32 17:05:47 33
17:05:48 34 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.99
17:05:50 TEST_REMOTE>select count(1) from emp1;
COUNT(1)
----------
2339
Elapsed: 00:00:00.00
17:06:09 TEST_REMOTE>delete from emp1 where rownum <13;
12 rows deleted.
Elapsed: 00:00:00.00
17:06:59 TEST_REMOTE>commit;
Commit complete.
Elapsed: 00:00:00.00
17:07:02 TEST_REMOTE>select count(1) from emp1;
COUNT(1)
----------
2327
17:08:13 TEST_REMOTE>DECLARE
scan_info dbms_comparison.comparison_type;
scan_id INTEGER;
consistent BOOLEAN;
BEGIN
-- dbms_comparison.drop_comparison(comparison_name => 'empcompare_new');
dbms_comparison.create_comparison(comparison_name => 'empcompare_new',
schema_name => 'TEST',
object_name => 'compareclone',
dblink_name => null,
remote_schema_name => 'TEST_REMOTE',
remote_object_name => 'EMP'
);
consistent := dbms_comparison.compare(comparison_name => 'empcompare_new', scan_info => scan_info);
IF consistent = TRUE THEN
dbms_output.put_line('No differences were found.');
ELSE
dbms_output.put_line('Differences were found.');
END IF;
dbms_comparison.converge(comparison_name => 'empcompare_new',
scan_id => scan_info.scan_id,
scan_info => scan_info,
converge_options => dbms_comparison.CMP_CONVERGE_REMOTE_WINS);
dbms_output.put_line('Scan ID: ' || scan_info.scan_id);
dbms_output.put_line('Local Rows Merged:' || scan_info.loc_rows_merged);
dbms_output.put_line('Remote Rows Merged:' || scan_info.rmt_rows_merged);
dbms_output.put_line('Local Rows Deleted:' || scan_info.loc_rows_deleted);
dbms_output.put_line('Remote Rows Deleted:' ||scan_info.rmt_rows_deleted);
END;17:11:54 2 17:11:54 3 17:11:54 4 17:11:54 5 17:11:54 6 17:11:54 7 17:11:54 8 17:11:54 9 17:11:54 10 17:11:54 11 17:11:54 12 17:11:54 13 17:11:54 14 17:11:54 15 17:11:54 16 17:11:54 17 17:11:54 18 17:11:54 19 17:11:54 20 17:11:54 21 17:11:54 22 17:11:54 23 17:11:54 24 17:11:54 25 17:11:54 26 17:11:54 27 17:11:54 28 17:11:54 29 17:11:54 30 17:11:54 31 17:11:54 32 17:11:54 33
17:11:55 34 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.48
17:11:57 TEST_REMOTE>select count(*) from emp1;
COUNT(*)
----------
2339
For all scan modes to be supported by the DBMS_COMPARISON package, the database objects must have one of the following types of indexes:
* single-column index on a number, timestamp, interval, or DATE datatype column
* A composite index that only includes number, timestamp, interval, or DATE datatype columns. Each column in the composite index must either have a NOT NULL constraint or be part of the primary key.
The DBMS_COMPARISON package supports all Oracle Data Types (VARCHAR2, NVARCHAR2, NUMBER, FLOAT, DATE, TIMESTAMP etc) except the following:
LONG
LONG RAW
ROWID
UROWID
CLOB
NCLOB
BLOB
BFILE
There are some useful data dictionary views
* DBA/USER_COMPARISON_SCAN
* DBA/USER_COMPARISON_SCAN_SUMMARY
* DBA/USER_COMPARISON_SCAN_VALUES
* DBA/USER_COMPARISON_ROW_DIF
Now,Let us proceed
DECLARE
scan_info dbms_comparison.comparison_type;
scan_id INTEGER;
consistent BOOLEAN;
BEGIN
-- dbms_comparison.drop_comparison(comparison_name => 'empcompare');
dbms_comparison.create_comparison(comparison_name => 'empcompare',
schema_name => 'TEST',
object_name => 'compareclone',
dblink_name => null,
remote_schema_name => 'TEST_REMOTE',
remote_object_name => 'EMP'
);
consistent := dbms_comparison.compare(comparison_name => 'empcompare', scan_info => scan_info);
IF consistent = TRUE THEN
dbms_output.put_line('No differences were found.');
ELSE
dbms_output.put_line('Differences were found.');
END IF;
dbms_comparison.converge(comparison_name => 'empcompare',
scan_id => scan_info.scan_id,
scan_info => scan_info,
converge_options => dbms_comparison.CMP_CONVERGE_REMOTE_WINS);
dbms_output.put_line('Scan ID: ' || scan_info.scan_id);
dbms_output.put_line('Local Rows Merged:' || scan_info.loc_rows_merged);
dbms_output.put_line('Remote Rows Merged:' || scan_info.rmt_rows_merged);
dbms_output.put_line('Local Rows Deleted:' || scan_info.loc_rows_deleted);
dbms_output.put_line('Remote Rows Deleted:' ||scan_info.rmt_rows_deleted);
END;
Now ,create a new table with structure only
17:00:47 TEST_REMOTE> create table emp1 as select * from TEST.emp where 1=2;
after delete run the below procedure
====================================
DECLARE
scan_info dbms_comparison.comparison_type;
scan_id INTEGER;
consistent BOOLEAN;
BEGIN
-- dbms_comparison.drop_comparison(comparison_name => 'empcompare_new');
dbms_comparison.create_comparison(comparison_name => 'empcompare_new',
schema_name => 'TEST',
object_name => 'compareclone',
dblink_name => null,
remote_schema_name => 'TEST_REMOTE',
remote_object_name => 'EMP'
);
consistent := dbms_comparison.compare(comparison_name => 'empcompare_new', scan_info => scan_info);
IF consistent = TRUE THEN
dbms_output.put_line('No differences were found.');
ELSE
dbms_output.put_line('Differences were found.');
END IF;
dbms_comparison.converge(comparison_name => 'empcompare_new',
scan_id => scan_info.scan_id,
scan_info => scan_info,
converge_options => dbms_comparison.CMP_CONVERGE_REMOTE_WINS);
dbms_output.put_line('Scan ID: ' || scan_info.scan_id);
dbms_output.put_line('Local Rows Merged:' || scan_info.loc_rows_merged);
dbms_output.put_line('Remote Rows Merged:' || scan_info.rmt_rows_merged);
dbms_output.put_line('Local Rows Deleted:' || scan_info.loc_rows_deleted);
dbms_output.put_line('Remote Rows Deleted:' ||scan_info.rmt_rows_deleted);
END;
/
DECLARE
*
ERROR at line 1:
ORA-23626: No eligible index on table TEST.compareclone
ORA-06512: at "SYS.DBMS_COMPARISON", line 4793
ORA-06512: at "SYS.DBMS_COMPARISON", line 448
ORA-06512: at line 7
17:04:47 TEST_REMOTE>alter table emp1 add CONSTRAINT "emp_ID_PK" PRIMARY KEY (EMP_CODE);
Table altered.
Elapsed: 00:00:00.03
17:05:46 TEST_REMOTE>DECLARE
scan_info dbms_comparison.comparison_type;
scan_id INTEGER;
consistent BOOLEAN;
BEGIN
-- dbms_comparison.drop_comparison(comparison_name => 'empcompare');
dbms_comparison.create_comparison(comparison_name => 'empcompare',
schema_name => 'TEST',
object_name => 'compareclone',
dblink_name => null,
remote_schema_name => 'TEST_REMOTE',
remote_object_name => 'EMP'
);
consistent := dbms_comparison.compare(comparison_name => 'empcompare', scan_info => scan_info);
IF consistent = TRUE THEN
dbms_output.put_line('No differences were found.');
ELSE
dbms_output.put_line('Differences were found.');
END IF;
dbms_comparison.converge(comparison_name => 'empcompare',
scan_id => scan_info.scan_id,
scan_info => scan_info,
converge_options => dbms_comparison.CMP_CONVERGE_REMOTE_WINS);
dbms_output.put_line('Scan ID: ' || scan_info.scan_id);
dbms_output.put_line('Local Rows Merged:' || scan_info.loc_rows_merged);
dbms_output.put_line('Remote Rows Merged:' || scan_info.rmt_rows_merged);
dbms_output.put_line('Local Rows Deleted:' || scan_info.loc_rows_deleted);
dbms_output.put_line('Remote Rows Deleted:' ||scan_info.rmt_rows_deleted);
END;17:05:47 2 17:05:47 3 17:05:47 4 17:05:47 5 17:05:47 6 17:05:47 7 17:05:47 8 17:05:47 9 17:05:47 10 17:05:47 11 17:05:47 12 17:05:47 13 17:05:47 14 17:05:47 15 17:05:47 16 17:05:47 17 17:05:47 18 17:05:47 19 17:05:47 20 17:05:47 21 17:05:47 22 17:05:47 23 17:05:47 24 17:05:47 25 17:05:47 26 17:05:47 27 17:05:47 28 17:05:47 29 17:05:47 30 17:05:47 31 17:05:47 32 17:05:47 33
17:05:48 34 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.99
17:05:50 TEST_REMOTE>select count(1) from emp1;
COUNT(1)
----------
2339
Elapsed: 00:00:00.00
17:06:09 TEST_REMOTE>delete from emp1 where rownum <13;
12 rows deleted.
Elapsed: 00:00:00.00
17:06:59 TEST_REMOTE>commit;
Commit complete.
Elapsed: 00:00:00.00
17:07:02 TEST_REMOTE>select count(1) from emp1;
COUNT(1)
----------
2327
17:08:13 TEST_REMOTE>DECLARE
scan_info dbms_comparison.comparison_type;
scan_id INTEGER;
consistent BOOLEAN;
BEGIN
-- dbms_comparison.drop_comparison(comparison_name => 'empcompare_new');
dbms_comparison.create_comparison(comparison_name => 'empcompare_new',
schema_name => 'TEST',
object_name => 'compareclone',
dblink_name => null,
remote_schema_name => 'TEST_REMOTE',
remote_object_name => 'EMP'
);
consistent := dbms_comparison.compare(comparison_name => 'empcompare_new', scan_info => scan_info);
IF consistent = TRUE THEN
dbms_output.put_line('No differences were found.');
ELSE
dbms_output.put_line('Differences were found.');
END IF;
dbms_comparison.converge(comparison_name => 'empcompare_new',
scan_id => scan_info.scan_id,
scan_info => scan_info,
converge_options => dbms_comparison.CMP_CONVERGE_REMOTE_WINS);
dbms_output.put_line('Scan ID: ' || scan_info.scan_id);
dbms_output.put_line('Local Rows Merged:' || scan_info.loc_rows_merged);
dbms_output.put_line('Remote Rows Merged:' || scan_info.rmt_rows_merged);
dbms_output.put_line('Local Rows Deleted:' || scan_info.loc_rows_deleted);
dbms_output.put_line('Remote Rows Deleted:' ||scan_info.rmt_rows_deleted);
END;17:11:54 2 17:11:54 3 17:11:54 4 17:11:54 5 17:11:54 6 17:11:54 7 17:11:54 8 17:11:54 9 17:11:54 10 17:11:54 11 17:11:54 12 17:11:54 13 17:11:54 14 17:11:54 15 17:11:54 16 17:11:54 17 17:11:54 18 17:11:54 19 17:11:54 20 17:11:54 21 17:11:54 22 17:11:54 23 17:11:54 24 17:11:54 25 17:11:54 26 17:11:54 27 17:11:54 28 17:11:54 29 17:11:54 30 17:11:54 31 17:11:54 32 17:11:54 33
17:11:55 34 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.48
17:11:57 TEST_REMOTE>select count(*) from emp1;
COUNT(*)
----------
2339
No comments:
Post a Comment