Objects invalid after full import

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

Quote:

---

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.

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

Quote:

---

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

Code:

---

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

---

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

Quote:

---

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

Code:

---

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);

Quote:

---

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

DBMS_UTILITY.COMPILE_SCHEMA (
schema VARCHAR2);

---

And why not just utlrp :)

Quote:

---

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

DBMS_UTILITY.COMPILE_SCHEMA (
schema VARCHAR2);

---

That would work as well. :)

Friggin developers always have to over-complicate things!

SHOOT THEM!!, then take their advice.

Quote:

---

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!!! :D

Quote:

---

Originally posted by surnag
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

---

maybe a stupid question:

did you check that the objects that you are unable to validate aren't views or procedures that refers to objects waiting to be imported to another schema? as you said you don't have completed the full import of all schemas, try to get all schemas imported, then validate (compile) objects and if it fails, check the permissions.