Oracle登录触发器ReCompile

Question

通常，当oracle对象由于被修改的依赖对象而失效时，无效对象的下一次执行将编译它，并且一切都将按预期执行(假设修改不会导致错误)。这与标准表触发器的预期效果相同：

-- 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

所有这些都是预料中的..。现在让我们看一下登录触发器：

-- 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

它看起来像数据库试图像预期的那样编译无效的触发器，但是它会抛出一个特权警告。MYDBA模式具有DBA特权，因此它应该拥有所有必需的特权。

Answer 1

把这些都打出来后..。我顿悟了。我看到它是在稍微不同的上下文中提到的，直到现在为止，put并没有把它放在一起。您需要执行以下授予：

grant administer database trigger to mydba;

这是通过DBA角色提供的，但我认为需要直接拨款来动态编译触发器。