REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM 
REM File:
REM 	c_user_alter_quota.sql
REM
REM <SQLDIR_GRP>STORAGE USER MAINT TABSP</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy (vit100gain@earthlink.net)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	This script will:                         
REM		1. REVOKE UNLIMITED TABLESPACE FROM
REM		   all NON DBA users (DESIGNER and 
REM		   SYS likes are excluded).        
REM		2. Limit their quotas based on the 
REM		   current usage.                  
REM	</SQLDIR_TXT>
REM
REM Usage:
REM	c_user_alter_quota.sql
REM 
REM Example:
REM	c_user_alter_quota.sql
REM
REM
REM History:
REM	08-01-2001	VMOGILEV	Created
REM
REM

set trimspool on
set verify off
set pages 0
set lines 100
set feedback off

accept 1 prompt "Enter PCT Increase for existing ts usage: "
accept 2 prompt "Enter Quota in KB for users with no ts usage: "
accept 3 prompt "Enter Tablespace name for users with no ts usage: "

select /*+ ORDERED */
       'prompt Altering user: '||a.grantee||' (obj_exists='||DECODE(sum(b.bytes),NULL,'NO','YES')||')'
,      'ALTER USER '||a.grantee||' QUOTA '||DECODE(sum(b.bytes),
                                               NULL,'&2',
                                               ROUND(
                                                  (sum(b.bytes)+(sum(b.bytes)*TO_NUMBER('&1',99)/100))/1024
                                               )
                                            )||'K ON '||NVL(b.tablespace_name,'&3')||';'
,      'REVOKE UNLIMITED TABLESPACE FROM '||a.grantee||';'
from    (select b.grantee
         from  dba_sys_privs b
         where b.privilege like '%UNLIMITED%'
         and   INSTR(b.grantee,'DESIGNER') < 1
         and   INSTR(b.grantee,'SYS') < 1
         and   NOT EXISTS (select 'x'
                           from dba_role_privs a
                           where b.grantee = a.grantee
                           and   a.granted_role = 'DBA')) a
,       dba_segments b
where   a.grantee = b.owner(+)
group by a.grantee, b.tablespace_name
order by DECODE(sum(b.bytes),NULL,2,1), a.grantee

spool c_user_alter_ts.run
/
spool off

ed c_user_alter_ts.run

prompt to run call c_user_alter_ts.run

/*
You should also do the following ...
=====================================
select * from DBA_TS_QUOTAS
select * from dba_role_privs
select * from DBA_SYS_PRIVS where privilege = 'UNLIMITED TABLESPACE'

select 'alter user '||username||' default tablespace users;' from dba_users where default_tablespace = 'SYSTEM' and INSTR(username,'SYS') = 0
union all
select 'alter user '||username||' temporary tablespace temp;' from dba_users where temporary_tablespace = 'SYSTEM' and INSTR(username,'SYS') = 0
/

*/