Edit D:\app\Administrator\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\online_help\tdpii\tdpii

Edit D:\app\Administrator\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\online_help\tdpii\tdpii_diverge003.htm

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
 "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii" />
<meta http-equiv="Content-Style-Type" content="text/css" />
<meta http-equiv="Content-Script-Type" content="text/javascript" />
<title>Tutorial: Comparing Data in Two Different Databases</title>
<meta name="generator" content="Oracle DARB XHTML Converter (Mode = ohj/ohw) - Version 5.1.1 Build 005" />
<meta name="date" content="2009-06-04T17:1:3Z" />
<meta name="robots" content="noarchive" />
<meta name="doctitle" content="Tutorial: Comparing Data in Two Different Databases" />
<meta name="relnum" content="11g Release 2 (11.2)" />
<meta name="partnum" content="E10703-01" />
<link rel="copyright" href="./dcommon/html/cpyr.htm" title="Copyright" type="text/html" />
<link rel="stylesheet" href="./dcommon/css/blafdoc.css" title="Oracle BLAFDoc" type="text/css" />
<link rel="contents" href="toc.htm" title="Contents" type="text/html" />
<link rel="prev" href="tdpii_diverge002.htm" title="Previous" type="text/html" />
<link rel="next" href="tdpii_diverge004.htm" title="Next" type="text/html" />
<script src="./callback.js" type="text/javascript"></script>
<noscript>Your browser does not support JavaScript. This help page requires JavaScript to render correctly.</noscript>
</head>
<body>
<div class="zz-skip-header"><a href="#BEGIN">Skip Headers</a></div>
<table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%">
<col width="86%" />
<col width="*" />
<tr valign="bottom">
<td align="left"></td>
<td align="center"><a href="tdpii_diverge002.htm"><img src="./dcommon/gifs/leftnav.gif" alt="Previous" /> 
Previous</a>&nbsp;</td>
<td align="center"><a href="tdpii_diverge004.htm"><img src="./dcommon/gifs/rightnav.gif" alt="Next" /> 
Next</a></td>
</tr>
</table>
<a id="BABCDFGB" name="BABCDFGB"></a><a id="TDPII093" name="TDPII093"></a>
<div class="sect1">
<h1>Tutorial: Comparing Data in Two Different Databases<a id="sthref700" name="sthref700"></a><a id="sthref701" name="sthref701"></a><a id="sthref702" name="sthref702"></a><a id="sthref703" name="sthref703"></a></h1>
<a name="BEGIN" id="BEGIN"></a>
This example continues the scenario described in <a href="tdpii_diverge002.htm#BABDBGFC">"Tutorial: Preparing to Compare and Converge Data"</a>. Complete the steps in that topic before continuing.
You can use the <code>CREATE_COMPARISON</code> procedure in the <code>DBMS_COMPARISON</code> package to define a comparison of a shared database object at two different databases. Once the comparison is defined, you can use the <code>COMPARE</code> function in this package to compare the database object specified in the comparison at the current point in time. You can run the <code>COMPARE</code> function multiple times for a specific comparison. Each time you run the function, it results one or more scans of the database objects, and each scan has its own scan ID.
To compare the entire hr.departments table at the ii1.example.com and ii2.example.com databases:&nbsp;
<ol>
<li>
On a command line, open SQL*Plus and connect to the <code>ii1.example.com</code> database as the administrative user who owns the database link created in <a href="tdpii_diverge002.htm#BABDBGFC">"Tutorial: Preparing to Compare and Converge Data"</a>. For example, if <code>SYSTEM</code> user owns the database link, then connect as <code>SYSTEM</code> user:
<pre xml:space="preserve" class="oac_no_warn">
sqlplus system@ii1.example.com
Enter password: password
</pre>
See <a href="topicid:ADMQS0361">Starting SQL*Plus and Connecting to the Database</a> for more information about starting SQL*Plus.
</li>
<li><a id="BABJIHBD" name="BABJIHBD"></a>
Run the <code>CREATE_COMPARISON</code> procedure to create the comparison for the <code>hr.departments</code> table:
<pre xml:space="preserve" class="oac_no_warn">
BEGIN
 DBMS_COMPARISON.CREATE_COMPARISON(
 comparison_name =&gt; 'compare_departments',
 schema_name =&gt; 'hr',
 object_name =&gt; 'departments',
 dblink_name =&gt; 'ii2.example.com');
