Schema Comparison - False Positives on Views

Toad for Oracle
1

Using Toad version 12.6.0.53 Xpert Edition, Compare Schemas between 2 Oracle 12c databases:

========================

= Objects Which Differ =

========================

 Object Name                       Parent Table (if any)            Differs by

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

View Columns (14)                                                                      

   AFFINE_PARAMETERS              ALL_SDO_3DTXFMS                  datatype

   AFFINE_PARAMETERS              USER_SDO_3DTXFMS                 datatype

   CS_BOUNDS                      CS_SRS                           datatype

   DIMINFO                        ALL_SDO_GEOM_METADATA            datatype

   DIMINFO                        USER_SDO_GEOM_METADATA           datatype

   GEOMETRY                       ALL_SDO_STYLES                   datatype

   GEOMETRY                       DBA_SDO_STYLES                   datatype

   GEOMETRY                       USER_SDO_STYLES                  datatype

   LEGACY_CS_BOUNDS               SDO_COORD_REF_SYSTEM             datatype

   OTHER_TABLE_NAMES              ALL_SDO_GEOR_SYSDATA             datatype

   OTHER_TABLE_NAMES              USER_SDO_GEOR_SYSDATA            datatype

   SDO_ROOT_MBR                   ALL_SDO_INDEX_METADATA           datatype

   SDO_ROOT_MBR                   MY_SDO_INDEX_METADATA            datatype

   SDO_ROOT_MBR                   USER_SDO_INDEX_METADATA          datatype

Views (14)                                                                             

   ALL_SDO_3DTXFMS                                                 columns

   ALL_SDO_GEOM_METADATA                                           columns

   ALL_SDO_GEOR_SYSDATA                                            columns

   ALL_SDO_INDEX_METADATA                                          columns

   ALL_SDO_STYLES                                                  columns

   CS_SRS                                                          columns

   DBA_SDO_STYLES                                                  columns

   MY_SDO_INDEX_METADATA                                           columns

   SDO_COORD_REF_SYSTEM                                            columns

   USER_SDO_3DTXFMS                                                columns

   USER_SDO_GEOM_METADATA                                          columns

   USER_SDO_GEOR_SYSDATA                                           columns

   USER_SDO_INDEX_METADATA                                         columns

   USER_SDO_STYLES                                                 columns

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

The only thing checked in Options is Views (Column Prec/Scale is UNchecked). I looked high and low and can’t figure out why they are reporting as different.

Does any one have any thoughts?

Thank you! :slight_smile:

2

Sometimes if a view needs to be compiled, the datatypes all show in the data dictionary as “UNKNOWN”. So I’d check that first.

If that’s not it, look in dba_tab_columns or all_tab_columns and I think you’ll find something different there.

3

Thanks for the reply, John.

I did force a recompile of one of the views on both databases. I also did check dba_tab_columns and couldn’t find any differences.

It still reports as different…

4

In that case you can probably safely ignore it. Especially if the view text and underlying tables match. I’ll try to reproduce this later on today. It looks like all of your views have SDO_GEOMETRY or similar types…maybe that has something to do with it.

5

Thanks again for the info, John.

I think they are false positives, but if there is some explanation, I’ll sleep better at night :smiley:

6

I’ll let you know what I find.

7

Thank you!!

8

I tried to reproduce the problem using some views that had an SDO_GEOMETRY column, but no differences were detected.

Can you send me a script to build the views (and underlying tables?). You can send to me directly at john.dorlon@quest.com.

Thanks

9

Just to complete this thread:

I suspect I may have been seeing this due to a problem upgrading one of the 2 databases. When I compared one of the 12c bases with our 11g base, the false positives did not appear. Everything about these views was the same on all 3 bases (2-12c and 1 11g), but there were problems upgrading one of the 12c bases.

Thank you for all your help and suggestions, John!