Create New User Based on Existing User

Here the below values are hard coded for testing purpose,you can replace them with bind variables.

Old User Name : XIBMO61
New User Name: CHANDRA
New Password : WELCOME

Note: Please make sure that you have sufficient privileges to execute the below statement.

-- Create user...
SELECT    'create user CHANDRA identified by values '''
              || 'WELCOME''' ||
                  ' default tablespace '
       || default_tablespace
       || ' temporary tablespace '
       || temporary_tablespace
       || ' profile '
       || PROFILE
       || ';'
  FROM SYS.dba_users
 WHERE username = UPPER('XIBMO61');



-- Grant Roles...
SELECT 'grant ' || granted_role || ' to CHANDRA' || DECODE(admin_option, 'YES', ' WITH ADMIN OPTION') || ';'
  FROM SYS.dba_role_privs
 WHERE grantee = UPPER('XIBMO61');

-- Grant System Privs...
SELECT 'grant ' || PRIVILEGE || ' to CHANDRA' || DECODE(admin_option, 'YES', ' WITH ADMIN OPTION') || ';'
  FROM SYS.dba_sys_privs
 WHERE grantee = UPPER('XIBMO61');

-- Grant Table Privs...
SELECT 'grant ' || PRIVILEGE || ' on ' || owner || '.' || table_name || ' to CHANDRA;'
  FROM SYS.dba_tab_privs
 WHERE grantee = UPPER('XIBMO61');

-- Grant Column Privs...
SELECT 'grant ' || PRIVILEGE || ' on ' || owner || '.' || table_name || '(' || column_name || ') to CHANDRA;'
  FROM SYS.dba_col_privs
 WHERE grantee = UPPER('XIBMO61');

-- 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('XIBMO61');

-- Set Default Role...
SET serveroutput on

DECLARE
   defroles                      VARCHAR2(4000);
BEGIN
   FOR c1 IN (SELECT *
                FROM SYS.dba_role_privs
               WHERE grantee = UPPER('XIBMO61') 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 CHANDRA default role ' || defroles || ';');
END;
/