Doc

              Name:     systsp.sql

              Author:   Mark Gurry

              Having your system tablespace used for purposes other than storing the Oracle               
              dictionary can not only cause poor performance, but also cause the database to crash 
              if  a dictionary table needs to be extended and it has insufficient free disk space.              

#

set heading off
set linesize 100
set echo off
set feedback off
set verify off

column owner format a10;
column segment_name format a25;
column segment_type format a25;
set pagesize 999
spool systsp.lis
column nl newline;

select '    The Following is a list of all objects that are owned by users other than SYS and SYSTEM ' nl,
       '                        but are stored in the SYSTEM tablespace....' nl
  from dual         
 where 0 <
       ( select count(*) 
           from dba_segments 
          where owner not in ('SYS', 'SYSTEM')
            and tablespace_name = 'SYSTEM'  )
/

break on owner skip 1;
select '            ', owner, segment_name, segment_type
  from dba_segments 
 where owner not in ('SYS', 'SYSTEM')
   and tablespace_name = 'SYSTEM'  
  order by owner, segment_name
/

select '          You have modified your SYSTEM Tablespace PCTINCREASE to '||pct_increase  nl,
       '                  This is different to Oracles recommended setting of 50' nl,
       '              Make sure that you will not have any problems as a result of this. ' nl
  from dba_tablespaces
 where pct_increase != 50
  and  tablespace_name = 'SYSTEM'
/


select '        The Following Users have the SYSTEM tablespace as their Default Tablespace.               ' nl,
       '             This is bad practice because it can often cause the SYSTEM  ' nl,
       '                  tablespace to fill and Oracle to grind to a halt.            ' nl
  from dual         
 where 0 <
       ( select count(*) 
           from dba_users     
          where username not in ('SYS', 'SYSTEM')
            and default_tablespace = 'SYSTEM'  )
/

select '                                  ', username
  from dba_users     
 where username not in ('SYS', 'SYSTEM')
   and default_tablespace = 'SYSTEM' 
  order by username
/
 
select '        The Following Users have the SYSTEM tablespace as their Temporary Tablespace.               ' nl,
       '             This is bad practice because it can often cause the SYSTEM  ' nl,
       '                  tablespace to fill and Oracle to grind to a halt.            ' nl
  from dual         
 where 0 <
       ( select count(*) 
           from dba_users     
          where username not in ('SYS', 'SYSTEM')
            and temporary_tablespace = 'SYSTEM'  )
/

select '                                  ', username
  from dba_users     
 where username not in ('SYS', 'SYSTEM')
   and temporary_tablespace = 'SYSTEM' 
  order by username
/
 

spool off