SET SERVEROUTPUT ON
DECLARE
    ESQUEMA VARCHAR2(30) := upper('ESQUEMA_A_SER_IMPORTADO');
    SENHA_CRIPT VARCHAR2(30);
    TESTE VARCHAR2(100);
    NROLES INT;
    NSYSPRIVS INT;
    NTQUOTAS INT;
    NOBJPRIVS INT;
    NPROXY INT;
BEGIN	
    DBMS_OUTPUT.ENABLE(1000000);
    FOR X IN ( SELECT USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, PROFILE, ACCOUNT_STATUS
               FROM DBA_USERS
               WHERE USERNAME=ESQUEMA) LOOP
        SELECT PASSWORD INTO SENHA_CRIPT FROM SYS.USER$ WHERE NAME = ESQUEMA;
        BEGIN
            DBMS_OUTPUT.PUT_LINE('DROP USER ' || X.USERNAME || ' CASCADE;');
            DBMS_OUTPUT.PUT_LINE(' ');
            DBMS_OUTPUT.PUT_LINE('CREATE USER ' || X.USERNAME);
            DBMS_OUTPUT.PUT_LINE('  IDENTIFIED BY VALUES ''' || SENHA_CRIPT || '''');
            DBMS_OUTPUT.PUT_LINE('  DEFAULT TABLESPACE ' || X.DEFAULT_TABLESPACE);
            DBMS_OUTPUT.PUT_LINE('  TEMPORARY TABLESPACE ' || X.TEMPORARY_TABLESPACE);
            DBMS_OUTPUT.PUT_LINE('  PROFILE ' || X.PROFILE);
            CASE 
               WHEN X.ACCOUNT_STATUS='OPEN' THEN 
                    DBMS_OUTPUT.PUT_LINE('  ACCOUNT UNLOCK;');
                WHEN X.ACCOUNT_STATUS='EXPIRED' THEN 
                    DBMS_OUTPUT.PUT_LINE('  PASSWORD EXPIRE');
                    DBMS_OUTPUT.PUT_LINE('  ACCOUNT UNLOCK;');                    
                WHEN X.ACCOUNT_STATUS='EXPIRED(GRACE)' THEN 
                    DBMS_OUTPUT.PUT_LINE('  PASSWORD EXPIRE');
                    DBMS_OUTPUT.PUT_LINE('  ACCOUNT UNLOCK;');
                WHEN X.ACCOUNT_STATUS='LOCKED(TIMED)' THEN 
                    DBMS_OUTPUT.PUT_LINE('  ACCOUNT LOCK;');
                WHEN X.ACCOUNT_STATUS='LOCKED' THEN 
                    DBMS_OUTPUT.PUT_LINE('  ACCOUNT LOCK;');
                ELSE
                    DBMS_OUTPUT.PUT_LINE('  PASSWORD EXPIRE'); 
                    DBMS_OUTPUT.PUT_LINE('  ACCOUNT LOCK;');                
            END CASE;
            SELECT COUNT(*) INTO NROLES FROM DBA_ROLE_PRIVS WHERE GRANTEE=ESQUEMA;
            IF NROLES >= 1 THEN
                DBMS_OUTPUT.PUT_LINE('  -- ' || NROLES || ' Role(s) for ' || ESQUEMA);
            END IF;
            FOR R IN (SELECT GRANTED_ROLE,ADMIN_OPTION FROM DBA_ROLE_PRIVS WHERE GRANTEE=ESQUEMA) LOOP
                BEGIN
                    IF R.ADMIN_OPTION ='YES' THEN
                        DBMS_OUTPUT.PUT_LINE('  GRANT ' || R.GRANTED_ROLE || ' TO ' || ESQUEMA || ' WITH ADMIN OPTION;');
                    ELSE
                        DBMS_OUTPUT.PUT_LINE('  GRANT ' || R.GRANTED_ROLE || ' TO ' || ESQUEMA || ';');
                    END IF;
                END;                
            END LOOP;
            SELECT COUNT(*) INTO NROLES FROM DBA_ROLE_PRIVS WHERE GRANTEE=ESQUEMA AND DEFAULT_ROLE<>'YES';
            IF NROLES < 1 THEN
                DBMS_OUTPUT.PUT_LINE('  ALTER USER ' || ESQUEMA || ' DEFAULT ROLE ALL;');
            ELSE
                FOR DR IN (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE=ESQUEMA AND DEFAULT_ROLE='YES') LOOP
                    BEGIN
                        DBMS_OUTPUT.PUT_LINE('  ALTER USER ' || ESQUEMA || ' DEFAULT ROLE ' || DR.GRANTED_ROLE || ';');
                    END;
                END LOOP;                
            END IF;                
            SELECT COUNT(*) INTO NSYSPRIVS FROM DBA_SYS_PRIVS WHERE GRANTEE=ESQUEMA;
            IF NSYSPRIVS >= 1 THEN
                DBMS_OUTPUT.PUT_LINE('  -- ' || NSYSPRIVS || ' System Privilege(s) for ' || ESQUEMA);
            END IF;
            FOR SP IN (SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE=ESQUEMA) LOOP
                BEGIN
                    DBMS_OUTPUT.PUT_LINE('  GRANT ' || SP.PRIVILEGE || ' TO ' || ESQUEMA || ';');
                END;
            END LOOP;
            SELECT COUNT(*) INTO NTQUOTAS FROM DBA_TS_QUOTAS WHERE USERNAME=ESQUEMA;
            IF NTQUOTAS >= 1 THEN
                DBMS_OUTPUT.PUT_LINE('  -- ' || NTQUOTAS || ' Tablespace Quota(s) for ' || ESQUEMA);
            END IF;
            FOR TQ IN (SELECT TABLESPACE_NAME FROM DBA_TS_QUOTAS WHERE USERNAME=ESQUEMA) LOOP
                BEGIN
                    DBMS_OUTPUT.PUT_LINE('  ALTER USER ' || ESQUEMA || ' QUOTA UNLIMITED ON ' || TQ.TABLESPACE_NAME || ';');
                END;
            END LOOP;
            SELECT COUNT(DISTINCT(TABLE_NAME)) INTO NOBJPRIVS FROM DBA_TAB_PRIVS WHERE GRANTEE=ESQUEMA;
            IF NOBJPRIVS >= 1 THEN
                DBMS_OUTPUT.PUT_LINE('  -- ' || NOBJPRIVS || ' Object Privilege(s) for ' || ESQUEMA);
            END IF;
            FOR TP IN (SELECT OWNER,TABLE_NAME,PRIVILEGE FROM DBA_TAB_PRIVS WHERE GRANTEE=ESQUEMA) LOOP
                BEGIN
                    DBMS_OUTPUT.PUT_LINE('  GRANT ' || TP.PRIVILEGE || ' ON ' || TP.OWNER || '.' || TP.TABLE_NAME || ' TO ' || ESQUEMA || ';');
                END;
            END LOOP;
            SELECT COUNT(PROXY) INTO NPROXY FROM DBA_PROXIES WHERE CLIENT=ESQUEMA;
            IF NPROXY >= 1 THEN
                DBMS_OUTPUT.PUT_LINE('  -- ' || NPROXY || ' Proxy(ies) for ' || ESQUEMA);
            END IF;
            FOR P IN (SELECT PROXY,CLIENT FROM DBA_PROXIES WHERE CLIENT=ESQUEMA) LOOP
                BEGIN
                    DBMS_OUTPUT.PUT_LINE('  ALTER USER ' || ESQUEMA || ' GRANT CONNECT THROUGH ' || P.PROXY || ';');
                END;
            END LOOP;
        END;
    END LOOP;
END;