Migrate roles, users, and permissions from Oracle to PolarDB for PostgreSQL (Compatible with Oracle)

Oracle 12c and later

SET HEADING OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET PAGESIZE 0
SET LINESIZE 2000
SET SERVEROUTPUT ON
-- The actual storage location for the output file. Adjust as needed.
SPOOL /my/path/to/create_role.sql

BEGIN
    FOR rec IN (
        SELECT 'CREATE ROLE ' || ROLE || ';' AS ddl_script
        FROM DBA_ROLES
        WHERE ORACLE_MAINTAINED = 'N'
        ORDER BY ROLE
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.ddl_script);
    END LOOP;
END;
/

SPOOL OFF
EXIT

Oracle 11g

SET HEADING OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET PAGESIZE 0
SET LINESIZE 2000
SET SERVEROUTPUT ON
SPOOL /my/path/to/create_role.sql

BEGIN
    FOR rec IN (
        SELECT 'CREATE ROLE ' || ROLE || ';' AS ddl_script
        FROM DBA_ROLES
        WHERE ROLE NOT IN (
            'CONNECT', 'RESOURCE', 'DBA', 'SELECT_CATALOG_ROLE',
            'EXECUTE_CATALOG_ROLE', 'DELETE_CATALOG_ROLE', 'EXP_FULL_DATABASE',
            'IMP_FULL_DATABASE', 'LOGSTDBY_ADMINISTRATOR', 'RECOVERY_CATALOG_OWNER',
            'SCHEDULER_ADMIN', 'HS_ADMIN_SELECT_ROLE', 'HS_ADMIN_EXECUTE_ROLE',
            'HS_ADMIN_ROLE', 'GLOBAL_AQ_USER_ROLE', 'OEM_ADVISOR', 'OEM_MONITOR',
            'MGMT_USER', 'DATAPUMP_EXP_FULL_DATABASE', 'DATAPUMP_IMP_FULL_DATABASE',
            'ADM_PARALLEL_EXECUTE_TASK', 'GATHER_SYSTEM_STATISTICS',
            'OPTIMIZER_PROCESSING_RATE', 'ACCESS_CATALOG_ROLE', 'JAVAUSERPRIV',
            'JAVAIDPRIV', 'JAVASYSPRIV', 'JAVADEBUGPRIV', 'EJBCLIENT', 'JMXSERVER',
            'JAVA_ADMIN', 'JAVA_DEPLOY', 'AUTHENTICATEDUSER', 'XDB_SET_INVOKER',
            'XDB_WEBSERVICES', 'XDB_WEBSERVICES_WITH_PUBLIC', 'XDB_WEBSERVICES_OVER_HTTP',
            'XDBADMIN', 'XDB_WEBSERVICES_OVER_HTTPS', 'CTXAPP', 'ORDADMIN',
            'WM_ADMIN_ROLE', 'OLAP_USER', 'OLAP_DBA', 'OLAP_XS_ADMIN',
            'CSW_USR_ROLE', 'WFS_USR_ROLE', 'SPATIAL_CSW_ADMIN',
            'SPATIAL_WFS_ADMIN', 'LBAC_DBA', 'APEX_ADMINISTRATOR_ROLE',
            'FLOWS_FILES_ROLE', 'AQ_ADMINISTRATOR_ROLE', 'AQ_USER_ROLE',
            'CWM_USER', 'DBFS_ROLE', 'OLAPI_TRACE_USER', 'OWB$CLIENT',
            'OWB_DESIGNCENTER_VIEW', 'OWB_USER'
        )
        AND ROLE NOT LIKE 'APEX\_%' ESCAPE '\'
        AND ROLE NOT LIKE 'FLOWS\_%' ESCAPE '\'
        AND ROLE NOT LIKE 'ORA\_%' ESCAPE '\'
        AND ROLE NOT LIKE 'ORACLE\_%' ESCAPE '\'
        AND ROLE NOT LIKE 'SYS\_%' ESCAPE '\'
        ORDER BY ROLE
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.ddl_script);
    END LOOP;
END;
/

SPOOL OFF
EXIT

Oracle 12c and later

SET HEADING OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET PAGESIZE 0
SET LINESIZE 2000
SET SERVEROUTPUT ON
SPOOL /my/path/to/create_user.sql

BEGIN
    FOR rec IN (
      SELECT 'CREATE USER ' || USERNAME || ';' AS ddl_script
      FROM DBA_USERS
      WHERE ORACLE_MAINTAINED = 'N'
      ORDER BY USERNAME
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.ddl_script);
    END LOOP;
END;
/

SPOOL OFF
EXIT

Oracle 11g

SET HEADING OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET PAGESIZE 0
SET LINESIZE 2000
SET SERVEROUTPUT ON
SPOOL /my/path/to/create_user.sql

