rem Name:    altusr.sql
rem Purpose: Set user tablespace defaults
rem Usage:   @altusr <%user%> <%role%> <default_ts|*> <temp_ts|*>
rem Subject: user
rem Attrib:  sql gen ddl

@setup
set heading off

spool altusr.tmp

select
  'alter user '||u.username||
  decode('&&3', '*', '', &&cr||'default tablespace '||'&&3')||
  decode('&&4', '*', '', &&cr||'temporary tablespace '||'&&4')||
  ';'
from sys.dba_users u
where u.username like upper('&&1')
and u.username <> 'SYS'
and (upper('&&2') in ('*', '%')
     or exists (
           select null
           from sys.dba_role_privs
           where grantee = u.username
           and granted_role like upper('&&2')
     )
)
and (('&&3' <> '*'
     and u.default_tablespace <> upper('&&3'))
     or ('&&4' <> '*' and u.temporary_tablespace <> upper('&&4'))
)
order by u.username
;

spool off
@doit
set feedback on
@altusr.tmp
@setdefs

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips