How to Find Invalid or Broken Views in Exasol?

Help Needed
1

Hello Exasol,

Is there a way to check for invalid views in Exasol — specifically, views that reference non-existing objects?

I’d like to identify any broken or outdated views in the database. Thanks in advance for your help!

2

Hello,

You can figure out whether views are invalid by looking at whether there are EXA_ALL_COLUMNS rows defined for them. An invalid view will either have no columns in that table or the column’s STATUS field will be “OUTDATED”. However, there are 2 scenarios where EXA_ALL_COLUMNS can be inaccurate:

  1. If the view was created with the FORCE keyword. In this case EXA_ALL_COLUMNS will not contain rows for the view regardless of whether the view is valid or not until it is accessed for the first time.
  2. If the view was originally valid and then later a dependency was changed. In this case EXA_ALL_COLUMNS will contain rows for it even but the STATUS column will read ‘OUTDATED’ (regardless of whether the view is still valid or not).

To make sure EXA_ALL_COLUMNS is accurate you have to first access any view that falls under these two scenarios and then check again. The query below will also show invalid system views, based on EXA_SYS_COLUMNS. So the procedure is:

  1. Run:
SELECT *
FROM (
       SELECT view_schema,
              view_name
       FROM exa_all_views v
       LEFT JOIN exa_all_columns c
          ON v.view_schema = c.column_schema
          AND v.view_name = c.column_table
       GROUP BY view_schema, view_name
       HAVING ANY( column_name IS NULL )
       OR ANY( status = 'OUTDATED' )
   
       UNION ALL

       SELECT
       	 column_schema,
         column_table
       FROM
       	exa_sys_columns c
       WHERE
       	status = 'OUTDATED'
       GROUP BY
      	column_schema, column_table
)
ORDER BY 1, 2
;
  1. For every view that is listed you need to access it by (for example) running DESC schema.view. If this DESC fails the view is invalid and vice versa.
  2. You can either collect the list of invalid views by keeping track of which DESC commands fail or you can later run the SQL in #1 above. All views the statement now returns will be invalid (assuming no other DDL has run in the meantime).

Hope this will be helpful to you

1 Like