5 Replies Latest reply on Aug 7, 2020 10:58 PM by Mike Kutz

    Datbase Diff and Identity Columns

    Mike Kutz

      It really looks like the database diff utility is doing a "diff(2)" on the DBMS_METADATA.GET_DDL() results instead of doing any logical comparison.

       

      Is this correct?

       

      From what I'm seeing:

      • tables with Identity Columns are being flagged as "different" because the sequence used for the Identity value is (naturally) different.
      • (untested) differences in Identity Columns (eg GENERATE ALWAYS, GENERATE BY DEFAUTL [ON NULL]) may not be happening
      • Constraints that are listed in a different order are flagged as different even if the constraints are identical.

       

      MK

        • 1. Re: Datbase Diff and Identity Columns
          thatJeffSmith-Oracle

          do you have Match Constraints by 'Name' or 'Definition' checked?

           

          Both sound like bugs, you should report those to My Oracle Support. The underlying code is coming from EM. At some point we'll probably replace the entire mechanism with our liquibase implementation, but we're not there yet.

          • 2. Re: Datbase Diff and Identity Columns
            Mike Kutz

            By Definition.

             

            I'll do some more testing and post a script for reproducibility.

             

            thatJeffSmith-Oracle wrote:

             

            Both sound like bugs, you should report those to My Oracle Support.

            I'm sure that capability is not included with ATP Free Tier license (or XE license).

             

            MK

            • 3. Re: Datbase Diff and Identity Columns
              thatJeffSmith-Oracle

              yeah, see if switching by name helps (assuming your constraints have the same name)

               

              If you give me a complete test case, two basic schemas demonstrating the issue, i'll test, reproduce, and file the bug for you

              • 4. Re: Datbase Diff and Identity Columns
                Mike Kutz

                Identity Column

                Run on both connections

                create table table_a (
                    table_a_id int GENERATED BY DEFAULT AS IDENTITY
                    ,constraint table_a_pk primary key (table_a_id)
                );
                
                create table table_b (
                    table_b_id int GENERATED BY DEFAULT AS IDENTITY
                    ,constraint table_b_pk primary key (table_b_id)
                );
                
                create table table_ab (
                    table_a_id  int
                    ,table_b_id int
                    ,constraint table_ab_pk primary key (table_a_id, table_b_id)
                    ,constraint table_ab_fk1 foreign key (table_a_id) references table_a (table_a_id)
                    ,constraint table_ab_fk2 foreign key (table_b_id) references table_b (table_b_id)
                );
                

                 

                If you do a Database Diff right now, the script wants you to modify the DEFAULT value (for the Identity column) to a non-existent sequence.

                 

                Step 2) Run this one only on Source

                alter table table_a modify ( table_a_id generated by default on null as identity );
                

                 

                 

                The additional "ON NULL" clause is not picked up by the script. (only the fact that DEFAULT is using a different SEQUENCE name)

                 

                Options Used

                 

                And I compared only the three Table objects.

                 

                Clean up Script

                drop table table_ab;
                drop table table_a;
                drop table table_b;
                

                 

                CONSTRAINT DIFFERENCE

                It looks like the Script shows the changes/no-changes of Constraints correctly.

                Only the DDL diff view is not what I was expecting. (I'll just have to keep that in mind)

                 

                ALTER TABLE "TABLE_AB" DROP CONSTRAINT "TABLE_AB_FK1";
                ALTER TABLE "TABLE_AB" ADD CONSTRAINT "TABLE_AB_FK1" FOREIGN KEY ("TABLE_A_ID") REFERENCES "TABLE_A"("TABLE_A_ID") ON DELETE CASCADE ENABLE;
                

                 

                I hope this helps

                 

                MK

                • 5. Re: Datbase Diff and Identity Columns
                  Mike Kutz

                  thatJeffSmith-Oracle wrote:


                  At some point we'll probably replace the entire mechanism with our liquibase implementation, but we're not there yet.

                  That feature would be great!  Especially for the SQL*Developer Web edition on ATP.

                   

                  Continuous Integration/Continuous Development REST APIs for Oracle Cloud (@customer) system?  yes please!

                   

                  My $0.02

                   

                  MK