Simpler Navigation coming for Servers and Operating Systems
Coming soon: a much simpler Servers and Operating Systems section of the Community. We will combine many of the older boards, and you won't have to click through so many levels to get at the information you need. If you are looking for an older board and do not find it, check the consolidated boards, as the posts are still there.
cancel
Showing results for 
Search instead for 
Did you mean: 

Invalid views

Tarek_1
Frequent Advisor

Invalid views

Hi again,
i have a production DB 8.1.6.0 that have a lot of invalid views. I want to "clean" the db. I'd like to remove the views, but before doing this, i want to backup them to prevent possible problems. This is a very critical db. However i have a replica of it on another db less critical, so i can try there.
So, steps to follow will be:
- backup invalid views (about 300)
- remove views
- test if everything works
- restore views (2 see if backup is ok)
- remove all again and do it on production db
How can these steps be done technically?
Thanks....
Tarek
6 REPLIES
John Palmer
Honored Contributor

Re: Invalid views

Just because the views are invalid doesn't meant that they're not used, have you tried recompiling them?

You can use a rows=n export to secure the views (among other things), running an import will recreate them for you.

Regards,
John
Tarek_1
Frequent Advisor

Re: Invalid views

I have exported all objects of the user that owns the invalid views and imported them in the replica environment and also here they result invalid.
If they're invalid i don't think they're used, but because i'm not sure i want to backup them and try to remove and see what happens.
John Palmer
Honored Contributor

Re: Invalid views

I'd still try to compile them.

In sqlplus:
set head off
set feed off
spool xxx.sql
select 'alter view '||view_name|| ' compile;' from user_objects where object_type = 'VIEW' and status = 'INVALID';
spool off
@xxx.sql

Regards,
John
Graham Cameron_1
Honored Contributor

Re: Invalid views

You need to find out why they are invalid.
ALTER VIEW COMPILE ;
and then see what errors you get - most likely a referenced table has been dropped or changed.

If you are determined to drop the views I attach a script which will extract a view to a file. Param 1 is the uppercase viewname, in 'single quotes'; optional param2 is the file name.
Good luck.

-- Graham
Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.
Tarek_1
Frequent Advisor

Re: Invalid views

I tried to compile some of them with this statement:
alter view view_name compile;
i get
Warning: View altered with compilation errors.
And the status is still invalid.
But i don't know what these views does
Bill Thorsteinson
Honored Contributor

Re: Invalid views

You should be able to find the query that makes up the view in the SYS.ALL_VIEWS table. Based on that you can determine what it does.

Try using the command 'SHOW ERRORS' immediately after the failed compile to find out what is broken.