-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -- Author : Sameer S Choudhari -- Description : Get the DDl for the ROLE creation. Here,I am ignoring Oracle specific roles. -- Call Syntax : @role_creation_script.sql -- Last Modified: 17/03/2015 -- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SET LONG 9999999 LONGCHUNKSIZE 9999999 PAGESIZE 0 LINESIZE 300 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON HEAD OFF ECHO OFF SELECT dbms_metadata.get_ddl( 'ROLE', role ) ||' ;' FROM dba_roles WHERE role NOT IN ( 'ADM_PARALLEL_EXECUTE_TASK','AQ_ADMINISTRATOR_ROLE','AQ_USER_ROLE','CONNECT','DATAPUMP_EXP_FULL_DATABASE','DATAPUMP_IMP_FULL_DATABASE','DBA', 'DBFS_ROLE','DELETE_CATALOG_ROLE','EXECUTE_CATALOG_ROLE','EXP_FULL_DATABASE','GATHER_SYSTEM_STATISTICS','GLOBAL_AQ_USER_ROLE','HS_ADMIN_EXECUTE_ROLE' ,'HS_ADMIN_ROLE','HS_ADMIN_SELECT_ROLE','IMP_FULL_DATABASE','LOGSTDBY_ADMINISTRATOR','OEM_ADVISOR','OEM_MONITOR','RECOVERY_CATALOG_OWNER','RESOURCE' ,'SCHEDULER_ADMIN','SELECT_CATALOG_ROLE' ); SPOOL role_creation_script.sql / SPOOL off; SET LINESIZE 80 PAGESIZE 14 FEEDBACK ON TRIMSPOOL ON VERIFY ON