DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: constraint reference to other schema

  1. #1
    Join Date
    Aug 2001
    Posts
    15

    Question

    Hi all,

    I am trying to do a fromuser touser- import:
    fromuser=user1, touser=user2

    Everything is fine. However when I try to enable the constraints in the user2 schema, some constraints can't be enabled because they reference tables in the user1 schema.

    I gave user2 dba rights on my test database, but that didn't make any difference. When I try to enable the constraints, I get the message that the view or table it references doesn't exist. How can I enable those constraints that point to the other user schema?

    Please help! Thanks!



  2. #2
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    This is an inelegant possibility.

    You can view the export (dmp) file in vi and grep on the statements that create or alter constraints and put them into another file.

    You can do a search and replace if possible and add the schema name to the objects.

    Good luck.

    David Knight
    OCP DBA 8i, 9i, 10g

  3. #3
    Join Date
    Aug 2001
    Posts
    15
    I did add the schema name to the objects.

    as user2 I am able to describe the objects of user1.

    desc user1.table_name.

    As soons as I enable a constraint, the very same tables that I was able to describe cannot be found:
    table or view does not exist.

    This seems like a privilege problem`to me. What privileges need to be granted for constraints of one schema to point to another schema?

  4. #4
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    But in the dump file, there should be statements like

    alter table tralala (constraint statement)

    If you add user2. or user1. to these statements, it might work.

    Good luck.
    David Knight
    OCP DBA 8i, 9i, 10g

  5. #5
    Join Date
    Aug 2001
    Posts
    15
    I did an import with indexfile=file

    that writes all the ddl and dml for creating tables and constraints into an sql Script.
    I opened the script and edited everything so that instead of to table_name, the constraints point to schema.table_name.

    At first, it said: table or view does not exist.
    Then I granted user2 - let's call him reporter - select, update, delete, alter and insert on the tables that the constraints point to in the user1 schema.
    Now it says: insufficient privileges.

    Can I even point constraints of one schema to tables in another?

    And if yes, would I have to do grants on a column basis?

    Thanks! And remember, I know where you work!!

  6. #6
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    Fair enough. If this works, my fee is one cold Pils.

    Can you grant privileges with dba studio? What does it say?

    Give reporter and user1 every privilege under the sun and see what happens. It`s TEST, right? :-)



    [Edited by dknight on 02-24-2002 at 12:57 PM]
    David Knight
    OCP DBA 8i, 9i, 10g

  7. #7
    Join Date
    Aug 2001
    Posts
    15

    Talking

    Hi all.

    Just for those who have or had the same problem I had:

    I found out what was wrong.
    To be able to reference tables in other schemas, the owner of the other schema has to grant 'reference' to the user who wants to acces the schema through foreign keys.

    That's all there is too it.

    If you don't want to edit the indexfile generated by
    imp indexfile=file you will have to create synonyms that point to the tables in the other schema.




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