-
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
-
Re: Objects invalid after full import
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
-
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
-
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);
I'm stmontgo and I approve of this message
-
-
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.
I remember when this place was cool.
-
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!!!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|