Friday, 24 November 2023

Taking export of ROLES and GRANTS

 When you are taking export of schema, roles are not exported. This is useful method to export the roles and grants. 


Take export of roles and grants

vi role.par

full=y

directory=DATA_PUMP_DIR

INCLUDE=ROLE_GRANT,grant

dumpfile=role.dmp

nologfile=yes

content=metadata_only

INCLUDE=ROLE:"IN (select role from dba_roles where ORACLE_MAINTAINED='N')"


Run the expdp 

expdp \'/ as sysdba\'  parfile=role.par


Generate the ddl using dump file in sql format 

impdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=role.dmp sqlfile=role.sql nologfile=yes EXCLUDE=PROC_SYSTEM_GRANT


Run the SQL file on the target database for creating the role and grants. 



No comments:

Post a Comment