Oracle create user script  ....

I am creating script to create scott user .

edit user_script.sql script as per your requirement .

create user script

user_script.sql

SQL> !cat user_script.sql
--accept dbaconnect char default '/' -
-- prompt 'Enter DBA username/password [ default / ] : '
set verify off scan on pagesize 0 feedback off recsep off echo off pause off
spool create_SCOTT.sql
select 'create user SCOTT identified by SCOTT' || chr(10) ||'default tablespace ' || default_tablespace || chr(10) ||'temporary tablespace ' || temporary_tablespace || chr(10) ||'profile ' || profile || chr(10) ||'/'
from dba_users
where username = upper('SCOTT')
/
select 'alter user SCOTT' || chr(10) ||'quota ' || decode(sign(nvl(max_bytes, -1)),-1, 'unlimited ',to_char(max_bytes / 1024) || ' K ')|| 'on ' || tablespace_name || chr(10) ||'/'
from dba_ts_quotas
where username = upper('SCOTT')
and max_bytes != 0
/
select 'grant ' || privilege || ' to SCOTT' || chr(10) ||
decode(admin_option, 'YES', 'with admin option;', '/')
from dba_sys_privs
where grantee = upper('SCOTT')
/
--
-- Non default roles are not handled by the procedure
--
select 'grant ' || granted_role || ' to SCOTT' || chr(10) ||decode(admin_option, 'YES', 'with admin option;', '/')
from dba_role_privs
where grantee = upper('SCOTT')
/
column dummy noprint
select grantor dummy,1 dummy,'connect SCOTT;dbaconnect' || chr(10) ||'@@become ' || grantor
from (select grantor from dba_tab_privs
where grantee = upper('SCOTT')
union
select grantor from dba_col_privs
where grantee = upper('SCOTT'))
union
select grantor, 2,'grant ' || privilege || ' on ' || owner || '.' || table_name|| chr(10) ||'to SCOTT' || decode(grantable, 'YES', ' with grant option;', ';')
from dba_tab_privs
where grantee = upper('SCOTT')
union
select grantor, 3,'grant ' || privilege || ' on ' || owner || '.' || table_name || '(' || column_name || ')' || chr(10) ||'to SCOTT' || decode(grantable, 'YES', ' with grant option;', ';')
from dba_col_privs
where grantee = upper('SCOTT')
order by 1, 2
/
--
-- Reconnect as the original DBA
--
--select 'connect SCOTT;dbaconnect' || chr(10) ||'@@become ' || USER
--from dual
/
spool off
set feedback on
-- start create_SCOTT




SQL> @user_script.sql
create user SCOTT identified by SCOTT
default tablespace USERS
temporary tablespace TEMP
profile DEFAULT
/
grant UNLIMITED TABLESPACE to SCOTT
/
grant RESOURCE to SCOTT
/
grant CONNECT to SCOTT
/



SQL> !cat create_SCOTT.sql
create user SCOTT identified by SCOTT
default tablespace USERS
temporary tablespace TEMP
profile DEFAULT
/
grant UNLIMITED TABLESPACE to SCOTT
/
grant RESOURCE to SCOTT
/
grant CONNECT to SCOTT
/