The best way to move ROLES and USERS from one DB to another???
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

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

  1. #1
    Join Date
    Oct 2000
    Posts
    103

    Question

    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

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Oct 2000
    Posts
    103
    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?

  4. #4
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
    Thanx
    Sam



    Life is a journey, not a destination!


  5. #5
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
    Thanx
    Sam



    Life is a journey, not a destination!


  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    yup very good script except the problem I mentioned in other thread

  7. #7
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    I thought the fix had been put by amar.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I will try it tomorrow and let you know!

  9. #9
    Join Date
    Oct 2000
    Posts
    103
    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.

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