Create Oracle user with same privileges of other user on same tablespace

[해리]

* Generate sql to create a user

set pages 0 feed off veri off lines 500 

accept oldname prompt "Enter user to model new user " 

accept newname prompt "Enter new user Name " 

accept pw prompt "Enter Users Password " 

spool users.sql 

select 'create user &&newname identified by &&pw'|| 

' default tablespace '||default_tablespace|| 

' temporary tablespace '||temporary_tablespace||' profile ' || 

profile||';' 

from sys.dba_users 

where username = upper('&&oldname'); 

select 'grant '||granted_role|| ' to &&newname'|| 

decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';' 

from sys.dba_role_privs 

where grantee = upper('&&oldname'); 

select 'alter user &&newname default role '|| granted_role ||';' 

from sys.dba_role_privs 

where grantee = upper('&&oldname') 

and default_role = 'YES'; 

select 'grant '||privilege||' to &&newname'|| 

decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';' 

from sys.dba_sys_privs 

where grantee = upper('&&oldname'); 

select 'grant '||privilege|| ' on '||owner||'.'||table_name||' to &&newname;' 

from sys.dba_tab_privs 

where grantee = upper('&&oldname') 

and privilege in ('SELECT', 'REFERENCES'); 

select 'grant '||privilege||'on '||owner||'.'||table_name|| 

'('||column_name||') to &&newname;' 

from sys.dba_col_privs 

where grantee = upper('&&oldname'); 

select 'grant select on '||owner||'.'||table_name||' to &&newname;' 

from sys.dba_tables 

where owner = upper('&&oldname'); 

spool off