BEGIN
    FOR rec IN (
        SELECT 'CREATE USER ' || USERNAME || ';' AS ddl_script
        FROM DBA_USERS
        WHERE USERNAME NOT IN (
            'SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN', 'OUTLN',
            'CTXSYS', 'MDSYS', 'ORDSYS', 'ORDDATA', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA',
            'WMSYS', 'EXFSYS', 'LBACSYS', 'XDB', 'ANONYMOUS', 'XS$NULL',
            'OLAPSYS', 'MDDATA', 'OWBSYS', 'OWBSYS_AUDIT',
            'APEX_030200', 'APEX_040000', 'APEX_040100', 'APEX_040200', 'APEX_050000',
            'APEX_PUBLIC_USER', 'FLOWS_FILES', 'FLOWS_030000', 'FLOWS_040000',
            'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR',
            'DIP', 'ORACLE_OCM', 'APPQOSSYS', 'MGMT_VIEW', 'SCOTT', 'HR',
            'TSMSYS', 'WKPROXY', 'WK_TEST', 'PERFSTAT', 'TRACESVR',
            'GSMADMIN_INTERNAL', 'GSMCATUSER', 'GSMUSER', 'SYSBACKUP', 'SYSDG', 'SYSKM'
        )
        AND USERNAME NOT LIKE 'APEX\_%' ESCAPE '\'
        AND USERNAME NOT LIKE 'FLOWS\_%' ESCAPE '\'
        AND USERNAME NOT LIKE 'ORA\_%' ESCAPE '\'
        AND USERNAME NOT LIKE 'ORACLE\_%' ESCAPE '\'
        AND USERNAME NOT LIKE 'SYS\_%' ESCAPE '\'
        ORDER BY USERNAME
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.ddl_script);
    END LOOP;
END;
/

SPOOL OFF
EXIT

Oracle 12c and later

SET HEADING OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET PAGESIZE 0
SET LINESIZE 2000
SET DEFINE OFF
SET SERVEROUTPUT ON
SPOOL /my/path/to/grant_object.sql

BEGIN
    FOR rec IN (
        SELECT
            'GRANT ' || LISTAGG(p.PRIVILEGE, ', ') WITHIN GROUP (ORDER BY p.PRIVILEGE) || ' ON ' ||
            CASE WHEN MIN(o.OBJECT_TYPE) IN ('PROCEDURE', 'FUNCTION', 'TYPE') THEN MIN(o.OBJECT_TYPE) || ' '
                 ELSE '' END ||
            '"' || p.OWNER || '"."' || p.TABLE_NAME || '" TO ' || p.GRANTEE ||
            CASE WHEN p.GRANTABLE = 'YES' THEN ' WITH GRANT OPTION' ELSE '' END ||
            ';' AS ddl_script
        FROM DBA_TAB_PRIVS p
        JOIN DBA_OBJECTS o ON p.TABLE_NAME = o.OBJECT_NAME AND o.OWNER = p.OWNER
        WHERE p.OWNER IN (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED = 'N')
          AND p.TABLE_NAME NOT LIKE 'ISEQ$$%'
          AND p.TABLE_NAME NOT LIKE 'AQ$%'
          AND o.OBJECT_TYPE NOT IN ('QUEUE', 'PACKAGE BODY', 'TYPE BODY')
          AND p.GRANTEE IN (
              SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED = 'N'
              UNION
              SELECT ROLE FROM DBA_ROLES
              UNION
              SELECT 'PUBLIC' FROM DUAL
          )
        GROUP BY p.GRANTEE, p.OWNER, p.TABLE_NAME, p.GRANTABLE
        ORDER BY p.GRANTEE, p.OWNER, p.TABLE_NAME, p.GRANTABLE
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.ddl_script);
    END LOOP;
END;
/

SPOOL OFF
EXIT

Oracle 11g

SET HEADING OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET PAGESIZE 0
SET LINESIZE 2000
SET DEFINE OFF
SET SERVEROUTPUT ON
SPOOL /my/path/to/grant_object.sql

