col bd new_value p_bd noprint
col uf new_value p_uf noprint
set define on verify off feed off termout off trimspool on head off pages 1000 serverout on

select instance_name bd, substr(host_name,1,2) uf from v$instance;

spool D:\temp\&p_bd._users.sql

declare

     cursor usu_cursor is
            select   username,decode(password,'EXTERNAL','EXTERNALLY','BY VALUES '''||password||'''') password,
                     default_tablespace, 'profile '||profile profile, ' account '||decode(account_status,'LOCKED','LOCK','UNLOCK') account
            from   sys.dba_users
            where  username not in ('SYS','SYSTEM','OUTLN','DBSNMP','OEM','ODSCOMMON','ODS','NAMES','PUBLIC','ECO_IRM','CTXSYS','EXFSYS','MGMT_VIEW','ORACLE_OCM','SYSMAN','TSMSYS','WMSYS','ADMPSSTSE')
            order by username;

     cursor quota_cursor (p_usuario varchar2) is
            select username, tablespace_name, max_bytes
            from   sys.dba_ts_quotas
            where  username = p_usuario
            and    tablespace_name not in ('SYSTEM','TEMP','RBS');

     -- Atribui roles a roles e/ou usuários
     cursor atr_role is
            select 'grant '||granted_role||' to "'||grantee||'";'||chr(10) cmd
            from   dba_role_privs
            where  grantee not in ('SYS','SYSTEM','OUTLN','DBSNMP','OEM','ODSCOMMON','ODS','NAMES','PUBLIC','ECO_IRM','CTXSYS','EXFSYS','MGMT_VIEW','ORACLE_OCM','SYSMAN','TSMSYS','WMSYS','ADMPSSTSE');

     -- Atribui privilégios de sistema a roles
     cursor atr_sys_privs is
            select 'grant '||privilege||' to "'||grantee||'";'||chr(10) cmd
            from   dba_sys_privs
            where  grantee not in ('PUBLIC','SYS','ECO_IRM','_NEXT_USER','AQ_ADMINISTRATOR_ROLE','AQ_USER_ROLE','CONNECT','DBA','DELETE_CATALOG_ROLE','EXECUTE_CATALOG_ROLE',
                                   'EXP_FULL_DATABASE','HS_ADMIN_ROLE','IMP_FULL_DATABASE','OEM_MONITOR','RECOVERY_CATALOG_OWNER','RESOURCE','SELECT_CATALOG_ROLE','SNMPAGENT',
                                   'OEM','ECO_IRM','ADMPSSTSE');
   -- Atribui privilégios aos objetos do SYS
     cursor atr_sys_obj is
            select 'grant ' || privilege || ' on sys.' || table_name || ' to ' || grantee || ';' cmd
            from   dba_tab_privs a, dba_objects b
            where  a.owner='SYS'
            and    a.grantee not in ( 'PUBLIC', 'SNMPAGENT', 'OUTLN', 'SYSTEM', 'DBA' )
            and    a.grantee not like '%ROLE'
            and    a.grantee not like '%_FULL_DATABASE'
      and    table_name = object_name
      and    object_type != 'DIRECTORY';

   cursor atr_sys_dir is
        select 'grant ' || privilege || ' on directory sys.' || table_name || ' to "' || grantee || '";' cmd
            from   dba_tab_privs a, dba_objects b
            where  a.owner='SYS'
            and    a.grantee not in ( 'PUBLIC', 'SNMPAGENT', 'OUTLN', 'SYSTEM', 'DBA' )
            and    a.grantee not like '%ROLE'
            and    a.grantee not like '%_FULL_DATABASE'
      and    table_name = object_name
      and    object_type = 'DIRECTORY';

begin

     dbms_output.enable(1000000);
     dbms_output.put_line('spool &p_bd._users.sql.out');
     dbms_output.put_line(chr(10)||'prompt Criando usuários....'||chr(10));

     for rec_usu in usu_cursor loop

--         dbms_output.put_line('create user '||rec_usu.username||' identified '||rec_usu.password);
         dbms_output.put_line('alter user "'||rec_usu.username||'"') ;
         dbms_output.put_line('default tablespace '||rec_usu.default_tablespace||' temporary tablespace TEMP');
      --   dbms_output.put_line('default tablespace users2 temporary tablespace TEMP');
         dbms_output.put_line(rec_usu.profile||' '||rec_usu.account);

         for rec_quota in quota_cursor(rec_usu.username) loop
             dbms_output.put_line('quota '||replace(rec_quota.max_bytes,'-1','unlimited')||' on '||rec_quota.tablespace_name);
         end loop;

         dbms_output.put_line('/');

     end loop;

     for rec_atr_role in atr_role loop
         dbms_output.put_line(rec_atr_role.cmd);
     end loop;
     for rec_atr_sys_privs in atr_sys_privs loop
         dbms_output.put_line(rec_atr_sys_privs.cmd);
     end loop;
     for rec_atr_sys_obj in atr_sys_obj loop
         dbms_output.put_line(rec_atr_sys_obj.cmd);
     end loop;
   for rec_atr_sys_dir in atr_sys_dir loop
         dbms_output.put_line(rec_atr_sys_dir.cmd);
     end loop;

     dbms_output.put_line(chr(10)||'spool off');

end;
/

spool off
set define "&" feed on termout on head on pages 60