role_creation_script.sql

-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Author       : Sameer S Choudhari
-- Description  : Get the DDl for the ROLE creation
-- 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_ddls.SQL
/
spool OFF;
SET linesize 80 pagesize 14 feedback ON trimspool ON verify ON