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

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.

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.

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.

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.