Objects invalid after full import

[surnag]

Hi Friends,

I took a full export of 9i db and after creating the necessary users in the newly created 9i db, i imported it.

In one of the users, all the objects are there but most of the views are invalid and if i try to compile them,it says table does not exist.But that table exists .

what might be the reason? if it is related to priveleges, how to determine what all priveleges that needs to be created before I again begin the Import ?

thanks

[gandolf989]

Originally posted by surnag
Hi Friends,

I took a full export of 9i db and after creating the necessary users in the newly created 9i db, i imported it.

In one of the users, all the objects are there but most of the views are invalid and if i try to compile them,it says table does not exist.But that table exists .

what might be the reason? if it is related to priveleges, how to determine what all priveleges that needs to be created before I again begin the Import ?

thanks

If you did a full export as system then you should import all of the privileges that you need. However, if you did the export/import as the schema owner then you might be missing some of the privileges. Look at dba_dependencies to see what priviliges you need. You should be able to create dynamic sql to do the grants. But even better would be to go back to the source database and do an export of grants only and import those grants into the new database. This should solve your problem.

[surnag]

Thanks, Since I have already done the import, what is the best way to proceed regarding grants. I want the objects to be valid. While importing, I had done the import of the user with grants =Y also. So, do you think it is better that I export and import for the remaining users with grants=Y so that this problem is solved ?


Thanks again

[gandolf989]

Originally posted by surnag
Thanks, Since I have already done the import, what is the best way to proceed regarding grants. I want the objects to be valid. While importing, I had done the import of the user with grants =Y also. So, do you think it is better that I export and import for the remaining users with grants=Y so that this problem is solved ?


Thanks again

exp system/passwd full=y grants=y rows=n constraints=n

imp system/passwd full=y grants=y rows=n constraints=n

Then compile the invalid objects

spool c:\my comp.sql
SELECT 'ALTER ' ||
   DECODE(object_type, 'PACKAGE BODY','PACKAGE',object_type) ||
   ' ' || owner || '.' || object_name ||
   DECODE(object_type, 'PACKAGE BODY',' COMPILE BODY;',' COMPILE;')
  FROM all_objects
 WHERE status = 'INVALID';
spool off
@c:\my comp.sql

[surnag]

Thanks for your reply. But i was assuming that grants will also be exported since its default=Y.
Once i am done with the below, dot you think I also need to enable or import the constrtaints too ?

exp system/passwd full=y grants=y rows=n constraints=n

imp system/passwd full=y grants=y rows=n constraints=n

Thanks

[stmontgo]

Originally posted by gandolf989
exp system/passwd full=y grants=y rows=n constraints=n

imp system/passwd full=y grants=y rows=n constraints=n

Then compile the invalid objects

spool c:\my comp.sql
SELECT 'ALTER ' ||
   DECODE(object_type, 'PACKAGE BODY','PACKAGE',object_type) ||
   ' ' || owner || '.' || object_name ||
   DECODE(object_type, 'PACKAGE BODY',' COMPILE BODY;',' COMPILE;')
  FROM all_objects
 WHERE status = 'INVALID';
spool off
@c:\my comp.sql

why not just.....

DBMS_UTILITY.COMPILE_SCHEMA (
schema VARCHAR2);

[adewri]

Originally posted by stmontgo
why not just.....

DBMS_UTILITY.COMPILE_SCHEMA (
schema VARCHAR2);

And why not just utlrp

[gandolf989]

Originally posted by stmontgo
why not just.....

DBMS_UTILITY.COMPILE_SCHEMA (
schema VARCHAR2);

That would work as well.

[Mr.Hanky]

Friggin developers always have to over-complicate things!

SHOOT THEM!!, then take their advice.

[gandolf989]

Originally posted by Mr.Hanky
Friggin developers always have to over-complicate things!

SHOOT THEM!!, then take their advice.

Now, now, Mr. I can't control my agressive tendencies!!!