DBMS_COMPARISON

CONN DENILSON/DENILSON@ORCL

SQL> CREATE TABLE teste(x number);

Table created

SQL> CREATE UNIQUE INDEX ix_teste ON teste(x);

Index created

SQL> begin

2 for i in 1..20

3 loop

4 insert into teste(x) values(i);

5 end loop;

6 end;

7 /

PL/SQL procedure successfully completed

SQL> COMMIT;

Commit complete

CONN DENILSON/DENILSON@ORCL2

SQL> CREATE TABLE teste(x number);

Table created

SQL> CREATE UNIQUE INDEX ix_teste ON teste(x);

Index created

SQL> begin

2 for i in 1..20

3 loop

4 insert into teste(x) values(i);

5 end loop;

6 end;

7 /

PL/SQL procedure successfully completed

SQL> COMMIT;

Commit complete

SQL> DELETE FROM teste WHERE x = 10;

1 row deleted

SQL> COMMIT;

Commit complete

CONN DENILSON/DENILSON@ORCL

SQL> ALTER SESSION SET max_dump_file_size=unlimited;

Session altered.

SQL> ALTER SESSION SET timed_statistics = true;

Session altered.

SQL> ALTER SESSION SET statistics_level = ALL ;

Session altered.

SQL> ALTER SESSION SET tracefile_identifier = dbms_comparison ;

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';

Session altered.

SQL>

SQL> exec dbms_comparison.create_comparison(comparison_name=>'compare_test', schema_name=>'DENILSON', object_name=>'TESTE',

dblink_name=>'DENILSON',remote_schema_name=>'DENILSON', remote_object_name=>'TESTE', scan_percent=>90);

PL/SQL procedure successfully completed.

SQL>

SQL> set serveroutput on

SQL>

SQL> DECLARE

2 retval dbms_comparison.comparison_type;

3 BEGIN

4 IF dbms_comparison.compare('compare_test', retval, perform_row_dif=>TRUE) THEN

5 dbms_output.put_line('No Differences!!');

6 ELSE

7 dbms_output.put_line('Differences Found!!');

8 END IF;

9 END;

10 /

Differences Found!!

PL/SQL procedure successfully completed.

SQL>

SQL>

SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

Session altered.

SQL>

SQL> select value from v$diag_info where name = 'Default Trace File';

VALUE

-----------------------------------------------------------------------------------

/u01/app/oracle/diag/rdbms/orcl/orcl/trace/sigfedb_ora_27124_DBMS_COMPARISON.trc

SQL> set linesize 2500

SQL> set null @

SQL> set colsep |

SQL> set linesize 2500

SQL> column COMPARISON_NAME format a16

SQL> INDEX_VALUE format a15

SQL> select * FROM user_comparison_row_dif;

COMPARISON_NAME | SCAN_ID|LOCAL_ROWID |REMOTE_ROWID |INDEX_VALUE |STA|LAST_UPDATE_TIME

----------------|----------|------------------|------------------|---------------|---|-----------------------------

COMPARE_TEST | 4|@ |AAA1lxAAEAAAABlAAJ|10 |DIF|10-JUN-10 09.50.06.785624 AM

exec dbms_comparison.drop_comparison(comparison_name=>'compare_test');

tkprof orcl_ora_27124_DBMS_COMPARISON.trc orcl_ora_4402_DBMS_COMPARISON.txt sys=yes waits=yes sort=execpu