BEGIN
    FOR rec IN (
        SELECT
            'GRANT ' || LISTAGG(p.PRIVILEGE, ', ') WITHIN GROUP (ORDER BY p.PRIVILEGE) || ' ON ' ||
            CASE WHEN MIN(o.OBJECT_TYPE) IN ('PROCEDURE', 'FUNCTION', 'TYPE') THEN MIN(o.OBJECT_TYPE) || ' '
                 ELSE '' END ||
            '"' || p.OWNER || '"."' || p.TABLE_NAME || '" TO ' || p.GRANTEE ||
            CASE WHEN p.GRANTABLE = 'YES' THEN ' WITH GRANT OPTION' ELSE '' END ||
            ';' AS ddl_script
        FROM DBA_TAB_PRIVS p
        JOIN DBA_OBJECTS o ON p.TABLE_NAME = o.OBJECT_NAME AND o.OWNER = p.OWNER
        WHERE p.OWNER IN (
            SELECT USERNAME FROM DBA_USERS WHERE USERNAME NOT IN (
                'SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN', 'OUTLN',
                'CTXSYS', 'MDSYS', 'ORDSYS', 'ORDDATA', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA',
                'WMSYS', 'EXFSYS', 'LBACSYS', 'XDB', 'ANONYMOUS', 'XS$NULL',
                'OLAPSYS', 'MDDATA', 'OWBSYS', 'OWBSYS_AUDIT',
                'APEX_030200', 'APEX_040000', 'APEX_040100', 'APEX_040200', 'APEX_050000',
                'APEX_PUBLIC_USER', 'FLOWS_FILES', 'FLOWS_030000', 'FLOWS_040000',
                'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR',
                'DIP', 'ORACLE_OCM', 'APPQOSSYS', 'MGMT_VIEW', 'SCOTT', 'HR',
                'TSMSYS', 'WKPROXY', 'WK_TEST', 'PERFSTAT', 'TRACESVR',
                'GSMADMIN_INTERNAL', 'GSMCATUSER', 'GSMUSER', 'SYSBACKUP', 'SYSDG', 'SYSKM'
            )
            AND USERNAME NOT LIKE 'APEX\_%' ESCAPE '\'
            AND USERNAME NOT LIKE 'FLOWS\_%' ESCAPE '\'
            AND USERNAME NOT LIKE 'ORA\_%' ESCAPE '\'
            AND USERNAME NOT LIKE 'ORACLE\_%' ESCAPE '\'
            AND USERNAME NOT LIKE 'SYS\_%' ESCAPE '\'
        )
          AND p.TABLE_NAME NOT LIKE 'ISEQ$$%'
          AND p.TABLE_NAME NOT LIKE 'AQ$%'
          AND o.OBJECT_TYPE NOT IN ('QUEUE', 'PACKAGE BODY', 'TYPE BODY')
          AND p.GRANTEE IN (
              SELECT USERNAME FROM DBA_USERS WHERE USERNAME NOT IN (
                  'SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN', 'OUTLN',
                  'CTXSYS', 'MDSYS', 'ORDSYS', 'ORDDATA', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA',
                  'WMSYS', 'EXFSYS', 'LBACSYS', 'XDB', 'ANONYMOUS', 'XS$NULL',
                  'OLAPSYS', 'MDDATA', 'OWBSYS', 'OWBSYS_AUDIT',
                  'APEX_030200', 'APEX_040000', 'APEX_040100', 'APEX_040200', 'APEX_050000',
                  'APEX_PUBLIC_USER', 'FLOWS_FILES', 'FLOWS_030000', 'FLOWS_040000',
                  'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR',
                  'DIP', 'ORACLE_OCM', 'APPQOSSYS', 'MGMT_VIEW', 'SCOTT', 'HR',
                  'TSMSYS', 'WKPROXY', 'WK_TEST', 'PERFSTAT', 'TRACESVR',
                  'GSMADMIN_INTERNAL', 'GSMCATUSER', 'GSMUSER', 'SYSBACKUP', 'SYSDG', 'SYSKM'
              )
              AND USERNAME NOT LIKE 'APEX\_%' ESCAPE '\'
              AND USERNAME NOT LIKE 'FLOWS\_%' ESCAPE '\'
              AND USERNAME NOT LIKE 'ORA\_%' ESCAPE '\'
              AND USERNAME NOT LIKE 'ORACLE\_%' ESCAPE '\'
              AND USERNAME NOT LIKE 'SYS\_%' ESCAPE '\'
              UNION
              SELECT ROLE FROM DBA_ROLES
              UNION
              SELECT 'PUBLIC' FROM DUAL
          )
        GROUP BY p.GRANTEE, p.OWNER, p.TABLE_NAME, p.GRANTABLE
        ORDER BY p.GRANTEE, p.OWNER, p.TABLE_NAME, p.GRANTABLE
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.ddl_script);
    END LOOP;
END;
/

SPOOL OFF
EXIT

Oracle 12c and later

SET HEADING OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET PAGESIZE 0
SET LINESIZE 2000
SET SERVEROUTPUT ON
SPOOL /my/path/to/grant_role.sql

BEGIN
    FOR rec IN (
        WITH NON_ORACLE_USERS_AND_ROLES AS (
            SELECT USERNAME AS NAME FROM DBA_USERS WHERE ORACLE_MAINTAINED = 'N'
            UNION
            SELECT ROLE AS NAME FROM DBA_ROLES WHERE ORACLE_MAINTAINED = 'N'
        ),
        FILTERED_ROLE_PRIVS AS (
            SELECT GRANTEE, GRANTED_ROLE, ADMIN_OPTION
            FROM DBA_ROLE_PRIVS
            WHERE GRANTEE IN (SELECT NAME FROM NON_ORACLE_USERS_AND_ROLES)
        )
        SELECT
            CASE
                WHEN ADMIN_OPTION = 'YES' THEN
                    'GRANT ' || GRANTED_ROLE || ' TO ' || GRANTEE || ' WITH ADMIN OPTION;'
                ELSE
                    'GRANT ' || GRANTED_ROLE || ' TO ' || GRANTEE || ';'
            END AS ddl_script
        FROM FILTERED_ROLE_PRIVS
        ORDER BY GRANTEE, GRANTED_ROLE
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.ddl_script);
    END LOOP;
