In the previous blog, I discussed about how to extract the create user syntax along with all system priviliges, granted roles and tablespace grants.In this post I will be talking about extracting just the roles from the source database.
Here is how I just extract the roles out
This command can be run locally on the DB server to just extract the roles
expdp \'/ as sysdba\' full=y INCLUDE=role dumpfile=role.dmp nologfile=yes
A small variation of the above command, which can be run from the target database.
For it to work,I usually create a database link from the target to the source database .I usually connect to system user.
expdp \'/ as sysdba\' NETWORK_LINK=<source db link name> full=y INCLUDE=role dumpfile=role.dmp nologfile=yes
The below command extracts the role defnition into a file called role.sql.
impdp \'/ as sysdba\' dumpfile=role.dmp sqlfile=role.sql nologfile=yes
Please use your favourite text editor to open up and see contents of role.sql.
Thanks gautham, I am able to extract roles now.
ReplyDeleteIs there a way to extract only specific roles from the database? Because most databases when created already have similar type of roles existed. I am assuming that the command above will extract all of the roles from the database. When I import the dmp file to my target database with some roles already exist, will that be a problem or it will just overwritten? Thanks
ReplyDeleteNot sure on 12.2
ReplyDeleteConnected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_FULL_01": "/******** AS SYSDBA" full=y INCLUDE=role dumpfile=role.dmp nologfile=yes
ORA-39168: Object path ROLE was not found.
ORA-31655: no data or metadata objects selected for job
Job "SYS"."SYS_EXPORT_FULL_01" completed with 2 error(s) at Fri Feb 26 22:26:08 2021 elapsed 0 00:00:10