any other way to recompile invalide objects ??

[Henrique Silva_3]

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

[Henrique Silva_3]

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

[Brian Crabtree]

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]

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

[Steven E. Protter]

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

SEP