Insufficient Privileges To Execut Dbms_ddl.set_trigger_firing_property (Doc ID 1923158.1)

Last updated on JULY 17, 2017

Applies to:

Oracle GoldenGate - Version 12.1.2.0.0 and later
Information in this document applies to any platform.

Symptoms

I am attempting to use dbms_ddl.set_trigger_firing_property in REPLICAT to delay triggers.

In my Replicat process prm file i have:

UserId sv_ggr_db@psnocdb, Password <password>
SQLEXEC "begin dbms_ddl.set_trigger_firing_property('snoc', 'schedulable_resource_UP', FALSE); end; "

I have granted the follwoing to the user:
GRANT EXECUTE on DBMS_DDL to sv_ggr_db

When I start the replicat process I get the errors:

ERROR OGG-00891 Unsuccessful execution: Database error 1031 (ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_DDL", line 361
ORA-06512: at line 1 SQL begin dbms_ddl.set_trigger_firing_property('snoc', 'schedulable_resource_UP', FALSE); end; ).

On the web I found a site that said to enclose the trigger name in double quotes:
SQLEXEC "begin dbms_ddl.set_trigger_firing_property('snoc', '"schedulable_resource_UP"', FALSE); end; "

But that resulted in the error:
ERROR OGG-00206 Invalid SQLEXEC specification.

In the installation guide it states to run:
exec dbms_goldengate_auth.grant_admin_privilege('sv_ggr_db'')
I did but still get the insufficient privileges error

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms