The best way to move ROLES and USERS from one DB to another???

[supermega]

I need to move about 30 roles that have been created on our Production DB to a test DB, can I do this using exp/imp? I dont want to bring any object or data just the users and roles.
If not should I do a select from dba_roles and dba_sys_privs for the roles?

Thanks,
SMD

[sambavan]

IF those roles are already there then I beleive that you could do just the export and import the user with the grants Y. On the other hand if those roles had been created created for the custom use, then you could create those roles manually on your test system and then import the users.

FYI: roles gets exported automatically, only when you do an export on type FULL.


Hope this would help you.
Sam

[supermega]

Well I do have a FULL export from the production DB now how can I only import the ROLES from that dmp file? I dont want any objects imported just roles.

Is there a way on the production DB to use sql to pull from dba_ or V$ tables to make new scripts to re-create the roles?

[sambavan]

Full import only would import the rolles. You cannot just extract the roles out of the full export. The one and the only way that I know is create the roles manually and then exp/imp the user(s).


Sam

[sambavan]

Check this link: use it to extract your DDLs and manualy edite that file and run that file to generate the roles. Once after doing that, import the users one by one.


http://dbasupport.com/forums/showthr...threadid=11378


Hope this would become handy to you.

Sam

[pando]

yup very good script except the problem I mentioned in other thread

[sambavan]

I thought the fix had been put by amar.

Sam

[pando]

I will try it tomorrow and let you know!

[supermega]

Does anyone know of anything like this thatr will run on Win2000. I need to combine to production servers into 1 server 1 instance. So I have to bring over 1 schema and a ton of users and roles.