Before Ddl Trigger (Create,Drop,Alter) Became Invalid Now Unable To Do Any Ddl On Database (Doc ID 795980.1)

Last updated on MARCH 01, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.3 and later
Information in this document applies to any platform.
This problem can occur on any platform.

Symptoms

Created a "before DDL database level" trigger which became invalid, now unable to do any Ddl on database, including the Sys user to resolve the problem.

For example.

create or replace trigger trg_before_ddl
before
create or alter or drop
on database ...

Now as Sys user, trying to disable trigger so can resolve issue, the alter fails with the following errors.

SQL> alter trigger some_user.trg_before_ddl disable;

ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of SOME_USER.TRG_BEFORE_DDL
ORA-04098: trigger 'SOME_USER.TRG_BEFORE_DDL' is invalid and failed re-validation

Changes

Testing new database level triggers.

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