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:
It looks like this script is unnecessary for me. I tried this in a development environment and the cursor returns a null set.
Thanks!
It is odd that in our environment we have so many. I wonder if this is an inventory thing?
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.
Post a Comment