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