Script to clone one user and his privilages to another user

rem --------------------------------------------------------------------------------------------------------
rem Name                : CLONEUSER.SQL
rem
rem Compatible versions : 10.x and above
rem
rem Description         : This script will generate SQL file with DDLs for user creation, associated grants
rem                       and privilegs (roles, system privileges, object privileges and tablespace quotas)
rem
rem prerequisites       : - You must login with DBA role to execute this script
rem                       - Package DBMS_METADATA must be in valid status
rem
rem --------------------------------------------------------------------------------------------------------
rem
----------------------
-- Setting SQL prompt:
----------------------
whenever sqlerror exit;
set serveroutput on long 100000000 pagesize 60 linesize 150 heading off feedback off verify off echo off
--------------------------------------------------
-- Handling Exception for Insufficient privileges:
--------------------------------------------------
declare
        privusercnt     number;
begin
    select count(username)
    into   privusercnt
    from   user_role_privs
    where  granted_role = 'DBA'
    and    username = ora_login_user;
    if ( privusercnt = 0 ) then
      raise_application_error
         (-20001, 'ERROR !'
          || CHR (10)
          || '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
          || CHR (10)
          || 'INFO: Insufficient privileges. You must login with DBA role to execute this script'
          || CHR (10)
          || '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
          || CHR (10)
          || CHR (10)
          || 'Terminating the script...'
         );
    end if;
end;
/
-------------------------------------------------------
-- Handling Exception for Invaid DBMS_METADATA package:
-------------------------------------------------------
declare
        invalidstatcnt  number;
begin
    select count(status)
    into   invalidstatcnt
    from   dba_objects
    where  object_name = 'DBMS_METADATA'
    and    status <> 'VALID';
    if ( invalidstatcnt > 0 ) then
      raise_application_error
         (-20001, 'ERROR !'
          || CHR (10)
          || '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
          || CHR (10)
          || 'INFO: Package SYS.DBMS_METADATA is not in valid status. Please recompile'
          || '      the package and package body, and try again.'
          || CHR (10)
          || '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
          || CHR (10)
          || CHR (10)
          || 'Terminating the script...'
         );
    end if;
end;
/
column dbname noprint new_value db_name
column fdate noprint new_value datestamp

select name dbname,
       to_char(sysdate,'yyyymmddhh24miss') fdate
from   v$database
/
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt ATTENTION:
prompt
prompt (1) A NULL | ENTER for source user will abort the script.
prompt (2) A NULL | ENTER for target user will keep the same source user name as target user.
prompt (3) To abort the script execution, please use CTRL+d
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt
accept sourceuser prompt "Enter source user for generating DDL : "
----------------------------------------------
-- Handling Exception for Invalid source user:
----------------------------------------------
declare
        sourceusercount number;
begin
    select count(username)
    into   sourceusercount
    from   dba_users
    where  username = upper(trim('&&sourceuser'));
    if ( sourceusercount = 0 ) then
      raise_application_error
         (-20002, 'ERROR !'
          || CHR (10)
          || '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
          || CHR (10)
          || 'INFO: Invalid username specified. Please enter a valid username'
          || CHR (10)
          || '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
          || CHR (10)
          || CHR (10)
          || 'Terminating the script...'
         );
    end if;
end;
/
prompt
accept targetuser prompt "Enter target user for generating DDL : "
prompt

------------------------------------------------------
-- Setting Transform Parameters for the DDL Transform:
------------------------------------------------------
begin
    dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
    dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/
------------------
-- Generating DDL:
------------------
spool user_ddl_&&sourceuser._&&db_name._&&datestamp..sql

prompt
prompt -- (1). DDL for user creation:
-------------------------------------
prompt
prompt -- Notes:
prompt
prompt -- (1) Please edit the password, default tablespace, temporary tablespace for target user.
prompt -- (2) If not, all these attributes will be the same as source user.
prompt -- (3) If you are using 11g database, please make sure that the encripted passoword dont have any space | line break
prompt
col userddl format a600
select trim(replace (dbms_metadata.get_ddl('USER',upper(trim('&&sourceuser')))
  ,'USER "'||upper(trim('&&sourceuser'))||'"','USER "'||upper(trim(nvl('&&targetuser','&&sourceuser')))||'"')) userddl
