Oracle: copy a role from one database to another?

Question

I would like to duplicate some roles from a production database to a test database.

How can I programatically generate a text-based SQLPLUS script to do this? Since I'm only interested in the roles I don't want to use EXP/IMP.

Solution

DBMS_METADATA might be helpful: http://www.orafaq.com/wiki/DBMS_METADATA

SELECT dbms_metadata.get_ddl('ROLE', role) FROM dba_roles;
SELECT dbms_metadata.get_granted_ddl('ROLE_GRANT',  '&&your_role_name') FROM dual;
SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT','&&your_role_name') FROM dual;
SELECT dbms_metadata.get_granted_ddl('OBJECT_GRANT','&&your_role_name') FROM dual;

Comments

[Mark Harrison]

It's useful, but I'd prefer not to roll my own, but instead use something which has been tested and debugged. Thanks!

[Jens Schauder]

This is hardly rolling your own. How do you think you will find stuff that is better tested then the stuff that comes with your database?

[Mark Harrison]

I'm not sure which pieces of metadata to retrieve in order to fully replicate a role, so I was hoping somebody might have a script that would do this. Will the last item (with the four selects) give me the complete set of information to replicate the role?

[Jens Schauder]

the package produces DDL statements.

[Mark Harrison]

great... and are the four statements enough to completely reproduce the role?

[Jens Schauder]

Sorry don't know the details. You'll have to experiment or read the documentation.

[Mark Harrison]

Docs don't say, as far as I can tell, but experimentation shows that it should be the right thing. I'll edit your answer to include the lines and accept. many thanks!!

[Ben]

@MarkHarrison, be careful with this, it's a little bit complicated transporting LOBs across DB links.