Pages

Tuesday, October 4, 2011

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!

3 comments:

Anonymous said...

It looks like this script is unnecessary for me. I tried this in a development environment and the cursor returns a null set.

Thanks!

Anonymous said...

It is odd that in our environment we have so many. I wonder if this is an inventory thing?

Anonymous said...

Actually I've done a bit more digging around and *all* the invalid views we are removing are the individual views for inventory organisations we've disabled in the config file.

It looks like if you do a single "show me everything" build and then cut it down certain views don't get removed as you cut down the list of generated views.

As we have (almost) migrated to R12 and they were originally build in the R11 environment consequently most of them are invalid due to errors and that's what we're seeing.