ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 触发器的闪存恢复

触发器的闪存恢复

原创 Linux操作系统 作者:jdsnhan 时间:2011-01-20 22:44:14 0 删除 编辑
闲着没事,做了一个测试。
 
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    
--此时,两个触发器都生效了,都作用在这个表上,会给系统带来什么影响呢??    这算不算一个bug呢?                    
                                                                

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13658252/viewspace-684395/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2011-01-20

  • 博文量
    1
  • 访问量
    2168