END;
/

SPOOL OFF
EXIT

Oracle 11g

SET HEADING OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET PAGESIZE 0
SET LINESIZE 2000
SET SERVEROUTPUT ON
SPOOL /my/path/to/grant_role.sql

BEGIN
    FOR rec IN (
        WITH NON_ORACLE_USERS_AND_ROLES AS (
            SELECT USERNAME AS NAME FROM DBA_USERS WHERE USERNAME NOT IN (
                'SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN', 'OUTLN',
                'CTXSYS', 'MDSYS', 'ORDSYS', 'ORDDATA', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA',
                'WMSYS', 'EXFSYS', 'LBACSYS', 'XDB', 'ANONYMOUS', 'XS$NULL',
                'OLAPSYS', 'MDDATA', 'OWBSYS', 'OWBSYS_AUDIT',
                'APEX_030200', 'APEX_040000', 'APEX_040100', 'APEX_040200', 'APEX_050000',
                'APEX_PUBLIC_USER', 'FLOWS_FILES', 'FLOWS_030000', 'FLOWS_040000',
                'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR',
                'DIP', 'ORACLE_OCM', 'APPQOSSYS', 'MGMT_VIEW', 'SCOTT', 'HR',
                'TSMSYS', 'WKPROXY', 'WK_TEST', 'PERFSTAT', 'TRACESVR',
                'GSMADMIN_INTERNAL', 'GSMCATUSER', 'GSMUSER', 'SYSBACKUP', 'SYSDG', 'SYSKM'
            )
            AND USERNAME NOT LIKE 'APEX\_%' ESCAPE '\'
            AND USERNAME NOT LIKE 'FLOWS\_%' ESCAPE '\'
            AND USERNAME NOT LIKE 'ORA\_%' ESCAPE '\'
            AND USERNAME NOT LIKE 'ORACLE\_%' ESCAPE '\'
            AND USERNAME NOT LIKE 'SYS\_%' ESCAPE '\'
            UNION
            SELECT ROLE AS NAME FROM DBA_ROLES WHERE ROLE NOT IN (
                'CONNECT', 'RESOURCE', 'DBA', 'SELECT_CATALOG_ROLE',
                'EXECUTE_CATALOG_ROLE', 'DELETE_CATALOG_ROLE', 'EXP_FULL_DATABASE',
                'IMP_FULL_DATABASE', 'LOGSTDBY_ADMINISTRATOR', 'RECOVERY_CATALOG_OWNER',
                'SCHEDULER_ADMIN', 'HS_ADMIN_SELECT_ROLE', 'HS_ADMIN_EXECUTE_ROLE',
                'HS_ADMIN_ROLE', 'GLOBAL_AQ_USER_ROLE', 'OEM_ADVISOR', 'OEM_MONITOR',
                'MGMT_USER', 'DATAPUMP_EXP_FULL_DATABASE', 'DATAPUMP_IMP_FULL_DATABASE',
                'ADM_PARALLEL_EXECUTE_TASK', 'GATHER_SYSTEM_STATISTICS',
                'OPTIMIZER_PROCESSING_RATE', 'ACCESS_CATALOG_ROLE', 'JAVAUSERPRIV',
                'JAVAIDPRIV', 'JAVASYSPRIV', 'JAVADEBUGPRIV', 'EJBCLIENT', 'JMXSERVER',
                'JAVA_ADMIN', 'JAVA_DEPLOY', 'AUTHENTICATEDUSER', 'XDB_SET_INVOKER',
                'XDB_WEBSERVICES', 'XDB_WEBSERVICES_WITH_PUBLIC', 'XDB_WEBSERVICES_OVER_HTTP',
                'XDBADMIN', 'XDB_WEBSERVICES_OVER_HTTPS', 'CTXAPP', 'ORDADMIN',
                'WM_ADMIN_ROLE', 'OLAP_USER', 'OLAP_DBA', 'OLAP_XS_ADMIN',
                'CSW_USR_ROLE', 'WFS_USR_ROLE', 'SPATIAL_CSW_ADMIN',
                'SPATIAL_WFS_ADMIN', 'LBAC_DBA', 'APEX_ADMINISTRATOR_ROLE',
                'FLOWS_FILES_ROLE', 'AQ_ADMINISTRATOR_ROLE', 'AQ_USER_ROLE',
                'CWM_USER', 'DBFS_ROLE', 'OLAPI_TRACE_USER', 'OWB$CLIENT',
                'OWB_DESIGNCENTER_VIEW', 'OWB_USER'
            )
            AND ROLE NOT LIKE 'APEX\_%' ESCAPE '\'
            AND ROLE NOT LIKE 'FLOWS\_%' ESCAPE '\'
            AND ROLE NOT LIKE 'ORA\_%' ESCAPE '\'
            AND ROLE NOT LIKE 'ORACLE\_%' ESCAPE '\'
            AND ROLE NOT LIKE 'SYS\_%' ESCAPE '\'
        ),
        FILTERED_ROLE_PRIVS AS (
            SELECT GRANTEE, GRANTED_ROLE, ADMIN_OPTION
            FROM DBA_ROLE_PRIVS
            WHERE GRANTEE IN (SELECT NAME FROM NON_ORACLE_USERS_AND_ROLES)
        )
        SELECT
            CASE
                WHEN ADMIN_OPTION = 'YES' THEN
                    'GRANT ' || GRANTED_ROLE || ' TO ' || GRANTEE || ' WITH ADMIN OPTION;'
                ELSE
                    'GRANT ' || GRANTED_ROLE || ' TO ' || GRANTEE || ';'
            END AS ddl_script
        FROM FILTERED_ROLE_PRIVS
        ORDER BY GRANTEE, GRANTED_ROLE
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.ddl_script);
    END LOOP;
