oracle ora30511,系统触发器 ora-30511

CREATE OR REPLACE TRIGGER allen

AFTER LOGON ON database

BEGIN

execute immediate ' alter user raddisonlee account unlock ';

commit;

END;

-------- ok ,it  pass in the sys account ! no any error message ....  but  when i change another account to logon  oracle ,it is failture ! error code under :

ERROR:

ORA-00604: 递归 SQL 层 1 出现错误

ORA-30511: 系统触发器中无效的 DDL 操作

ORA-06512: 在line 2

Since you have 8.1.6, you can use an ON CREATE trigger to do this perhaps.

Something like:

create or replace trigger do_grant

after CREATE on schema

declare

l_str varchar2(255);

l_job number;

begin

if ( ora_dict_obj_type = 'TABLE' )

then

l_str := 'execute immediate "grant select on ' ||

ora_dict_obj_name ||

' to scott";';

dbms_job.submit( l_job, replace(l_str,'"','''') );

end if;

end;

/

That, when run in a given user account will schedule a job to grant SELECT on

any table created to SCOTT.  We use dbms_job to run the grant in the background

because this is executing as part of a larger data dictionary transaction and we

cannot commit (nor can we use an autonomous transaction - the table is not yet

"fully" created)...

If you have job_queue_processes set to >0 and job_queue_interval set to some

reasonable number (I use 60 for 1 minute intervals), then scott will have access

to any table about 1 minute AFTER its created....

也就是说，在一个给定的用户帐户下运行时，将安排一个作业，授予SELECT权限对此用户上

创建的任何表给scott。我们使用DBMS_JOB在后台运行grant 是因为这是在执行一个较大的数据字典事务的一部分，我们

不能commit(也可以使用自治事务-该表是尚未 “完全”创建的)...

如果您已经JOB_QUEUE_PROCESSES设置为>0且JOB_QUEUE_INTERVAL设置一些

合理数量的(我用60，1分钟间隔)，那么scott将 可以访问任何表在其创造了约一分钟后....