user761758 user761758 - 1 month ago 9
SQL Question

Oracle 12c Compare last_ddl_time on views

I have a list of views in two schemas in which everything is equal except the last_ddl_time.

select owner, object_name, last_ddl_time
from all_objects
where object_type = upper('view')
and owner in ('SC1', 'SC2')
order by 2,1,3;


produces the correct data, but I would like the SQL to compare last_ddl_time and return data only if the last_ddl_time for SC1 is larger than last_ddl_time for SC2.

I have tried creating a sub-query that makes the comparisons, but cannot get the syntax worked out to produce the compared data.

The object is to find views updated in SC2 before the view of the same name was updated in SC1.

Any and all suggestions & insights appreciated.

Answer

You can use an inline view (or a similar CTE) that finds the DDL time for each schema, e.g. using an aggregate and windowing clause, and the filter the results by comparing those values in the outer query:

select owner, object_name, last_ddl_time
from (
  select owner, object_name, last_ddl_time,
    max(case when owner = 'SC1' then last_ddl_time end)
      over (partition by object_name) as sc1_time,
    max(case when owner = 'SC2' then last_ddl_time end)
      over (partition by object_name) as sc2_time
  from all_objects
  where object_type = 'VIEW'
  and owner in ('SC1','SC2')
)
where sc1_time > sc2_time
order by 2,1,3;

You could also use this to show both DDL times side-by-side:

select object_name, sc1_time, sc2_time
from (
  select owner, object_name,
    max(case when owner = 'SC1' then last_ddl_time end)
      over (partition by object_name) as sc1_time,
    max(case when owner = 'SC2' then last_ddl_time end)
      over (partition by object_name) as sc2_time
  from all_objects
  where object_type = 'VIEW'
  and owner in ('SC1','SC2')
)
where sc1_time > sc2_time
and owner = 'SC1'
order by 1;