END;
/
</pre>
Note that the name of the new comparison is <code>compare_departments</code>. This comparison is owned by the user who runs the <code>CREATE_COMPARISON</code> procedure.
</li>
<li><a id="BABFHCBI" name="BABFHCBI"></a>
Run the <code>COMPARE</code> function to compare the <code>hr.departments</code> table at the two databases:
<pre xml:space="preserve" class="oac_no_warn">
SET SERVEROUTPUT ON
DECLARE
 consistent BOOLEAN;
 scan_info DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
 consistent := DBMS_COMPARISON.COMPARE(
 comparison_name =&gt; 'compare_departments',
 scan_info =&gt; scan_info,
 perform_row_dif =&gt; TRUE);
 DBMS_OUTPUT.PUT_LINE('Scan ID: '||scan_info.scan_id);
 IF consistent=TRUE THEN
 DBMS_OUTPUT.PUT_LINE('No differences were found.');
 ELSE
 DBMS_OUTPUT.PUT_LINE('Differences were found.');
 END IF;
END;
/

Scan ID: 1
Differences were found.
 
PL/SQL procedure successfully completed.
</pre>
Specify the name of the comparison created in Step <a href="#BABJIHBD">2</a> for the <code>comparison_name</code> parameter.
The function prints the scan ID for the comparison. The scan ID is important when you are querying data dictionary views for information about the comparison and when you are converging the database objects.
The function also prints whether or not differences were found in the table at the two databases:
<ul>
<li>
If the function prints <code>'No differences were found'</code>, then the table is consistent at the two databases.
</li>
<li>
If the function prints <code>'Differences were found'</code>, then the table has diverged at the two databases.
</li>
</ul>
</li>
<li><a id="BABFBHHJ" name="BABFBHHJ"></a>
Make a note of the scan ID returned by the function in the previous step. In this example, assume the scan ID is <code>1</code>.
</li>
<li>
<a id="sthref705" name="sthref705"></a><a id="sthref706" name="sthref706"></a>If differences were found in Step <a href="#BABFHCBI">3</a>, then run the following query to show the number of differences found:
<pre xml:space="preserve" class="oac_no_warn">
COLUMN OWNER HEADING 'Comparison Owner' FORMAT A16
COLUMN COMPARISON_NAME HEADING 'Comparison Name' FORMAT A20
COLUMN SCHEMA_NAME HEADING 'Schema Name' FORMAT A11
COLUMN OBJECT_NAME HEADING 'Object Name' FORMAT A11
COLUMN CURRENT_DIF_COUNT HEADING 'Differences' FORMAT 9999999
 
SELECT c.OWNER, 
 c.COMPARISON_NAME, 
 c.SCHEMA_NAME, 
 c.OBJECT_NAME, 
 s.CURRENT_DIF_COUNT 
 FROM DBA_COMPARISON c, DBA_COMPARISON_SCAN s
 WHERE c.COMPARISON_NAME = s.COMPARISON_NAME AND
 c.OWNER = s.OWNER AND
 s.SCAN_ID = 1;
</pre>
Specify the scan ID you recorded in Step <a href="#BABFBHHJ">4</a> in the <code>WHERE</code> clause of the query.
The output will be similar to the following:
<pre xml:space="preserve" class="oac_no_warn">
Comparison Owner Comparison Name Schema Name Object Name Differences
---------------- -------------------- ----------- ----------- -----------
SYSTEM COMPARE_DEPARTMENTS HR DEPARTMENTS 3
</pre></li>
<li>
<a id="sthref707" name="sthref707"></a><a id="sthref708" name="sthref708"></a><a id="sthref709" name="sthref709"></a>To see which rows were different in the database object being compared, run the following query:
<pre xml:space="preserve" class="oac_no_warn">
COLUMN COLUMN_NAME HEADING 'Index Column' FORMAT A15
COLUMN INDEX_VALUE HEADING 'Index Value' FORMAT A15
COLUMN LOCAL_ROWID HEADING 'Local Row Exists?' FORMAT A20
COLUMN REMOTE_ROWID HEADING 'Remote Row Exists?' FORMAT A20
 
