PL/SQL developer delete rows using select for update

今天测试了一下PL/SQL DEVELOPER工具进行SELECT FOR UPDATE删除数据的时候数据库是如何处理的。测试的场景如下图红色圈圈部分。

jietu

采用的方法是logminer挖掘日志,挖掘的脚本如下

alter system set utl_file_dir = '/arch' scope=spfile;
shutdown immediate
startup

BEGIN
  DBMS_LOGMNR_D.build (
    dictionary_filename => 'dict.ora',
    dictionary_location => '/arch');
END;
/

BEGIN
  DBMS_LOGMNR.add_logfile (
    options     => DBMS_LOGMNR.new,
    logfilename => '/oradata2/arch/1_345_927379454.dbf');
  DBMS_LOGMNR.add_logfile (
    options     => DBMS_LOGMNR.addfile,
    logfilename => '/oradata2/arch/1_346_927379454.dbf'); 
END;
/

BEGIN
  DBMS_LOGMNR.start_logmnr(
    dictfilename => '/arch/dict.ora');
end;
/

create table blue.t as select * From v$logmnr_contents;

BEGIN
  DBMS_LOGMNR.end_logmnr;
END;
/

对于select for update直接通过”-“删除记录的方式的测试结果通过logminer抓取的结果如下

SQL> select sql_redo,sql_undo from t where table_name = 'T_DUP' order by timestamp;

SQL_REDO                                                                         SQL_UNDO
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
create table t_dup(id number);
insert into "BLUE"."T_DUP"("ID") values ('1');                                   delete from "BLUE"."T_DUP" where "ID" = '1' and ROWID = 'AAAvC3AAEAAAJ7sAAA';
insert into "BLUE"."T_DUP"("ID") values ('1');                                   delete from "BLUE"."T_DUP" where "ID" = '1' and ROWID = 'AAAvC3AAEAAAJ7sAAB';
select * from "BLUE"."T_DUP" where ROWID = 'AAAvC3AAEAAAJ7sAAA' for update;
delete from "BLUE"."T_DUP" where "ID" = '1' and ROWID = 'AAAvC3AAEAAAJ7sAAA';    insert into "BLUE"."T_DUP"("ID") values ('1');

又测试了delete from table_name where rowid = ”,在数据层记录是和上面的输出一样的。
抓取tns的报文信息发现在执行”-“删除数据的时候,报文信息如下

where rowid = :plsqldev_rowid for update nowait
DELETE FROM WHERE ROWID = :PLSQLDEV_ROWID
图像 | 此条目发表在logminer分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s