How To Create Users Like Another User In Oracle Database At SQL Command Line, Not From OEM (Doc ID 1352212.1)

Solution 1:
exec  dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);

select dbms_metadata.get_ddl( 'USER', '<USERNAME>' ) from dual
    UNION ALL
    select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', '<USERNAME>' ) from dual
    UNION ALL
    select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', '<USERNAME>' ) from dual
    UNION ALL
    select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', '<USERNAME>' ) from dual;

Solution 2:
To get the user's privileges you can query the following tables:

--  For Table privileges: DBA_TAB_PRIVS

-- For Column privileges: DBA_COL_PRIVS

-- For System privileges: DBA_SYS_PRIVS

-- For Roles:  DBA_ROLE_PRIVS


You can also use the DBMS_METADATA package:

-- For object privileges granted to a user :
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',upper('&GRANTED_USER'))
FROM DUAL;

-- For system privileges granted to a user :
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',upper('&GRANTED_USER'))
FROM DUAL;

-- For roles granted to a user :
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',upper('&GRANTED_USER'))
FROM DUAL;

The following scripts can be used to create a user identical to another user :

NOTE: The commands are provided as is and are not supported by Oracle.
You will have to verify yourself that the commands are applicable to your environment.

set pages 0 feed off veri off lines 500

accept oldname prompt "Enter user to model new user to: "
accept newname prompt "Enter new user name: "
accept psw prompt "Enter new user's password: "

-- Create user...
select 'create user &&newname identified by &psw'||
' default tablespace '||default_tablespace||
' temporary tablespace '||temporary_tablespace||' profile '||
profile||';'
from sys.dba_users
where username = upper('&&oldname');

-- Grant Roles...
select 'grant '||granted_role||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_role_privs
where grantee = upper('&&oldname');

-- Grant System Privs...
select 'grant '||privilege||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_sys_privs
where grantee = upper('&&oldname');

-- Grant Table Privs...
select 'grant '||privilege||' on '||owner||'.'||table_name||' to &&newname;'
from sys.dba_tab_privs
where grantee = upper('&&oldname');

-- Grant Column Privs...
select 'grant '||privilege||' on '||owner||'.'||table_name||
'('||column_name||') to &&newname;'
from sys.dba_col_privs
where grantee = upper('&&oldname');

-- Set tablespace Quotas...
select 'alter user '||username||' quota '||
decode(max_bytes, -1, 'UNLIMITED', max_bytes)||
' on '||tablespace_name||';'
from sys.dba_ts_quotas
where username = upper('&&oldname');
NOTE:
The above generated commands must be executed to actually create the new user with its privileges


-- Set Default Role...
set serveroutput on
declare
defroles varchar2(4000);
begin
  for c1 in (select * from sys.dba_role_privs
             where grantee = upper('&&oldname')
             and default_role = 'YES'
            )
  loop
    if length(defroles) > 0 then
      defroles := defroles||','||c1.granted_role;
    else
      defroles := defroles||c1.granted_role;
    end if;
  end loop;
dbms_output.put_line('alter user &&newname default role '||defroles||';');
end;
/

.........To find the user details with out any error ......
select (case
        when ((select count(*)
               from   dba_users
               where  username = '&&Username') > 0)
        then  dbms_metadata.get_ddl ('USER', '&&Username')
        else  to_clob ('   -- Note: User not found!')
        end ) Extracted_DDL from dual
UNION ALL
select (case
        when ((select count(*)
               from   dba_ts_quotas
               where  username = '&&Username') > 0)
        then  dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', '&&Username')
        else  to_clob ('   -- Note: No TS Quotas found!')
        end ) from dual
UNION ALL
select (case
        when ((select count(*)
               from   dba_role_privs
               where  grantee = '&&Username') > 0)
        then  dbms_metadata.get_granted_ddl ('ROLE_GRANT', '&&Username')
        else  to_clob ('   -- Note: No granted Roles found!')
        end ) from dual
UNION ALL
select (case
        when ((select count(*)
               from   dba_sys_privs
               where  grantee = '&&Username') > 0)
        then  dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', '&&Username')
        else  to_clob ('   -- Note: No System Privileges found!')
        end ) from dual
UNION ALL
select (case
        when ((select count(*)
               from   dba_tab_privs
               where  grantee = '&&Username') > 0)
        then  dbms_metadata.get_granted_ddl ('OBJECT_GRANT', '&&Username')
        else  to_clob ('   -- Note: No Object Privileges found!')
        end ) from dual
/