GUI Display Query for Editioning Views

[clock]

We're running Oracle 26ai and PLSQL Dev 15.0.4.2064

For EBR, I'm noticing editioning views not actualized in the current edition do not show up in the view list, even when I'm in a child edition with the ability to select from that view via a standard worksheet.

Are you able to tell me the query used for the GUI view, particularly when populating the list of Views?

 

[Jim Gust]

I believe the file is CANames.sql located in the application folder.

I've updated mine based on another users analysis that forcing the optimizer to '9.2.0' resulted in better performance. I tested this in my own environment verified it to be more performant for me.

select /*+ optimizer_features_enable('9.2.0') */ object_name, object_type
  from sys.dba_objects o
 where o.owner = :schema
   and o.object_type in ('TABLE', 'VIEW', 'PACKAGE','TYPE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE')
;
select /*+ optimizer_features_enable('9.2.0') */ object_name, object_type
  from (select  connect_by_root(synonym_name) object_name, a.object_type
          from sys.dba_synonyms s
           CROSS JOIN LATERAL (select o.object_type
                  from sys.dba_objects o
                 where 1 = 1
                   and s.table_owner = o.owner
                   and s.table_name = o.object_name
                   and o.object_type in ('TABLE',
                                         'VIEW',
                                         'PACKAGE',
                                         'TYPE',
                                         'PROCEDURE',
                                         'FUNCTION',
                                         'SEQUENCE')  ) a
         where 1 = 1
           and connect_by_isleaf = 1
           and connect_by_iscycle = 0
         start with s.owner in ('PUBLIC', USER)
        connect by nocycle s.owner = prior s.table_owner
               and s.synonym_name = prior s.table_name)
 where 1 = 1
   and object_type is not null
;
select /*+ optimizer_features_enable('9.2.0') */ db_link as object_name, 'DATABASE LINK' as object_type
  from sys.dba_db_links o
  where o.owner = :schema
     or o.owner = 'PUBLIC'
;