oracle 闪回关于触发器的bug

SQL> create table test1 (cola varchar2(10) );

表已创建。

 

SQL> create table test2 (colb varchar2(10));

表已创建。

 

SQL> select * from test1;

未选定行

 

SQL> select * from test2;

未选定行

 

SQL> create or replace trigger trigger1

  2    before insert on test1

  3    for each row

  4  declare

  5  begin

  6    insert into test2(colb) values('trigger1');

  7  end trigger1;

  8  /

触发器已创建

 

SQL> select object_name,object_id from user_objects where object_name in ('TRIGGER1','TEST1');

 

OBJECT_NAME                                                                     

--------------------------------------------------------------------------------

 OBJECT_ID                                                                     

----------                                                                      

TEST1                                                                          

    101621                                                                     

                                                                                

TRIGGER1                                                                       

    101623                                                                     

                                                                               

 

SQL> col object_name for a20

SQL> /

OBJECT_NAME           OBJECT_ID                                                

-------------------- ----------                                                

TEST1                    101621                                                 

TRIGGER1                 101623                                                 

 

SQL> insert into test1(cola) values('no1');

已创建 1 行。

SQL> commit;

提交完成。

 

SQL> select * from test1;

COLA                                                                            

--------------------                                                           

no1                                                                            

SQL> select * from test2;

COLB                                                                            

--------------------                                                           

trigger1                                                                       

--到这里，表现一切正常。下面开始删除恢复。

 

SQL> drop table test1;

表已删除。

 

SQL> select object_name,object_id from user_objects

  2  where object_name in ('TRIGGER1','TEST1');

未选定行

--如果没加purge选项，则两者都被删除到回收站，虽然user_object视图看不到，在底层表obj$中能看到。

 

 

SQL> flashback table TEST1 to before drop;

闪回完成。

 

SQL> select object_name,object_id from user_objects

  2  where object_name in ('TRIGGER1','TEST1');

OBJECT_NAME           OBJECT_ID                                                

-------------------- ----------                                                 

TEST1                    101621                                                

--闪回成功后，在视图中，正常对象只能看到表TEST1，不能看到触发器。

 

SQL> grant select on obj$ to ncoadev;

授权成功。

--切换到sys用户，授权访问指定对象。

 

SQL> select name,obj# from sys.obj$ where obj# in (101621,101623);

NAME                                                               OBJ#        

------------------------------------------------------------ ----------        

TEST1                                                            101621        

BIN$mlTKEo1FAGjgQ38AAAEAaA==$0                                   101623        

 

--obj$表中，能看到 ID为101623的触发器还在，但名字被oracle打了内部标记。此时，用pl/sql dev等工具是看不到那个触发器的。

 

SQL> select * from test2;

COLB                                                                           

--------------------                                                            

trigger1   

                                                                   

SQL> insert into test1(cola) values('no2');

已创建 1 行。

SQL> commit;

提交完成。

 

SQL> select * from test2;

COLB                                                                            

--------------------                                                           

trigger1                                                                       

trigger1    

--又插入了一条记录，证明触发器还是生效的，但很难管理了。更麻烦的是后面：

 

SQL> create or replace trigger trigger1

  2    before insert on test1

  3    for each row

  4  declare

  5  begin

  6    insert into test2(colb) values('trigger2');

  7  end trigger1;

  8  /

触发器已创建

--如果开发人员看不到触发器了，认为触发器丢失，又建立了一个新的同名触发器trigger1，仍然能建立。

 

SQL> select * from test2;

COLB                                                                            

--------------------                                                           

trigger1                                                                       

trigger1                                                                        

SQL> insert into test1(cola) values('no3');

已创建 1 行。

SQL> commit;

提交完成。

 

SQL> select * from test2;

 

COLB                                                                           

--------------------                                                           

trigger1                                                                       

trigger1                                                                       

trigger2                                                                        

trigger1    

 

--此时，两个触发器都生效了，都作用在这个表上，会给系统带来什么影响呢？？算不算oracle的bug呢？