SPOOL create_users.sql REPLACE
SET LINESIZE 3000
SET TRIMSPOOL ON
SET SERVEROUTPUT ON
SET FEEDBACK OFF

declare
  sqltext clob;
  not_found exception;
  pragma exception_init(not_found, -31603);
begin
  for i in (select UPPER(LOGINNAME) LOGINNAME FROM kfm.users where UPPER(LOGINNAME) <> 'KFM' order by 1)
  loop
    begin
      select dbms_metadata.get_ddl('USER', i.LOGINNAME) into sqltext FROM DUAL;
      select regexp_replace(sqltext, 'DEFAULT TABLESPACE[[:space:]]*"[[:alnum:]]"', 'DEFAULT TABLESPACE "KFM"') into sqltext FROM DUAL;
      select regexp_replace(sqltext, '.*TEMPORARY TABLESPACE.*', ';
		GRANT CREATE SESSION TO "' || i.LOGINNAME || '";
		GRANT KFM_ROLE TO "' || i.LOGINNAME || '";') into sqltext FROM DUAL;
      DBMS_OUTPUT.put_line(sqltext);
    exception
      when not_found then
        null;
      when others then
        raise;
    end;
  end loop;
end;
/

SPOOL OFF

exit
