-
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!
-
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
-
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?
-
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
-
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!!
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|