List user in system tablespace

set termout off
store set sqlplus_settings replace

set serveroutput on size 1000000
set linesize 130
set pagesize 120

set verify off
set head off
ttitle off
btitle off

clear columns
clear breaks



column username format a30 heading "USER" wrap
column guilty heading "USES SYSTEM FOR" word_wrap

-- Get database name and store in variable
column name new_value s_dbname
SELECT rtrim(name) name FROM v$database;

-- Get today's date
column today new_value s_curDate
SELECT to_char(sysdate, 'Month DD, YYYY') today FROM dual;

-- Get host name and store in variable
column host_name new_value s_machine
SELECT host_name from v$instance;

set termout on
set feedback off

set head on

spool systemTablespaceUsersInfo_&s_dbname._&s_machine

ttitle left &s_curDate -
center "Users having SYSTEM as default tablespace in " &s_dbname " on " &s_machine skip 2

select username,
decode(default_tablespace,'SYSTEM',
decode(temporary_tablespace,'SYSTEM',
'DEFAULT AND TEMPORARY TABLESPACES','DEFAULT TABLESPACE'),
'TEMPORARY TABLESPACE') guilty
from sys.dba_users
where default_tablespace = 'SYSTEM'
or temporary_tablespace = 'SYSTEM';

spool off

ttitle off
btitle off
clear columns
clear breaks
set feedback on
set termout on
set verify on