SELECT c.COLUMN_NAME,
 r.INDEX_VALUE, 
 DECODE(r.LOCAL_ROWID,
 NULL, 'No',
 'Yes') LOCAL_ROWID,
 DECODE(r.REMOTE_ROWID,
 NULL, 'No',
 'Yes') REMOTE_ROWID
 FROM DBA_COMPARISON_COLUMNS c,
 DBA_COMPARISON_ROW_DIF r,
 DBA_COMPARISON_SCAN s
 WHERE c.COMPARISON_NAME = 'COMPARE_DEPARTMENTS' AND
 r.SCAN_ID = s.SCAN_ID AND
 s.PARENT_SCAN_ID = 1 AND
 r.STATUS = 'DIF' AND
 c.INDEX_COLUMN = 'Y' AND
 c.COMPARISON_NAME = r.COMPARISON_NAME AND
 c.OWNER = r.OWNER
 ORDER BY r.INDEX_VALUE;
</pre>
In the <code>WHERE</code> clause, specify the name of the comparison and the scan ID for the comparison. In this example, the name of the comparison is <code>compare_departments</code> and the scan ID is <code>1</code>.
The output will be similar to the following:
<pre xml:space="preserve" class="oac_no_warn">
Index Column Index Value Local Row Exists? Remote Row Exists?
--------------- --------------- -------------------- --------------------
DEPARTMENT_ID 10 Yes Yes
DEPARTMENT_ID 270 Yes No
DEPARTMENT_ID 280 No Yes
</pre>
This output shows the index column for the table being compared and the index value for each row that is different in the shared database object. In this example, the index column is the primary key column for the <code>hr.departments</code> table (<code>department_id</code>). The output also shows the type of difference for each row:
<ul>
<li>
If <code>Local</code> <code>Row</code> <code>Exists?</code> and <code>Remote</code> <code>Row</code> <code>Exists?</code> are both <code>Yes</code> for a row, then the row exists in both instances of the database object, but the data in the row is different.
</li>
<li>
If <code>Local</code> <code>Row</code> <code>Exists?</code> is <code>Yes</code> and <code>Remote</code> <code>Row</code> <code>Exists?</code> is <code>No</code> for a row, then the row exists in the local database object but not in the remote database object.
</li>
<li>
If <code>Local</code> <code>Row</code> <code>Exists?</code> is <code>No</code> and <code>Remote</code> <code>Row</code> <code>Exists?</code> is <code>Yes</code> for a row, then the row exists in the remote database object but not in the local database object.
</li>
</ul>
</li>
</ol>
<div class="helpinfonotealso">
<h2>Related Topics</h2>
<a href="tdpii_diverge001.htm#BABBAFCB">About Comparing and Converging Data in Different Databases</a>
<a href="tdpii_diverge004.htm#BABFHACE">Tutorial: Converging Divergent Data</a>
<a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=ARPLS868','newWindow').focus()">Oracle Database PL/SQL Packages and Types Reference</a> for detailed information about the <code>DBMS_COMPARISON</code> package
<a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=STREP146','newWindow').focus()">Oracle Streams Replication Administrator's Guide</a> for information about using the advanced features of the <code>DBMS_COMPARISON</code> package
</div>
</div>


<div class="footer">
<table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%">
<col width="86%" />
<col width="*" />
<tr>
<td align="left">Copyright&nbsp;&copy;&nbsp;2007, 2009,&nbsp;Oracle&nbsp;and/or&nbsp;its&nbsp;affiliates.&nbsp;All&nbsp;rights&nbsp;reserved. 
<a href="./dcommon/html/cpyr.htm">Legal Notices</a></td>
<td align="center"><a href="tdpii_diverge002.htm"><img src="./dcommon/gifs/leftnav.gif" alt="Previous" /> 
Previous</a>&nbsp;</td>
<td align="center"><a href="tdpii_diverge004.htm"><img src="./dcommon/gifs/rightnav.gif" alt="Next" /> 
Next</a></td>
</tr>
</table>
</div>

</body>
</html>

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de