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

Last updated on FEBRUARY 06, 2020

Applies to:

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 <username>@<name>, Password <password>
SQLEXEC "begin dbms_ddl.set_trigger_firing_property('snoc', 'schedulable_resource_UP', FALSE); end; "

I have granted the following to the user:
GRANT EXECUTE on DBMS_DDL to <username>

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('<username>')
I did but still get the insufficient privileges error

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.