from dual
where exists
  (select 'x'
  from dba_users drp
  where drp.username = upper(trim('&&sourceuser')))
/
set linesize 110
prompt
prompt -- (2). DDL for tablespace quotas:
-----------------------------------------
prompt
prompt -- Note: - Please verify and edit the target tablespace names, quotas by checking contents.
prompt
select 'ALTER USER '
  ||replace(username,upper(trim('&&sourceuser')),upper(trim(nvl('&&targetuser','&&sourceuser'))))
  ||' QUOTA '
  ||decode(max_bytes,-1,'UNLIMITED',max_bytes)
  ||' ON '
  ||tablespace_name
  ||';' ddl
from dba_ts_quotas
where username = upper(trim('&&sourceuser'))
and  exists
  (select 'x'
  from dba_ts_quotas drp
  where drp.username = upper(trim('&&sourceuser')))
/
/*
----------------------------------------------------------------------------------------------------------
Note:
-----
The below SQL can be useed instead of the above TABLESPACE QUOTA generation command
this will generate the DDL in PL/SQL block format.

select replace (dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA',upper(trim('&&sourceuser')))
  ,'"'||upper(trim('&&sourceuser'))||'"','"'||upper(trim(nvl('&&targetuser','&&sourceuser')))||'"') ddl
from dual
where exists
  (select 'x'
  from dba_ts_quotas drp
  where drp.username = upper(trim('&&sourceuser')))
/
----------------------------------------------------------------------------------------------------------
*/
prompt
prompt -- (3). DDL for roles:
-----------------------------
select replace (dbms_metadata.get_granted_ddl('ROLE_GRANT',upper(trim('&&sourceuser' )))
  ,'"'||upper(trim('&&sourceuser'))||'"','"'||upper(trim(nvl('&&targetuser','&&sourceuser')))||'"') ddl
from dual
where exists
  (select 'x'
  from dba_role_privs drp
  where drp.grantee = upper(trim('&&sourceuser')))
/
-- DDL for alter user with default role
--
select replace (dbms_metadata.get_granted_ddl('DEFAULT_ROLE',upper(trim('&&sourceuser' )))
  ,'"'||upper(trim('&&sourceuser'))||'"','"'||upper(trim(nvl('&&targetuser','&&sourceuser')))||'"') ddl
from dual
where exists
  (select 'x'
  from dba_role_privs drp
  where drp.grantee = upper(trim('&&sourceuser')))
/

prompt
prompt -- (4). DDL for system privileges:
-----------------------------------------
select replace (dbms_metadata.get_granted_ddl('SYSTEM_GRANT',upper(trim( '&&sourceuser')))
  ,'"'||upper(trim('&&sourceuser'))||'"','"'||upper(trim(nvl('&&targetuser','&&sourceuser')))||'"') ddl
from dual
where exists
  (select 'x'
  from dba_sys_privs drp
  where drp.grantee = upper(trim('&&sourceuser')))
/

prompt
prompt -- (5). DDL for object privileges:
-----------------------------------------
select replace (dbms_metadata.get_granted_ddl('OBJECT_GRANT',upper(trim( '&&sourceuser')))
  ,'"'||upper(trim('&&sourceuser'))||'"','"'||upper(trim(nvl('&&targetuser','&&sourceuser')))||'"') ddl
from dual
where exists
  (select 'x'
  from dba_tab_privs drp
  where drp.grantee = upper(trim('&&sourceuser')))
/

spool off

prompt
prompt Post steps:
prompt ~~~~~~~~~~~
prompt INFO: (1) Please review, edit and execute the script user_ddl_&&sourceuser._&&db_name._&&datestamp..sql in target database.
prompt INFO: (2) Please remove space | line break from the SQL file, before execution
prompt
------------------------
-- Resetting SQL prompt:
------------------------
prompt
prompt

undefine sourceuser targetuser db_name datestamp
whenever sqlerror continue;
set heading on feedback on verify on echo on
exit;