END;
/

SPOOL OFF
EXIT

Oracle 12c and later

SET HEADING OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET PAGESIZE 0
SET LINESIZE 2000
SET SERVEROUTPUT ON
SPOOL /my/path/to/grant_sys_priv.sql

BEGIN
    FOR rec IN (
        SELECT
            'GRANT ' || PRIVILEGE || ' TO ' || GRANTEE ||
            CASE WHEN ADMIN_OPTION = 'YES' THEN ' WITH ADMIN OPTION' END || ';' AS ddl_script
        FROM DBA_SYS_PRIVS
        WHERE GRANTEE IN (
            SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED = 'N'
            UNION
            SELECT ROLE FROM DBA_ROLES
            UNION
            SELECT 'PUBLIC' FROM DUAL
        )
        ORDER BY GRANTEE, PRIVILEGE
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.ddl_script);
    END LOOP;
END;
/

SPOOL OFF
EXIT

Oracle 11g

SET HEADING OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET PAGESIZE 0
SET LINESIZE 2000
SET SERVEROUTPUT ON
SPOOL /my/path/to/grant_sys_priv.sql

BEGIN
    FOR rec IN (
        SELECT
            'GRANT ' || PRIVILEGE || ' TO ' || GRANTEE ||
            CASE WHEN ADMIN_OPTION = 'YES' THEN ' WITH ADMIN OPTION' END || ';' AS ddl_script
        FROM DBA_SYS_PRIVS
        WHERE GRANTEE IN (
            SELECT USERNAME FROM DBA_USERS WHERE USERNAME NOT IN (
                'SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN', 'OUTLN',
                'CTXSYS', 'MDSYS', 'ORDSYS', 'ORDDATA', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA',
                'WMSYS', 'EXFSYS', 'LBACSYS', 'XDB', 'ANONYMOUS', 'XS$NULL',
                'OLAPSYS', 'MDDATA', 'OWBSYS', 'OWBSYS_AUDIT',
                'APEX_030200', 'APEX_040000', 'APEX_040100', 'APEX_040200', 'APEX_050000',
                'APEX_PUBLIC_USER', 'FLOWS_FILES', 'FLOWS_030000', 'FLOWS_040000',
                'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR',
                'DIP', 'ORACLE_OCM', 'APPQOSSYS', 'MGMT_VIEW', 'SCOTT', 'HR',
                'TSMSYS', 'WKPROXY', 'WK_TEST', 'PERFSTAT', 'TRACESVR',
                'GSMADMIN_INTERNAL', 'GSMCATUSER', 'GSMUSER', 'SYSBACKUP', 'SYSDG', 'SYSKM'
            )
            AND USERNAME NOT LIKE 'APEX\_%' ESCAPE '\'
            AND USERNAME NOT LIKE 'FLOWS\_%' ESCAPE '\'
            AND USERNAME NOT LIKE 'ORA\_%' ESCAPE '\'
            AND USERNAME NOT LIKE 'ORACLE\_%' ESCAPE '\'
            AND USERNAME NOT LIKE 'SYS\_%' ESCAPE '\'
            UNION
            SELECT ROLE FROM DBA_ROLES
            UNION
            SELECT 'PUBLIC' FROM DUAL
        )
        ORDER BY GRANTEE, PRIVILEGE
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.ddl_script);
    END LOOP;
END;
/

SPOOL OFF
EXIT

