Re: Copying Users/Roles/Privs between Oracle Instances

In article <3AC3BC9F.8CC04602_at_home.net>, Stratford2000_at_home.net says...
> I need to copy a bunch of users with their roles and privs to an Oracle
> 7.3.3 on NT to an Oracle 8.1.6.2.0 on Unix AIX.
>
> I cannot discern how to do this from the 8i Utilities manual with
> Export/Import, yet I hear it can be done. I must be blind because I
> cannot see how to do that.

You are not blind as there is no export/import user commands. Export/import commands moves schema's.

>
> The users do not own any objects like tables, as such. They have a role
> and the role has permissions on the objects in the database (tables,
> views)

You can either write a sql program to query the database and create the sql needed to recreate the users. I recommend this method as you learn a lot about how what makes up a user id. There is an option on the create command to pass the hashed password to the instance. I can't remember what the option is maybe someone else knows.

Another option is to export as system the with rows=n. You can then edit the file to create a script which will recreate the users. Remember once you edit a dump file it cannot be imported. This option is useful if you have an idea of what you are looking for.

If you are only moving 10's of users the second option is doable. Years ago, I had to move hundreds of user's. I wrote a script to create the creation script. Unfortunately, I can't find it.

Good luck,

Liz Received on Fri Mar 30 2001 - 14:08:24 CST