系统触发器 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将 可以访问任何表在其创造了约一分钟后....