Oracle 12c and later

SET HEADING OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET PAGESIZE 0
SET LINESIZE 2000
SET SERVEROUTPUT ON
SPOOL /my/path/to/default_role.sql

BEGIN
    FOR rec IN (
        WITH user_list AS (
            SELECT ROLE AS name FROM DBA_ROLES WHERE ORACLE_MAINTAINED = 'N'
            UNION
            SELECT USERNAME AS name FROM DBA_USERS WHERE ORACLE_MAINTAINED = 'N'
        ),
        role_stats AS (
            SELECT
                GRANTEE,
                COUNT(*) AS total_roles,
                SUM(CASE WHEN DEFAULT_ROLE = 'YES' THEN 1 ELSE 0 END) AS default_roles,
                LISTAGG(CASE WHEN DEFAULT_ROLE = 'YES' THEN GRANTED_ROLE END, ', ')
                    WITHIN GROUP (ORDER BY GRANTED_ROLE) AS default_role_list
            FROM DBA_ROLE_PRIVS
            WHERE GRANTEE IN (SELECT name FROM user_list)
              AND GRANTEE NOT IN ('PDBADMIN')
            GROUP BY GRANTEE
        )
        SELECT
            'ALTER USER ' || GRANTEE || ' DEFAULT ROLE ' ||
            CASE
                WHEN default_roles = 0 THEN 'NONE'
                WHEN default_roles = total_roles THEN 'ALL'
                ELSE default_role_list
            END || ';' AS ddl_script
        FROM role_stats
        ORDER BY GRANTEE
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.ddl_script);
    END LOOP;
END;
/

SPOOL OFF
EXIT

Oracle 11g

SET HEADING OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET PAGESIZE 0
SET LINESIZE 2000
SET SERVEROUTPUT ON
SPOOL /my/path/to/default_role.sql

BEGIN
    FOR rec IN (
        WITH user_list AS (
            SELECT ROLE AS name FROM DBA_ROLES WHERE ROLE NOT IN (
                'CONNECT', 'RESOURCE', 'DBA', 'SELECT_CATALOG_ROLE',
                'EXECUTE_CATALOG_ROLE', 'DELETE_CATALOG_ROLE', 'EXP_FULL_DATABASE',
                'IMP_FULL_DATABASE', 'LOGSTDBY_ADMINISTRATOR', 'RECOVERY_CATALOG_OWNER',
                'SCHEDULER_ADMIN', 'HS_ADMIN_SELECT_ROLE', 'HS_ADMIN_EXECUTE_ROLE',
                'HS_ADMIN_ROLE', 'GLOBAL_AQ_USER_ROLE', 'OEM_ADVISOR', 'OEM_MONITOR',
                'MGMT_USER', 'DATAPUMP_EXP_FULL_DATABASE', 'DATAPUMP_IMP_FULL_DATABASE',
                'ADM_PARALLEL_EXECUTE_TASK', 'GATHER_SYSTEM_STATISTICS',
                'OPTIMIZER_PROCESSING_RATE', 'ACCESS_CATALOG_ROLE', 'JAVAUSERPRIV',
                'JAVAIDPRIV', 'JAVASYSPRIV', 'JAVADEBUGPRIV', 'EJBCLIENT', 'JMXSERVER',
                'JAVA_ADMIN', 'JAVA_DEPLOY', 'AUTHENTICATEDUSER', 'XDB_SET_INVOKER',
                'XDB_WEBSERVICES', 'XDB_WEBSERVICES_WITH_PUBLIC', 'XDB_WEBSERVICES_OVER_HTTP',
                'XDBADMIN', 'XDB_WEBSERVICES_OVER_HTTPS', 'CTXAPP', 'ORDADMIN',
                'WM_ADMIN_ROLE', 'OLAP_USER', 'OLAP_DBA', 'OLAP_XS_ADMIN',
                'CSW_USR_ROLE', 'WFS_USR_ROLE', 'SPATIAL_CSW_ADMIN',
                'SPATIAL_WFS_ADMIN', 'LBAC_DBA', 'APEX_ADMINISTRATOR_ROLE',
                'FLOWS_FILES_ROLE', 'AQ_ADMINISTRATOR_ROLE', 'AQ_USER_ROLE',
                'CWM_USER', 'DBFS_ROLE', 'OLAPI_TRACE_USER', 'OWB$CLIENT',
                'OWB_DESIGNCENTER_VIEW', 'OWB_USER'
            )
            AND ROLE NOT LIKE 'APEX\_%' ESCAPE '\'
            AND ROLE NOT LIKE 'FLOWS\_%' ESCAPE '\'
            AND ROLE NOT LIKE 'ORA\_%' ESCAPE '\'
            AND ROLE NOT LIKE 'ORACLE\_%' ESCAPE '\'
            AND ROLE NOT LIKE 'SYS\_%' ESCAPE '\'
            UNION
            SELECT USERNAME AS name FROM DBA_USERS WHERE USERNAME NOT IN (
                'SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN', 'OUTLN',
                'CTXSYS', 'MDSYS', 'ORDSYS', 'ORDDATA', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA',
                'WMSYS', 'EXFSYS', 'LBACSYS', 'XDB', 'ANONYMOUS', 'XS$NULL',
                'OLAPSYS', 'MDDATA', 'OWBSYS', 'OWBSYS_AUDIT',
                'APEX_030200', 'APEX_040000', 'APEX_040100', 'APEX_040200', 'APEX_050000',
                'APEX_PUBLIC_USER', 'FLOWS_FILES', 'FLOWS_030000', 'FLOWS_040000',
                'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR',
                'DIP', 'ORACLE_OCM', 'APPQOSSYS', 'MGMT_VIEW', 'SCOTT', 'HR',
                'TSMSYS', 'WKPROXY', 'WK_TEST', 'PERFSTAT', 'TRACESVR',
                'GSMADMIN_INTERNAL', 'GSMCATUSER', 'GSMUSER', 'SYSBACKUP', 'SYSDG', 'SYSKM'
            )
            AND USERNAME NOT LIKE 'APEX\_%' ESCAPE '\'
            AND USERNAME NOT LIKE 'FLOWS\_%' ESCAPE '\'
            AND USERNAME NOT LIKE 'ORA\_%' ESCAPE '\'
            AND USERNAME NOT LIKE 'ORACLE\_%' ESCAPE '\'
            AND USERNAME NOT LIKE 'SYS\_%' ESCAPE '\'
        ),
        role_stats AS (
            SELECT
                GRANTEE,
                COUNT(*) AS total_roles,
                SUM(CASE WHEN DEFAULT_ROLE = 'YES' THEN 1 ELSE 0 END) AS default_roles,
                LISTAGG(CASE WHEN DEFAULT_ROLE = 'YES' THEN GRANTED_ROLE END, ', ')
                    WITHIN GROUP (ORDER BY GRANTED_ROLE) AS default_role_list
            FROM DBA_ROLE_PRIVS
            WHERE GRANTEE IN (SELECT name FROM user_list)
              AND GRANTEE NOT IN ('PDBADMIN')
            GROUP BY GRANTEE
        )
        SELECT
            'ALTER USER ' || GRANTEE || ' DEFAULT ROLE ' ||
            CASE
                WHEN default_roles = 0 THEN 'NONE'
                WHEN default_roles = total_roles THEN 'ALL'
                ELSE default_role_list
            END || ';' AS ddl_script
        FROM role_stats
        ORDER BY GRANTEE
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.ddl_script);
    END LOOP;
