Below a little script that will allow to create a user like another one in an easy way
begin
-- This pl/sql block creates a user like another one
declare v_username varchar2(30) := '<username_like>';
v_username_new varchar2(30) := '<new_user>';
v_pwd varchar2(30):= 'ChangeMe123___';
defroles varchar2(500);
user_exists integer;
v_stmt varchar2(500);
begin
select count(*) into user_exists from dba_users where username=v_username;
execute immediate 'drop user ' || v_username_new;
if user_exists> 0 then
select 'create user ' || v_username_new || ' identified by "' || v_pwd || '" default tablespace '||default_tablespace|| ' temporary tablespace '||temporary_tablespace||' profile '|| profile || ' PASSWORD EXPIRE' into v_stmt
from sys.dba_users
where username = upper(v_username);
dbms_output.put_line (v_stmt);
execute immediate v_stmt;
-- grant roles
begin
for c0 in (select 'grant '||granted_role||' to ' || v_username_new || ' '||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION') as stmt
from sys.dba_role_privs
where grantee = upper(v_username))
loop
dbms_output.put_line ('v_stmt2' || c0.stmt);
execute immediate c0.stmt;
end loop;
exception when no_data_found then
dbms_output.put_line ('no data found');
end;
-- grant system privs
begin
for c0 in (select 'grant '||privilege||' to '|| v_username_new || ' ' ||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION') as stmt
from sys.dba_sys_privs
where grantee = upper(v_username))
loop
dbms_output.put_line (c0.stmt);
execute immediate c0.stmt;
end loop;
exception when no_data_found then
dbms_output.put_line ('');
end;
-- grant table privs
begin
for c0 in (select 'grant '||privilege||' on '||owner||'.'||table_name||' to ' || v_username_new as stmt
from sys.dba_tab_privs
where grantee = upper(v_username)
and type != 'DIRECTORY')
loop
dbms_output.put_line (c0.stmt);
execute immediate c0.stmt;
end loop;
exception when no_data_found then
dbms_output.put_line ('');
end;
-- grant directory privs
begin
for c0 in (select 'grant '||privilege||' on directory '||owner||'.'||table_name||' to ' || v_username_new as stmt
from sys.dba_tab_privs
where grantee = upper(v_username)
and type = 'DIRECTORY')
loop
dbms_output.put_line (c0.stmt);
execute immediate c0.stmt;
end loop;
exception when no_data_found then
dbms_output.put_line ('');
end;
-- tbs quotas
begin
for c0 in (select 'alter user '||username||' quota '||
decode(max_bytes, -1, 'UNLIMITED', max_bytes)||
' on '||tablespace_name as stmt
from sys.dba_ts_quotas
where username = upper(v_username))
loop
dbms_output.put_line (c0.stmt);
execute immediate c0.stmt;
end loop;
exception when no_data_found then
dbms_output.put_line ('');
end;
--default role
for c1 in (select granted_role as grrole from dba_role_privs
where grantee = upper(v_username)
and default_role = 'YES'
)
loop
execute immediate 'alter user ' || v_username_new || ' ' || ' default role '|| c1.grrole;
end loop;
end if;
end;
end;
/