Creating and Granting Privileges to the Oracle GoldenGate User for Oracle 11g

You can create the Oracle GoldenGate user by creating a script similar to the following:

set echo on
spool createogguser.log
-- Create the OGG User
GRANT CONNECT, RESOURCE to OGGUSER identified by OGGUSER;
--Grant OGG required privileges
GRANT CREATE SESSION to OGGUSER;
GRANT ALTER SESSION to OGGUSER; 
GRANT SELECT ANY DICTIONARY to OGGUSER;
GRANT FLASHBACK ANY TABLE to OGGUSER;
GRANT ALTER ANY TABLE to OGGUSER;
GRANT SELECT ANY TABLE to OGGUSER;
GRANT INSERT ANY TABLE to OGGUSER;
GRANT DELETE ANY TABLE to OGGUSER;
GRANT UPDATE ANY TABLE to OGGUSER;
GRANT CREATE TABLE to OGGUSER;
GRANT UNLIMITED TABLESPACE to OGGUSER;
GRANT EXECUTE on DBMS_FLASHBACK to OGGUSER;
GRANT SELECT ON dba_clusters to OGGUSER;
spool off;

Example to List the Privileges Granted to the GoldenGate User in Oracle 11g

To list the privileges granted to the Oracle GoldenGate User, you can run the following script:

set echo on
set heading off
spool showogguserprivileges.log
-- Show all privileges associated with the OGG User
select
  lpad(' ', 2*level) || granted_role "USER, his roles and privileges"
from
  (
  /* THE USERS */
    select 
      null     grantee, 
      username granted_role
    from 
      dba_users
    where
      username like upper('OGGUSER')
  /* THE ROLES TO ROLES RELATIONS */ 
  union
    select 
      grantee,
      granted_role
    from
      dba_role_privs
  /* THE ROLES TO PRIVILEGE RELATIONS */ 
  union
    select
      grantee,
      privilege
    from
      dba_sys_privs
  )
start with grantee is null
connect by grantee = prior granted_role;
spool off; 

The above script generates following result:

  OGGUSER
    ALTER ANY TABLE 
    ALTER SESSION
    CONNECT 
      CREATE SESSION
    CREATE TABLE  
    DELETE ANY TABLE 
    FLASHBACK ANY TABLE 
    INSERT ANY TABLE  
    RESOURCE  
      CREATE CLUSTER  
      CREATE INDEXTYPE
      CREATE OPERATOR
      CREATE PROCEDURE 
      CREATE SEQUENCE
      CREATE TABLE 
      CREATE TRIGGER 
      CREATE TYPE
    SELECT ANY DICTIONARY 
    SELECT ANY TABLE 
     UNLIMITED TABLESPACE 
    UPDATE ANY TABLE 

22 rows selected.