Toad World® Forums

Schema Comparison - False Positives on Views

This is another nuisance that has been lingering for a few versions. My favorite schema has all views in sync with a 11.6 SDXML after an initial re-sync process where I resolved all but the 2 intentional deltas. Subsequent comparisons on 11.6 show 9 additional false positives (comparison on double click are identical) and 12.1 has 15 false deltas reported. “Differs By” is “columns” on all of these. The 2 with “body,columns” deltas are accurate.

Sample (file comparison is identical on both sides):

CREATE OR REPLACE FORCE VIEW NWPS.SPF_PENDINGS
(PEND_SA_ID, IDENTIFIER, REF_SAID, SET_DT, URGE_CD,
STATUS_DT, STATUS, USER_ID, MESSAGE, SPF_USER_ID,
PROCESS_MSG)
AS
SELECT “PEND_SA_ID”,
“IDENTIFIER”,
“REF_SAID”,
“SET_DT”,
“URGE_CD”,
“STATUS_DT”,
“STATUS”,
“USER_ID”,
“MESSAGE”,
“SPF_USER_ID”,
“PROCESS_MSG”
FROM spf_pendings_lk;

These may be validation and synonym/remote object issues. I’ll post reply after I get the objects valid.

Sometimes you won’t see any difference in the view definition, but if you look at the precision and scale in dba_tab_columns, you’ll see a difference (especially when the view is invalid). If you uncheck “Column Prec/Scale” under “Views” in the “Object Types to Compare” tab, these differences will be ignored.

Kind of along this line, but it looks like there’s no way to compare public synonyms on remote objects. These should be private since it is a private link, but it is what it is for now. I’ve compared with a live and a SDXML and I can’t get it to display. I have ~20 missing in my source that are in the destinations. These invalid views are looking for this missing synonym.

Sample:

CREATE OR REPLACE PUBLIC SYNONYM vw_xxx_lk FOR vw_xxx@remote_link;

hmm…How about if you do a separate comparison for those…choose PUBLIC for the schema, and then check only “Synonyms, private, owned by schema”

That does it…along with a few thousand others, but searching for the link syntax is easy enough with the results. Thanks!