Noetix: Removing Invalid Views (In A Supported Way)

This blog post covers use of a fairly simple script to drop any invalid views in the NOETIX_SYS schema that should not have been already removed by the regenerate process (i.e. where a view shouldn't be there at all rather than having just failed to build correctly).

The script (callable from XU6) is;

@utlspon xu6_drop_unused_invalid_views_upd

begin
  dbms_output.put_line('See 110930-000002 Invalid Views under NOETIX_SYS Schema');
  dbms_output.put_line('-------------------------------------------------------');
  dbms_output.put_line('Remove Invalid Noetix Views (that are not set to be ommitted)');
  for v_SQL in (select 'DROP VIEW NOETIX_SYS.' || do.object_name text
                  from dba_objects do
                 where do.status = 'INVALID'
                   and do.OWNER = 'NOETIX_SYS'
                   and do.OBJECT_TYPE = 'VIEW'
                   and do.OBJECT_NAME not in
                       (select view_name
                          from n_views
                         where nvl(omit_flag, 'N') = 'N')) loop
    dbms_output.put_line('  ' || v_SQL.Text);
    execute immediate v_SQL.Text;
  end loop;
  dbms_output.put_line('END');
end;
/

commit;

@utlspoff
NOTE: If you have not called your Noetix schema owner NOETIX_SYS you will need to alter all occurrences of that in the above SQL!