/*
 * Title  : sho_nsr.sql
 * Author : EXADIME LLC
 * Created: 29-Jul-2017
 * Updated: 29-Jul-2017
 * Release: 1.0.0
 * Purpose: Shows Non System Roles (11g).
 * History: N/A
 */

start oracle.ini

set echo off
set feedback off
set serverout on

set linesize 80
set pagesize 200

set heading on
set headsep on
set underline on
set colsep " "

set verify off
set escape off
set embedded on

col "Role Name"			format a30

spool &&rpt_dir/sho_nsr.rpt

select 
	role	"Role Name"
from
	dba_roles
where
	role not in (
		'CONNECT',
		'RESOURCE',
		'DBA',
		'SELECT_CATALOG_ROLE',
		'EXECUTE_CATALOG_ROLE',
		'DELETE_CATALOG_ROLE',
		'EXP_FULL_DATABASE',
		'IMP_FULL_DATABASE',
		'LOGSTDBY_ADMINISTRATOR',
		'DBFS_ROLE',
		'AQ_ADMINISTRATOR_ROLE',
		'AQ_USER_ROLE',
		'DATAPUMP_EXP_FULL_DATABASE',
		'DATAPUMP_IMP_FULL_DATABASE',
		'ADM_PARALLEL_EXECUTE_TASK',
		'GATHER_SYSTEM_STATISTICS',
		'RECOVERY_CATALOG_OWNER',
		'SCHEDULER_ADMIN',
		'HS_ADMIN_SELECT_ROLE',
		'HS_ADMIN_EXECUTE_ROLE',
		'HS_ADMIN_ROLE',
		'GLOBAL_AQ_USER_ROLE',
		'OEM_ADVISOR',
		'OEM_MONITOR'
	)
order by
	1
/

spool off;

clear columns

set embedded off
set escape off
set verify on

set colsep " "
set underline on
set headsep on
set heading on

set pagesize 24
set linesize 80

set serverout off
set feedback on
set echo off
