select 'create user ' 
    || username 
    || ' identified by ' 
    || username 
    || ' default tablespace ' 
    || default_tablespace 
    || ' quota unlimited on ' 
    || default_tablespace 
    || ';' 
from dba_users 
where username like 'SCOTT%';

Добавить полномочия на необходимые дополнительные табличные пространства

select 'alter user ' 
    || username 
    || ' unlimited quota on '
    || tablespace_name 
    || 
from dba_ts_quotas 
where username like 'SCOTT%';

set serveroutput on;
set HEADING ON;
SET FEED off;
select 'create tablespace '|| tablespace_name ||
          ' datafile '''|| file_name ||
          ''' size '|| bytes/1024/1024 ||
          'M autoextend on next '|| increment_by||';' command 
from dba_data_files where tablespace_name in 
            (select tablespace_name from dba_ts_quotas where username='&&uname'
              union
            select default_tablespace from dba_users where username='&&uname')
order by command;

select 'create user '||username||' identified by '|| username
  ||' default tablespace '||default_tablespace||' quota unlimited on '
  ||default_tablespace||';' command 
from dba_users 
where username='&&uname';

select 'alter user ' || username || ' unlimited quota on '|| tablespace_name ||';' command 
from dba_ts_quotas 
where username='&&uname';

select 'grant '|| privilege||' to '|| grantee||';' command 
from dba_sys_privs 
where grantee='&&uname'
union
select 'grant '|| granted_role ||' to '|| grantee||';' command 
from dba_role_privs 
where grantee='&&uname'
union
select 'grant '|| privilege ||' on ' || owner||'.'|| table_name||' to '|| grantee||';' command 
from dba_tab_privs 
where grantee='&&uname';