Oracle Logon Trigger – How to Recompile in Oracle 11g and 12c

Typically when an oracle object goes invalid due to a dependent object being modified, the next execution of the invalid object will compile it and everything will be execute as expected (assuming no errors caused by the modification). This works as expected for standard table triggers:

-- connect to DB
connect myschema@TESTDB

-- Create table
create table myschema.t as select * from all_objects;

-- Create before insert
create or replace trigger myschema.trg before insert on t
for each row
begin
:new.object_id :=12;
end;
/

-- Grant insert privilege to table
grant insert on myschema.t to otherschema;

-- Modify underlying table to make trigger go invalid
alter table myschema.t drop column owner;

-- Connect as otherschema
connect otherschema@TESTDB

-- View status of trigger (invalid)
select object_name, status from dba_objects where owner = 'MYSCHEMA';
OBJECT_NAME       STATUS
-----------       -------
TRG               INVALID

-- insert record into myschema.t
insert into myschema.t 
select OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,
DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,
TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,
NAMESPACE,EDITION_NAME 
from all_objects 
where rownum=1;

-- View status of trigger (valid)
select object_name, status from dba_objects where owner = 'MYSCHEMA';
OBJECT_NAME       STATUS
-----------       -------
TRG               VALID

All of that is as expected… now lets look at a logon trigger:

-- Connect as privileged schema
connect mydba@TESTDB

-- Create table in privileged schema
create table mydba.test_table ( c1 varchar2(100), c2 date );

-- Create logon trigger
create or replace trigger mydba.test_logon_trg
after logon on database
declare
v_variable varchar2(100);
begin
select c1 into v_variable 
from mydba.test_table;     
exception when no_data_found
then
  null;
end;
/

-- Modify table to make trigger go invalid
alter table mydba.test_table modify ( c1 varchar2(200) );

-- Check status of trigger ( invalid )
select owner, object_name, status 
from dba_objects 
where owner = 'MYDBA' and object_name = 'TEST_LOGON_TRG';

-- Connect as non-privileged user (if you use a privileged, i.e. DBA,  account it bypasses the trigger)
connect myschema@TESTDB

ORA-04045: errors during recompilation/revalidation of 
MYDBA.TEST_LOGON_TRG
ORA-01031: insufficient privileges

It looks like the database tries to compile the invalid trigger as expected, but it throws a privilege warning. The MYDBA schema has the DBA privilege and so it should have all the privileges necessary.