Disappearing triggers

How to make triggers disappear without actually dropping them? I recently ran into this situation and reported it to Oracle as a possible bug. This is still being investigated by Oracle.

I am going to explain this using the testcase I built for Oracle Support.

Let's start by creating a simple table.

Connected to:
Oracle Database 18c EE Extreme Perf Release 18.0.0.0.0 - Production
Version 18.5.0.0.0

CREATE TABLE t1_
(id   NUMBER(6)         NOT NULL
,col1 DATE              NOT NULL
,col2 VARCHAR2(10 CHAR)
,ts   TIMESTAMP(9)      NOT NULL
);

Table created.

ALTER TABLE t1_ ADD CONSTRAINT t1_pk PRIMARY KEY (ID)
;

Table altered.

Because I want to be able to do near zero downtime patching I use Oracle Edition Based Redefinition (EBR). So this means I also need an Editioning view on the table.

CREATE OR REPLACE EDITIONING VIEW t1 
AS
SELECT id
,      col1
,      col2
,      ts 
FROM   t1_
;

View created.

Now I am going to create a simple trigger on the Editioning view. Not on the table of course because that would make our trigger non-editionable and that is not what I want.

CREATE OR REPLACE TRIGGER t1_bri
BEFORE INSERT ON t1 FOR EACH ROW
BEGIN
  :new.ts := SYSTIMESTAMP;
END;
/

Trigger created.

Setup is ready and let us view the information on this trigger in the data dictionary.

SELECT trigger_name, status, table_name
FROM   dba_triggers t
WHERE  t.trigger_name = 'T1_BRI'
AND    t.owner        = USER
;

TRIGGER_NA STATUS     TABLE_NAME
---------- ---------- ------------------------------
T1_BRI     ENABLED    T1

1 row selected.

So far so good, the trigger is present, visible and enabled. Now I am going to create a new version and change something.

CREATE edition v1
;

Edition created.

ALTER SESSION SET edition = v1
;

Session altered.

ALTER VIEW t1 COMPILE
;

View altered.

I have changed the editioning view or more precisely I have compiled the Editioning view. This action will create a new version of the Editioning view in the V1 edition. Now let us query the data dictionary again.

SELECT trigger_name, status, table_name
FROM   dba_triggers t
WHERE  t.trigger_name = 'T1_BRI'
AND    t.owner        = USER
;

no rows selected

SELECT trigger_name, status, table_name 
FROM   user_triggers t
WHERE  t.trigger_name = 'T1_BRI'
;

no rows selected

No rows selected?!? So the trigger has disappeared? Let us look a bit further.

SELECT object_name, status
FROM   dba_objects 
WHERE  object_type = 'TRIGGER'
AND    object_name = 'T1_BRI'
;

OBJECT_NAME                    STATUS
------------------------------ ----------
T1_BRI                         VALID

1 row selected.

So the trigger has not disappeared but is no longer visible in DBA_TRIGGERS and USER_TRIGGERS. The reason for this bug/feature is the fact that the *_TRIGGERS data dictionary views display information for the current edition, V1 in this case. I can show this by repeating the last query but instead of using the DBA_OBJECTS view I will now use the DBA_OBJECTS_AE view that will list all version of objects.

SELECT object_name, status, edition_name
FROM   dba_objects_ae 
WHERE  object_type = 'TRIGGER'
AND    object_name = 'T1_BRI'
;

OBJECT_NAME STATUS     EDITION_NAME
----------- ---------- ------------
T1_BRI      VALID      ORA$BASE

1 row selected.

This shows that the trigger is present in the ORA$BASE edition which is the initial edition that is always present in a new database.

To make the trigger reappear in the DBA_TRIGGERS and USER_TRIGGERS view we could recompile the trigger but this is not necessary. We can also validate the trigger by means of DBMS_UTLITY.VALIDATE. According to the documentation: "This procedure validates a database object using the same mechanism that is used for automatic re-validation.". So it effectively corrects the information caused by the bug/feature.

SQL> EXEC dbms_utility.validate(owner => 'MHF', objname => 'T1_BRI', namespace => 3)

PL/SQL procedure successfully completed.

SELECT trigger_name, status, table_name
FROM   dba_triggers t
WHERE  t.trigger_name = 'T1_BRI'
AND    t.owner        = USER
;

TRIGGER_NAME STATUS     TABLE_NAME
------------ ---------- ------------
T1_BRI       ENABLED    T1

1 row selected.

SELECT trigger_name, status, table_name 
FROM   user_triggers t
WHERE  t.trigger_name = 'T1_BRI'
;

TRIGGER_NAME STATUS     TABLE_NAME
------------ ---------- ------------
T1_BRI       ENABLED    T1

1 row selected.

The "namespace" parameter of the DBMS_UTLITY.VALIDATE can be found in the *_OBJECTS view of the object you want to validate.

We can conclude that working with editions is not as straightforward as may seem from the documentation or tutorials. When creating editions and compiling objects with dependent objects, the trigger in this case, it is necessary to explicitly actualize all objects before starting to use the edition. This can be done by recompilation of all objects or by DBMS_UTLITY.VALIDATE for all objects that are either invalid or inherited by the current edition.

UPDATE July 2019:

Oracle Support confirmed it is indeed a bug that is under development (Bug 17777718 : DBA_OBJECTS RETURNS INCORRECT STATUS FOR EDITIONED OBJECTS