role_creation_script.sql

-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 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