END;
/

SPOOL OFF
EXIT

Oracle 12c and later

-- ==================================================================
-- Step 1: Export password verify functions referenced by PROFILEs
-- Description: Query DBA_SOURCE to get the full definition of custom verify functions referenced by PROFILEs.
-- Excludes Oracle built-in functions (ORA12C_VERIFY_FUNCTION, ORA12C_STIG_VERIFY_FUNCTION).
-- ==================================================================
SET SERVEROUTPUT ON SIZE UNLIMITED
SET LINESIZE 32767
SET LONG 2000000000
SET LONGCHUNKSIZE 2000000000
SET PAGESIZE 0
SET FEEDBACK OFF
SET HEADING OFF
SPOOL profile_verify_functions.sql

SELECT 'CREATE OR REPLACE ' || LISTAGG(TEXT, CHR(10)) WITHIN GROUP (ORDER BY LINE) AS ddl_script
FROM DBA_SOURCE
WHERE NAME IN (
    SELECT DISTINCT LIMIT
    FROM DBA_PROFILES
    WHERE RESOURCE_NAME = 'PASSWORD_VERIFY_FUNCTION'
      AND LIMIT IS NOT NULL
      AND LIMIT <> 'NULL'
      AND LIMIT NOT IN ('ORA12C_VERIFY_FUNCTION', 'ORA12C_STIG_VERIFY_FUNCTION')
)
AND TYPE = 'FUNCTION'
AND OWNER = 'SYS'
GROUP BY NAME, TYPE, OWNER;

SPOOL OFF

-- ==================================================================
-- Step 2: Create non-DEFAULT PROFILEs
-- Description: Read password-related parameters from DBA_PROFILES and generate CREATE PROFILE statements.
-- Excludes system PROFILEs (DEFAULT, ORA_CIS_PROFILE, ORA_STIG_PROFILE, MONITORING_PROFILE).
-- Migrates only password policy parameters; resource limits are not migrated.
-- ==================================================================
SPOOL create_profiles.sql

