Objects invalid after full import
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Objects invalid after full import

  1. #1
    Join Date
    Sep 2004
    Posts
    18

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,001

    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.
    this space intentionally left blank

  3. #3
    Join Date
    Sep 2004
    Posts
    18
    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

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,001
    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
    this space intentionally left blank

  5. #5
    Join Date
    Sep 2004
    Posts
    18
    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

  6. #6
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    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

  7. #7
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by stmontgo
    why not just.....

    DBMS_UTILITY.COMPILE_SCHEMA (
    schema VARCHAR2);
    And why not just utlrp
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,001
    Originally posted by stmontgo
    why not just.....

    DBMS_UTILITY.COMPILE_SCHEMA (
    schema VARCHAR2);
    That would work as well.
    this space intentionally left blank

  9. #9
    Join Date
    Jan 2001
    Posts
    3,131
    Friggin developers always have to over-complicate things!

    SHOOT THEM!!, then take their advice.
    I remember when this place was cool.

  10. #10
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,001
    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!!!
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width