6 Replies Latest reply on Oct 18, 2017 6:21 PM by thatJeffSmith-Oracle

    Database Diff (compare schemas) virtual hidden columns

    NMOF

      Hello Everyone,

       

      Maybe there is no way around it, but possible someone can advise me on a better way.

       

      I am trying to compare 2 schemas using SQL Developer and is working, however it seems that my source has a lot of hidden virtual columns, which for what I want to check is not relevant and is annoying, this makes the report to return much more tables with differences.

       

      Is there any way of running the report excluding the hidden virtual columns?

       

      Thank you.

       

      Kind regards,

       

      NF

        • 1. Re: Database Diff (compare schemas) virtual hidden columns

          Maybe there is no way around it, but possible someone can advise me on a better way.

          Please post suggestions for enhancements on the Sql Developer Exchange - https://apex.oracle.com/pls/apex/f?p=43135:1

          I am trying to compare 2 schemas using SQL Developer and is working, however it seems that my source has a lot of hidden virtual columns,
          which for what I want to check is not relevant and is annoying, this makes the report to return much more tables with differences.

          It really isn't possible for ANY product to know what YOU consider 'relevant'.

           

          Is there any way of running the report excluding the hidden virtual columns?

          And there is the problem. Do you mean 'hidden' columns? Do you mean 'virtual' columns? Or do you mean columns that are BOTH hidden and virtual?

           

          Do you mean you don't want to compare any tables that have one, or more, hidden and/or virtual columns?

           

          If so that could exclude a LOT of tables. And I don't see much value in comparing two tables but ignoring their virtual/hidden columns. In many cases that

          could indicate the tables are identical when they really aren't.

           

          There are SEVERAL ways hidden/invisible columns can get created:

           

          1. you can make a column 'invisible' - it exists, it has data but it won't be included 'automatically' if you do DML on the table - you have to specifically mention it.

          2. you can create virtual columns - these are often used to support indexes that can't be properly created on other non-virtual columns.

          3. you can create extended statistics on a table - Oracle can then add 'hidden' columns to support those statistics

          • 2. Re: Database Diff (compare schemas) virtual hidden columns
            NMOF

            Hello,

             

            Thanks for your reply.

             

            I am comparing the "same" schema in different databases, they are different environments, so for this let's say I am comparing dev with uat, probably should have mentioned that.

             

            In one environment I have the hidden virtual columns created by extended statistics (it looks like that anyway),  didn't knew they were there until now to be honest and they were not created by us, anyway I believe the creation of those columns can change if the extended statistics are executed in a different database for the same tables.

             

            With that said this is messing my diff report because there is a lot of tables marked different because of those columns, I can use a script like http://www.idevelopment.info/data/Oracle/DBA_scripts/Database_Administration/dba_compare_schemas.sql which doesn't report this hidden virtual columns, but that script firstly give me more than I want, like privileges differences and only the tables not the ddl.

             

            I really like the format of the Diff report, is there anyway to make this hidden columns to not be reported?

             

            Thank you.

             

            NMOF

            • 3. Re: Database Diff (compare schemas) virtual hidden columns
              Gary Graham-Oracle

              SQL Developer takes advantage of DBMS_METADATA.GET_DDL, which just gets all DDL for the specified objects (including all columns, of course)... https://docs.oracle.com/database/121/ARPLS/d_metada.htm#ARPLS026

               

              So any enhancement would require our developer to provide filter options, then filter out the hidden/invisible stuff that comes back from GET_DDL.

               

              You might be better off investing some time in modifying that dba_compare_schemas script you mention (although I see a copyright notice on it!).  For example, when doing column queries, it typically does a MINUS SELECT to exclude certain things.  So might look at doing a MINUS on selects like that look something like ...

              select ...

              from all_tab_cols

              where hidden_column = 'YES'

                 or virtual_column = 'YES'

              If you go that route, any follow-up questions on the SQL would be better asked in the SQL and PL/SQL space.

               

              Hope this helps.

              • 4. Re: Database Diff (compare schemas) virtual hidden columns

                In one environment I have the hidden virtual columns created by extended statistics (it looks like that anyway),  didn't knew they were there until now to be honest and they were not created by us, anyway I believe the creation of those columns can change if the extended statistics are executed in a different database for the same tables.

                Oracle's DBMS_METADATA does NOT include system-generated hidden/virtual columns.

                 

                It includes the metadata needed to recreate the table. It doesn't include extended stat columns.

                 

                Post an actual example showing hidden/virtual columns being compared that you think should NOT be compared. Post the results of using DBMS_METADATA.GET_DDL on the two tables also.

                • 5. Re: Database Diff (compare schemas) virtual hidden columns

                  SQL Developer takes advantage of DBMS_METADATA.GET_DDL, which just gets all DDL for the specified objects (including all columns, of course)...

                  Well, not really. See my last reply to OP.

                   

                  That package doesn't include hidden/virtual columns created by Oracle for things like extended stats.

                   

                  It also doesn't include all of the partitions/subpartitions a table might actually have if the table uses INTERVAL partitioning. It would only include the base/core partition and the interval info.

                  select partition_name from user_tab_partitions

                  where table_name = 'TRANSACTIONS1'

                   

                  P_BEFORE_2007

                  SYS_P2538

                  SYS_P2539

                   

                  select dbms_metadata.get_ddl('TABLE', 'TRANSACTIONS1') from dual

                   

                    CREATE TABLE "HR"."TRANSACTIONS1"

                     (    "ID" NUMBER,

                      "TRANSACTION_DATE" DATE,

                      "VALUE" NUMBER

                     )

                    PARTITION BY RANGE ("TRANSACTION_DATE") INTERVAL (NUMTODSINTERVAL(1,'DAY'))

                  (PARTITION "P_BEFORE_2007"  VALUES LESS THAN (TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

                  I think we need to see the actual hidden/virtual column example OP is talking about. Object columns create hidden columns - those need to be compared or the matching won't be correct. Same with hidden columns supporting functional indexes

                  create index ndx1 on transactions1 (id * 2)

                   

                  select column_name from user_tab_columns where table_name = 'TRANSACTIONS1'

                   

                  ID

                  TRANSACTION_DATE

                  VALUE

                   

                  select column_name from user_tab_cols where table_name = 'TRANSACTIONS1'

                   

                  SYS_NC00004$

                  ID

                  TRANSACTION_DATE

                  VALUE

                  But the GET_DDL is the same after creating the functional index even though there are hidden columns. Those columns are related to the index, not the table itself, so aren't part of the table ddl or any comparison.

                  • 6. Re: Database Diff (compare schemas) virtual hidden columns
                    thatJeffSmith-Oracle

                    this is all moot - please file an enhancement request to my oracle support or to sqldeveloper.oracle.com

                     

                    At the end of the day, it's just code.