SELECT DISTINCT 'CREATE PROFILE ' || PROFILE || ' LIMIT '
         || LISTAGG(RESOURCE_NAME || ' ' || LIMIT, ' ') WITHIN GROUP (ORDER BY RESOURCE_NAME)
         || ';' AS ddl_script
  FROM DBA_PROFILES WHERE RESOURCE_NAME IN (
'PASSWORD_LIFE_TIME',
'PASSWORD_REUSE_TIME',
'PASSWORD_REUSE_MAX',
'PASSWORD_VERIFY_FUNCTION',
'PASSWORD_GRACE_TIME',
'PASSWORD_LOCK_TIME',
'FAILED_LOGIN_ATTEMPTS'
) AND PROFILE NOT IN ('DEFAULT', 'ORA_CIS_PROFILE', 'ORA_STIG_PROFILE', 'MONITORING_PROFILE')
GROUP BY PROFILE;

SPOOL OFF

-- ==================================================================
-- Step 3: Update the DEFAULT PROFILE
-- Description: The target already has a DEFAULT profile, so use ALTER PROFILE instead of CREATE.
-- ==================================================================
SPOOL alter_default_profile.sql

SELECT DISTINCT 'ALTER PROFILE DEFAULT LIMIT '
         || LISTAGG(RESOURCE_NAME || ' ' || LIMIT, ' ') WITHIN GROUP (ORDER BY RESOURCE_NAME)
         || ';' AS ddl_script
  FROM DBA_PROFILES WHERE RESOURCE_NAME IN (
'PASSWORD_LIFE_TIME',
'PASSWORD_REUSE_TIME',
'PASSWORD_REUSE_MAX',
'PASSWORD_VERIFY_FUNCTION',
'PASSWORD_GRACE_TIME',
'PASSWORD_LOCK_TIME',
'FAILED_LOGIN_ATTEMPTS'
) AND PROFILE = 'DEFAULT'
GROUP BY PROFILE;

SPOOL OFF

-- ==================================================================
-- Step 4: Bind users to PROFILEs
-- Description: Migrate the user-profile binding for non-system source users to the target.
-- Filter: ORACLE_MAINTAINED = 'N' excludes system users.
-- ==================================================================
SPOOL alter_user_profile.sql

SELECT 'ALTER USER ' || USERNAME || ' PROFILE ' || PROFILE || ';' AS ddl_script
FROM DBA_USERS
WHERE PROFILE NOT IN ('ORA_CIS_PROFILE', 'ORA_STIG_PROFILE', 'MONITORING_PROFILE')
  AND ORACLE_MAINTAINED = 'N'
ORDER BY USERNAME;

SPOOL OFF

Oracle 11g

Oracle 11g does not have the ORACLE_MAINTAINED column. Therefore, Step 4 uses a predefined list to filter system users. Steps 1, 2, and 3 are identical to the Oracle 12c and later version; only Step 4 differs.

-- ==================================================================
-- Steps 1-3: Identical to the Oracle 12c and later version. Not repeated here.
-- ==================================================================

-- ==================================================================
-- Step 4: Bind users to PROFILEs (Oracle 11g)
-- Description: Uses an explicit system user list instead of the ORACLE_MAINTAINED column.
-- ==================================================================
SPOOL alter_user_profile.sql

SELECT 'ALTER USER ' || USERNAME || ' PROFILE ' || PROFILE || ';' AS ddl_script
FROM DBA_USERS
WHERE PROFILE NOT IN ('ORA_CIS_PROFILE', 'ORA_STIG_PROFILE', 'MONITORING_PROFILE')
  AND USERNAME NOT IN (
    'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN',
    'CTXSYS','MDSYS','ORDSYS','ORDDATA','ORDPLUGINS','SI_INFORMTN_SCHEMA',
    'WMSYS','EXFSYS','LBACSYS','XDB','ANONYMOUS','XS$NULL',
    'OLAPSYS','MDDATA',
    'OWBSYS','OWBSYS_AUDIT',
    'APEX_030200','APEX_040000','APEX_040100','APEX_040200','APEX_050000',
    'APEX_PUBLIC_USER','FLOWS_FILES','FLOWS_030000','FLOWS_040000',
    'SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR',
    'DIP','ORACLE_OCM','APPQOSSYS','MGMT_VIEW','SCOTT','HR','TSMSYS',
    'WKPROXY','WK_TEST','PERFSTAT','TRACESVR',
    'GSMADMIN_INTERNAL','GSMCATUSER','GSMUSER','SYSBACKUP','SYSDG','SYSKM'
  )
  AND USERNAME NOT LIKE 'APEX\_%' ESCAPE '\'
  AND USERNAME NOT LIKE 'FLOWS\_%' ESCAPE '\'
  AND USERNAME NOT LIKE 'ORA\_%' ESCAPE '\'
  AND USERNAME NOT LIKE 'ORACLE\_%' ESCAPE '\'
  AND USERNAME NOT LIKE 'SYS\_%' ESCAPE '\'
ORDER BY USERNAME;

SPOOL OFF