12 Replies Latest reply on May 16, 2020 7:45 PM by flavioc

    SQLDev diff report - identical tables reported as different

    flavioc

      Hi,

      did anyone encounter this problem?

       

      I was checking two tables located on two instances, same db version, same definition, but SQLDev diff report run with the default options (ignore tablespace, storage, match constraints by definition, etc.) marks them with the yellow exclamation mark however the scripts generated for both are identical (copied and pasted into a file merge utility just in case I am blind and they are 100% equal).

       

      This happens with either SQLDev 19.2.1 or 19.4, db version is 12.2.0.1 EE

       

      Any ideas?
      Is this a known problem?

       

      Thank you

      Flavio

        • 1. Re: SQLDev diff report - identical tables reported as different
          thatJeffSmith-Oracle

          please provide a test case/example

          • 2. Re: SQLDev diff report - identical tables reported as different
            flavioc

            diff1.png

            diff2.png

            skipped step 3 and 4, here is how the result looks like

            diff3.png

            I pick the first table in the list and I get the following:

            diff4.png

            If i copy and paste these two scripts in a file merge utility the are identical.

             

            left:

              CREATE TABLE "GAGREA_PARC"."AABLELAB_ESTE_TAB"

               (    "ID_ELAB" NUMBER,

                "SUPE_ESTE" NUMBER,

                "SUPE_ESTE_MISS" NUMBER,

                "SHAPE_ESTE" "PUBLIC"."SDO_GEOMETRY",

                "SHAPE_ESTE_MISS" "PUBLIC"."SDO_GEOMETRY",

                CONSTRAINT "AABLELAB_ESTE_PK" PRIMARY KEY ("ID_ELAB") ENABLE

               );

             

            right:

              CREATE TABLE "GAGREA_PARC"."AABLELAB_ESTE_TAB"

               (    "ID_ELAB" NUMBER,

                "SUPE_ESTE" NUMBER,

                "SUPE_ESTE_MISS" NUMBER,

                "SHAPE_ESTE" "PUBLIC"."SDO_GEOMETRY",

                "SHAPE_ESTE_MISS" "PUBLIC"."SDO_GEOMETRY",

                CONSTRAINT "AABLELAB_ESTE_PK" PRIMARY KEY ("ID_ELAB") ENABLE

               );

             

            There are a few tables marked equal but frankly speaking I can't make out a rule as to why they are seen as identical while others don't.
            I had a suspect about the presence of SDO_GEOMETRY columns but then I saw cases of tables marked different or identical having geometries, so I ran out of ideas.

             

            By the way, this was running on a windows 10 pc with sqldeveloper 19.2.1:

            java version "1.8.0_212"

            Java(TM) SE Runtime Environment (build 1.8.0_212-b10)

            Java HotSpot(TM) 64-Bit Server VM (build 25.212-b10, mixed mode)

             

            but I got exactly the same result with sqldeveloper 19.4 on Mac OS 10.14.6

             

            Thank you

            Flavio

            • 3. Re: SQLDev diff report - identical tables reported as different
              thatJeffSmith-Oracle

              So if I take that table script, and run it in two different schemas, and do a compare, it'll find differences?

              • 4. Re: SQLDev diff report - identical tables reported as different
                flavioc

                Hi Jeff,

                well I don't dare to say it will happen because I am comparing the same table on two distinct instances, I don't know if that makes a big difference, but supposing that it won't, that is they are reported as identical, I stiill would like to know why they are not seen as identical in my specific situation.

                I mean, I need to compare these objects in my environment, if I can't rely on SQLDev's DIFF, I'll need to find a different way of doing it.

                 

                Thank you

                Flavio

                • 5. Re: SQLDev diff report - identical tables reported as different
                  thatJeffSmith-Oracle

                  we're not technically doing the diff, that's coming from the database via the oem lifecycle pack, for which we can access at no additional cost for all of our database users

                  • 6. Re: SQLDev diff report - identical tables reported as different
                    flavioc

                    I am aware that SQLDev is using the DBMS_METADATA.GET_XML API for doing the comparison according to the statements logged.

                    Are you suggesting to open a SR with support for this?

                     

                    Thank you

                    Flavio

                    • 7. Re: SQLDev diff report - identical tables reported as different
                      thatJeffSmith-Oracle

                      if you have a reproducible scenario, yes

                      • 8. Re: SQLDev diff report - identical tables reported as different
                        flavioc

                        I made further testing, with interesting results:

                        Initially I used the script above, the barebone version of the CREATE TABLE.

                         

                        first I created the table in a different schema on the same instance, this time DIFF says they are identical.
                        Then I created the table in another schema on another instance, also this time DIFF says they are identical.

                         

                        Then I compared the newly created table in the last test with the target table in production and this time it says it's different.

                        Then I created the table in another schema in the production database and again it says it's different.

                         

                        Then I dropped the table I created for the last test and generated the object script using SQLDev, so this time the output contained storage clauses and LOB clauses. then I removed the storage clause but kept the LOB clauses which look like these:

                         

                          CREATE TABLE "AABLELAB_ESTE_TAB"

                           (    "ID_ELAB" NUMBER,

                            "SUPE_ESTE" NUMBER,

                            "SUPE_ESTE_MISS" NUMBER,

                            "SHAPE_ESTE" "SDO_GEOMETRY",

                            "SHAPE_ESTE_MISS" "SDO_GEOMETRY",

                             CONSTRAINT "AABLELAB_ESTE_PK" PRIMARY KEY ("ID_ELAB") ENABLE

                           )

                        VARRAY "SHAPE_ESTE"."SDO_ELEM_INFO" STORE AS SECUREFILE LOB

                        VARRAY "SHAPE_ESTE"."SDO_ORDINATES" STORE AS SECUREFILE LOB

                        VARRAY "SHAPE_ESTE_MISS"."SDO_ELEM_INFO" STORE AS SECUREFILE LOB

                        VARRAY "SHAPE_ESTE_MISS"."SDO_ORDINATES" STORE AS SECUREFILE LOB

                        ;

                         

                        This time DIFF reports the two tables as IDENTICAL.

                        If I omit the LOB clauses again the two tables are DIFFERENT.

                         

                        That's it

                        Flavio

                        • 9. Re: SQLDev diff report - identical tables reported as different
                          andreml

                          Hi Flavio,

                          When you say: „If I omit the LOB clauses again the two tables are DIFFERENT."

                          This means you provide the CREATE with less specific information/setting, right?

                          Just a thought: Have you compared the default settings for the different schemas?

                          Maybe there are some different default settings that take action when objects are created?

                          Regards

                          Andre

                          • 10. Re: SQLDev diff report - identical tables reported as different
                            flavioc

                            Of course there must be *something* like what you say, the problem is that DIFF marks them as DIFFERENT but then it fails to report WHERE is the difference. The scripts in the left and right panes are identical. The script that should align the two objects is empty.
                            If the difference lies at some level in the storage or LOB storage clause it's wrong anyway because I specified to ignore the storage clause.

                             

                            Regards,

                            Flavio

                            • 11. Re: SQLDev diff report - identical tables reported as different
                              andreml

                              Hi Flavio,

                              That's certainly no consolation to you now.

                              But such things also happen with TOAD.

                              Again, objects are (sometimes) displayed as different and when you try to see the differences, you get a message that no differences were detected.

                              Strange things happens:-)

                              So this seems to be a complicated matter in general...

                              But, perhaps SQL Developer will soon be able to do better than TOAD...

                              Regards

                              Andre

                              • 12. Re: SQLDev diff report - identical tables reported as different
                                flavioc

                                Thank you for your reply, Andre.

                                I'll try to work out some other fashion of doing the comparison given my specific requirements which might be unsuitable for a generic situation but they could still get the job done in my case.

                                 

                                Regards,

                                Flavio