I found GET_GRANTED_DDL func which is very helpful while doing imp/exp – impdp/expdp operations or for any other user migrations. Check it out..
SELECT ‘select DBMS_METADATA.GET_GRANTED_DDL (”ROLE_GRANT”, ”’||USERNAME||”’) from dual;’
FROM DBA_USERS
‘SELECTDBMS_METADATA.GET_GRANTED_DDL(”ROLE_GRANT”,”’||USERNAME||”’)FROMDUAL;’
select DBMS_METADATA.GET_GRANTED_DDL (‘ROLE_GRANT’, ‘SYSTEM’) from dual;
select DBMS_METADATA.GET_GRANTED_DDL (‘ROLE_GRANT’, ‘SYS’) from dual;
select DBMS_METADATA.GET_GRANTED_DDL (‘ROLE_GRANT’, ‘SCOTT’) from dual;
select DBMS_METADATA.GET_GRANTED_DDL (‘ROLE_GRANT’, ‘ORACLE_OCM’) from dual;
DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,’SYSTEM’)
GRANT “DBA” TO “SYSTEM” WITH ADMIN OPTION
GRANT “AQ_ADMINISTRATOR_ROLE” TO “SYSTEM” WITH ADMIN OPTION
GRANT “SALES_HISTORY_ROLE” TO “SYSTEM” WITH ADMIN OPTION
GRANT “MGMT_USER” TO “SYSTEM”
1 row selected.
DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,’SYS’)
GRANT “CONNECT” TO “SYS” WITH ADMIN OPTION
GRANT “RESOURCE” TO “SYS” WITH ADMIN OPTION
GRANT “DBA” TO “SYS” WITH ADMIN OPTION
GRANT “SELECT_CATALOG_ROLE” TO “SYS” WITH ADMIN OPTION
GRANT “EXECUTE_CATALOG_ROLE” TO “SYS” WITH ADMIN OPTION
GRANT “DELETE_CATALOG_ROLE” TO “SYS” WITH ADMIN OPTION
GRANT “EXP_FULL_DATABASE” TO “SYS” WITH ADMIN OPTION
GRANT “IMP_FULL_DATABASE” TO “SYS” WITH ADMIN OPTION
GRANT “RECOVERY_CATALOG_OWNER” TO “SYS” WITH ADMIN OPTION
GRANT “GATHER_SYSTEM_STATISTICS” TO “SYS” WITH ADMIN OPTION
DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,’SYS’)
GRANT “LOGSTDBY_ADMINISTRATOR” TO “SYS” WITH ADMIN OPTION
GRANT “AQ_ADMINISTRATOR_ROLE” TO “SYS” WITH ADMIN OPTION
GRANT “AQ_USER_ROLE” TO “SYS” WITH ADMIN OPTION
GRANT “OEM_MONITOR” TO “SYS” WITH ADMIN OPTION
GRANT “HS_ADMIN_ROLE” TO “SYS” WITH ADMIN OPTION
GRANT “JAVAUSERPRIV” TO “SYS” WITH ADMIN OPTION
GRANT “JAVAIDPRIV” TO “SYS” WITH ADMIN OPTION
GRANT “JAVASYSPRIV” TO “SYS” WITH ADMIN OPTION
GRANT “JAVADEBUGPRIV” TO “SYS” WITH ADMIN OPTION
GRANT “EJBCLIENT” TO “SYS” WITH ADMIN OPTION
GRANT “JAVA_ADMIN” TO “SYS” WITH ADMIN OPTION
DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,’SYS’)
GRANT “JAVA_DEPLOY” TO “SYS” WITH ADMIN OPTION
GRANT “CTXAPP” TO “SYS” WITH ADMIN OPTION
GRANT “XDBADMIN” TO “SYS” WITH ADMIN OPTION
GRANT “AUTHENTICATEDUSER” TO “SYS” WITH ADMIN OPTION
GRANT “OLAP_DBA” TO “SYS” WITH ADMIN OPTION
GRANT “OLAP_USER” TO “SYS” WITH ADMIN OPTION
GRANT “SCHEDULER_ADMIN” TO “SYS” WITH ADMIN OPTION
GRANT “OEM_ADVISOR” TO “SYS” WITH ADMIN OPTION
GRANT “MGMT_USER” TO “SYS” WITH ADMIN OPTION
GRANT “CWM_USER” TO “SYS” WITH ADMIN OPTION
DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,’SYS’)
GRANT “OLAPI_TRACE_USER” TO “SYS” WITH ADMIN OPTION
1 row selected.
DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,’SCOTT’)
GRANT “CONNECT” TO “SCOTT”
GRANT “RESOURCE” TO “SCOTT”