Invalid views

[Tarek_1]

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

[John Palmer]

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]

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]

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]

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

[Tarek_1]

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]

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.