Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

any other way to recompile invalide objects ??

Henrique Silva_3
Regular Advisor

any other way to recompile invalide objects ??


Hi everyone.

I did an import from a 8.1.7.0 32 bit db to a 8.1.7.4 64 bit db.

used fromuser=user1,user2 touser=user1,user2

all objects from user2 went in fine, however, I had 233 views that are invalid on user1.
( used create force view ).

I created a dinamic sql script that did

alter view view_name compile; on all invalid views. It runs but do not change the status of them.

I also ran $ORACLE_HOME/rdbms/admin/utlrp.sql which recompiles all objects on db ( invalid ones ? ), and no success there either.

now, If I drop the view and recreate it, it works, the problem, is that I am editing this long log file that I got from the import command, and I have another 220 views to go
:-((

Is the text of these views on dba_text, or some view somewhere, where I can get a dynamic sql script of all the invalid views, so that I do not have to edit this file ? Or is there another trick somewhere, which will recompile these views on the spot ?

thanks,

Henrique Silva
"to be or not to be, what was the question ???? "
4 REPLIES
Henrique Silva_3
Regular Advisor

Re: any other way to recompile invalide objects ??


NEVER MIND !!

This morning, dropped the users, did another import. Same errors. Just for fun, created the dynamic sql script to alter view compile again, and it WORKED this time :-(((

Go figure !!!!

Everything is fine now !!!

THank you !!!

Henrique Silva
"to be or not to be, what was the question ???? "
Brian Crabtree
Honored Contributor

Re: any other way to recompile invalide objects ??

Henrique,

Just for future information, views will move in and out of invalid sometimes depending on changes to the base objects. The easiest way to resolve this is to verify that the view works (automaticlly recompiles when used). You can setting a dynamic script to go through and select "where rownum = 0" from each view, and this will force it to recompile, and give an error if there is a problem.

Overall, I wouldn't worry about it too much. Provided that you know that the views work on the export server, forcing them to be valid on the import can be alot of extra work. The only thing you would want to check for is views across db links (just to make sure that the dblink works correctly, since this can cause the view to fail).

Thanks,

Brian
Henrique Silva_3
Regular Advisor

Re: any other way to recompile invalide objects ??

Thanks Brian !!!

The weird thing for me is that I have been doing exports from other systems, which are 64 bit UTF8, all the time, and have never had this problem B4. This is a 32 bit USASCII system, with no patching, so, I was wondering if that was the problem.

In any case, they all recompile fine this morning ( I have no idea why this same script did not work yesterday ), and everything is now fine, but I will look into your suggestion.

Thanks again,

Henrique
"to be or not to be, what was the question ???? "
Steven E. Protter
Exalted Contributor

Re: any other way to recompile invalide objects ??

Did you follow the attached migration procedure. If not, it could explain your trouble and lead to much, much more trouble.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com