You Asked

Hi Tom,

I have already synchronizing data successfully between remote table and local table using DBMS_COMPARISON with scan mode FULL.
But the synchronization performance very slow as of data growth.

When I read the documentation, there is no sample of how to use DBMS_COMPARISON using scan mode CUSTOM,CYCLIC, maybe I miss it.

I can achieve the same thing using MERGE, but I want to do it using DBMS_COMPARISON because I think it is more simple.

my question is:
How I can do the "compare and synchronize" data based on specific date using DBMS_COMPARISON, let say with condition TRUNC(TRADEDATE)=TRUNC(SYSDATE).

Regards,

M Fatkhur

and Connor said...

You can do with a view to limit the rows you want, eg


SQL> create table t1
  2  as select rownum pk, trunc(sysdate)-rownum/1000 date_col, d.* from dba_objects d,
  3    ( select 1 from dual connect by level <= 20 );

Table created.

SQL>
SQL> create table t2 as select * from t1;

Table created.

SQL>
SQL> alter table t1 modify date_col not null;

Table altered.

SQL> alter table t2 modify date_col not null;

Table altered.

SQL>
SQL> create index t1_ix on t1 ( date_col);

Index created.

SQL> create index t2_ix on t2 ( date_col);

Index created.

SQL>
SQL> alter table t1 add primary key ( pk );

Table altered.

SQL> alter table t2 add primary key ( pk );

Table altered.

SQL>
SQL>
SQL> set serverout on
SQL> set timing on
SQL> declare
  2      l_scan_info   dbms_comparison.comparison_type;
  3  begin
  4      dbms_comparison.create_comparison(
  5        comparison_name     => 'COMP1',
  6        schema_name         => user,
  7        object_name         => 'T1',
  8        dblink_name         => null,
  9        remote_object_name  => 'T2' );
 10
 11      if ( dbms_comparison.compare(
 12           comparison_name     => 'COMP1',
 13           scan_info           => l_scan_info ) )
 14      then
 15          dbms_output.put_line( 'TRUE: the same' );
 16      else
 17          dbms_output.put_line( 'FALSE: differences found' );
 18      end if;
 19  end;
 20  /
TRUE: the same

PL/SQL procedure successfully completed.

Elapsed: 00:00:26.24

So a full table to table compare took 26 seconds. Now lets create a couple of views for just today's data

SQL> create or replace view V1 as select * from t1 where date_col > trunc(sysdate);

View created.

Elapsed: 00:00:00.05
SQL> create or replace view V2 as select * from t2 where date_col > trunc(sysdate);

View created.

Elapsed: 00:00:00.01
SQL> set serverout on
SQL> set timing on
SQL> declare
  2      l_scan_info   dbms_comparison.comparison_type;
  3  begin
  4      dbms_comparison.create_comparison(
  5        comparison_name     => 'COMP2',
  6        schema_name         => user,
  7        object_name         => 'V1',
  8        dblink_name         => null,
  9        remote_object_name  => 'V2' );
 10
 11      if ( dbms_comparison.compare(
 12           comparison_name     => 'COMP2',
 13           scan_info           => l_scan_info ) )
 14      then
 15          dbms_output.put_line( 'TRUE: the same' );
 16      else
 17          dbms_output.put_line( 'FALSE: differences found' );
 18      end if;
 19  end;
 20  /
TRUE: the same

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.30
SQL>
SQL>

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

ERROR remote object V2 is not a single table view

M Fatkhur R, November 16, 2016 - 7:56 am UTC

the scenario above success when comparing between local table.
But when V2 select data from remote server, an error occurred "remote object V2 is not a single table view",

This the SQL statement,

create or replace view V2 as select * from t2@remote_link where date_col > trunc(sysdate);

November 19, 2016 - 3:41 am UTC

The objects must be local to each database. The cross-database stuff is handled by dbms_comparison itself, ie

db1
===
create or replace view V1 as select * from local_table;

db2
===
create or replace view V2 as select * from local_table;

db1
===
dbms_comparison.create_comparison(
comparison_name => 'COMP2',
schema_name => user,
object_name => 'V1',
dblink_name => 'db11', <<=====
remote_object_name => 'V2' );

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

ERROR remote object V2 is not a single table view

More to Explore

PL/SQL demos

PL/SQL docs

Synchronize specific data based on date using DBMS_COMPARISON

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

ERROR remote object V2 is not a single table view

More to Explore

PL/SQL demos

PL/SQL docs