I put together a handy script that reproduces the Data Definition Language (DDL) for an existing GoldenGate user. This friendly tool makes it easy to duplicate the necessary database privileges, ensuring everything stays consistent. It not only streamlines the setup for new replication users but also helps to minimize any errors along the way!

-- Define the user once here
DEFINE target_user = 'GGADMIN' -- Replace with your actual user in UPPERCASE

SELECT ddl_line FROM (
    -- Construct CREATE USER
    SELECT 
        'CREATE USER ' || username || 
        ' IDENTIFIED BY <your_password> DEFAULT TABLESPACE ' || default_tablespace || 
        ' TEMPORARY TABLESPACE ' || temporary_tablespace || 
        ' QUOTA UNLIMITED ON ' || default_tablespace || ';' AS ddl_line
    FROM dba_users 
    WHERE username = '&target_user'

    UNION ALL

    -- Granted Roles
    SELECT 
        'GRANT ' || granted_role || ' TO ' || grantee || ';' 
    FROM dba_role_privs
    WHERE grantee = '&target_user'

    UNION ALL

    -- System Privileges
    SELECT 
        'GRANT ' || privilege || ' TO ' || grantee || ';' 
    FROM dba_sys_privs
    WHERE grantee = '&target_user'

    UNION ALL

    -- Object Privileges
    SELECT 
        'GRANT ' || privilege || ' ON ' || owner || '.' || table_name || ' TO ' || grantee || ';' 
    FROM dba_tab_privs
    WHERE grantee = '&target_user'
)
ORDER BY 1;