oracle flashback query for flashback data archive return duplicate row

Unknown

we have a system using Oracle flashback data archive for data change tracking，after by frequently insert/delete/update, we found some rows in SYS_FBA_HIST_NNNN are reuese xid, that's result the start_scn are also reused, but end_scn is right. so when we execute flashback query using as of scn/timestamp, there will be returned two rows with same rowid, that's the wrong result. both oracle 11.2.0.4 and oracle 18c have the same behavior. can someone help to check whether it is a bug?? thx.

how to repeat. the follows:

create flashback archive default fda_test_1day tablespace "USERS" retention 1 day;

create table xyz_test (

    c_tenantid                     varchar2(20)    DEFAULT ' '        NOT NULL,

    c_tacode                       varchar2(2)     DEFAULT ' '        ,

    c_fundacco                     varchar2(12)    DEFAULT ' '        ,

    c_agencyno                     varchar2(9)     DEFAULT ' '        ,

    c_netno                        varchar2(9)     DEFAULT ' '        ,

    c_tradeacco                    varchar2(24)    DEFAULT ' '        ,

    c_fundcode                     varchar2(12)    DEFAULT ' '        ,

    c_sharetype                    char(1)         DEFAULT ' '        ,

    d_cdate                        number(8,0)     DEFAULT 0          ,

    c_cserialno                    varchar2(20)    DEFAULT ' '        NOT NULL,

    c_sourcetype                   char(1)         DEFAULT ' '        ,

    f_remainshares                 number(16,2)    DEFAULT 0.0        ,

    d_registdate                   number(8,0)     DEFAULT 0          ,

    f_oricfmbalance                number(16,2)    DEFAULT 0.0        ,

    f_oricfmshares                 number(16,2)    DEFAULT 0.0        ,

    f_orinetvalue                  number(7,4)     DEFAULT 0.0        ,

    c_orisource                    char(1)         DEFAULT ' '        ,

    f_ruleagio                     number(5,4)     DEFAULT 1.0        ,

    f_oriagio                      number(5,4)     DEFAULT 1.0        ,

    f_maxallotratio                number(5,4)     DEFAULT 0.0        ,

    f_minredeemratio               number(5,4)     DEFAULT 0.0        ,

    c_holdflag                     char(1)         DEFAULT ' '        ,

    c_acceptmode                   char(1)         DEFAULT ' '        ,

    c_firstcserailno               varchar2(20)    DEFAULT ' '        ,

    c_fundmethod                   varchar2(2)     DEFAULT ' '        ,

    c_bankno                       varchar2(60)    DEFAULT ' '        ,

    c_subfundmethod                varchar2(2)     DEFAULT ' '        ,

    f_managefare                   number(16,2)    DEFAULT 0.0        ,

    f_interestshare                number(16,2)    DEFAULT 0.0        ,

    d_lastdeductdate               number(8,0)     DEFAULT 0          ,

    f_income                       number(16,2)    DEFAULT 0.0        ,

    f_newincome                    number(16,2)    DEFAULT 0.0        ,

    c_specialcode                  varchar2(20)    DEFAULT ' '        ,

    f_pendsubmit02shr              number(16,2)    DEFAULT 0.0        ,

    f_pendsubmit03shr              number(16,2)    DEFAULT 0.0        ,

    f_pendsubmit50shr              number(16,2)    DEFAULT 0.0        ,

    f_assignshare                  number(16,2)    DEFAULT 0.0        ,

    f_pendsubmit13shr              number(16,2)    DEFAULT 0.0        ,

    f_pendsubmit16shr              number(16,2)    DEFAULT 0.0        ,

    f_lastremainshares             number(16,2)    DEFAULT 0.0        ,

    f_pendsubmit14shr              number(16,2)    DEFAULT 0.0        ,

    f_pendsubmit15shr              number(16,2)    DEFAULT 0.0        ,

    f_lastincome                   number(16,2)    DEFAULT 0.0        ,

    d_orilastdeductdate            number(8,0)     DEFAULT 0          ,

    f_costprice                    number(15,12)   DEFAULT 0.0        ,

    f_lastmanagefare               number(16,2)    DEFAULT 0.0        ,

    f_apportionratio               number(9,8)     DEFAULT 0.0        ,

    f_fareratio                    number(5,4)     DEFAULT 0.0        ,

    f_backfareratio                number(9,8)     DEFAULT 0.0        ,

    f_minbackratio                 number(5,4)     DEFAULT 0.0        ,

    f_orifareratio                 number(5,4)     DEFAULT 0.0        ,

    d_cyclestartdate               number(8,0)     DEFAULT 0          ,

    d_cycleenddate                 number(8,0)     DEFAULT 0          ,

    d_cyclenextdate                number(8,0)     DEFAULT 0          ,

    l_cycle                        number(10,0)    DEFAULT 0          ,

    f_evennetvalue                 number(7,4)     DEFAULT 0.0        ,

    c_protectflag                  char(1)         DEFAULT ' '        ,

    f_backfare                     number(16,2)    DEFAULT 0.0        ,

    f_costfare                     number(16,2)    DEFAULT 0.0        ,

    c_lastoutfundcode              varchar2(20)    DEFAULT ' '        ,

    f_redeemedbaseshr              number(16,2)    DEFAULT 0.0        ,

    l_incserno                     number(20,0)    DEFAULT 0          ,

    f_lastdeductnetvalue           number(7,4)     DEFAULT 0.0        ,

    f_lastdeductasset              number(16,2)    DEFAULT 0.0        ,

    f_newnetvalue                  number(7,4)     DEFAULT 0.0        ,

    f_newasset                     number(16,2)    DEFAULT 0.0        ,

    c_actcode                      varchar2(20)    DEFAULT ' '       

) enable row movement,rowdependencies,flashback archive;

alter table xyz_test add primary key(c_cserialno, c_tenantid);

-- create simulate procedure

create or replace procedure insert_sharedetail is

i number;

fundcode VARCHAR2(10);

begin

i:=0;

loop

i:=i+1;

if i>2000 then

  exit;

end if;

fundcode := replace(lpad(floor(i/100000),6),' ','0');

INSERT INTO xyz_test(C_TENANTID, C_TACODE, C_FUNDACCO, C_AGENCYNO, C_NETNO, C_TRADEACCO, C_FUNDCODE, C_SHARETYPE, D_CDATE, C_CSERIALNO, C_SOURCETYPE, F_REMAINSHARES, D_REGISTDATE, F_ORICFMBALANCE, F_ORICFMSHARES, F_ORINETVALUE, C_ORISOURCE, F_RULEAGIO, F_ORIAGIO, F_MAXALLOTRATIO, F_MINREDEEMRATIO, C_HOLDFLAG, C_ACCEPTMODE, C_FIRSTCSERAILNO, C_FUNDMETHOD, C_BANKNO, C_SUBFUNDMETHOD, F_MANAGEFARE, F_INTERESTSHARE, D_LASTDEDUCTDATE, F_INCOME, F_NEWINCOME, C_SPECIALCODE, F_PENDSUBMIT02SHR, F_PENDSUBMIT03SHR, F_PENDSUBMIT50SHR, F_ASSIGNSHARE, F_PENDSUBMIT13SHR, F_PENDSUBMIT16SHR, F_LASTREMAINSHARES, F_PENDSUBMIT14SHR, F_PENDSUBMIT15SHR, F_LASTINCOME, D_ORILASTDEDUCTDATE, F_COSTPRICE, F_LASTMANAGEFARE, F_APPORTIONRATIO, F_FARERATIO, F_BACKFARERATIO, F_MINBACKRATIO, F_ORIFARERATIO, D_CYCLESTARTDATE, D_CYCLEENDDATE, D_CYCLENEXTDATE, L_CYCLE, F_EVENNETVALUE, C_PROTECTFLAG, F_BACKFARE, F_COSTFARE, C_LASTOUTFUNDCODE, F_REDEEMEDBASESHR, L_INCSERNO, F_LASTDEDUCTNETVALUE, F_LASTDEDUCTASSET, F_NEWNETVALUE, F_NEWASSET, C_ACTCODE) VALUES ('*', 'F6', 'F60000000031', '002', '002', '110002003', fundcode, 'A', '20180914', replace(lpad(i,20),' ','0'), '0', '10000.50', '20180914', '10000.50', '10000.50', '1', '0', '1', '1', '0', '0', '0', ' ', '6449654002077306883', ' ', NULL, ' ', NULL, '0', NULL, NULL, NULL, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', NULL, '0', '0', '0', '0', NULL, NULL, NULL, NULL, NULL, '0', '0', NULL, '0', NULL, NULL, '6449654002081501184', '0', '0', '0', '0', ' ');

end loop;

commit;

end insert_sharedetail;

/

create or replace procedure update_sharedetail is

i number;

fundcode VARCHAR2(10);

begin

update xyz_test set c_fundcode = '027010';

commit;

end update_sharedetail;

/

create or replace procedure delete_sharedetail is

i number;

fundcode VARCHAR2(10);

begin

delete from xyz_test;

commit;

end delete_sharedetail;

/

create or replace procedure sharedetail is

i number;

fundcode VARCHAR2(10);

begin

i:=0;

loop

i:=i+1;

if i>200 then

  exit;

end if;

insert_sharedetail();

update_sharedetail();

delete_sharedetail();

end loop;

end sharedetail;

/

-- start test, in ssd, about 500s. in hdd, about 2000s.

call sharedetail();

-- query the wrong result. maybe one time is not always repeat,two more times can always repeat.

select * from dba_flashback_archive_tables where table_name='XYZ_TEST'; -- get the corresponding fda hist table

select to_char(scn_to_timestamp(startscn),'yyyy-MM-dd HH24:mi:SS:FF'),

       to_char(scn_to_timestamp(endscn),'yyyy-MM-dd HH24:mi:SS:FF'),

       to_char(scn_to_timestamp(nextstart),'yyyy-MM-dd HH24:mi:SS:FF'),

       to_char(scn_to_timestamp(nextend),'yyyy-MM-dd HH24:mi:SS:FF'),

       startscn,

       endscn,

       nextstart,

       nextend,

       errxid,

       a.*

  from (

       select lead(startscn, 1, dbms_flashback.get_system_change_number) over (partition by c_cserialno order by endscn, startscn) nextstart,

              lead(endscn, 1, dbms_flashback.get_system_change_number) over (partition by c_cserialno order by endscn, startscn) nextend,

              lead(xid, 1, null) over(partition by c_cserialno order by endscn, startscn) errxid,

              a.*

         from SYS_FBA_HIST_116447 a) a  -- SYS_FBA_HIST_116447为上面查询出来对应的闪回归档历史表

where endscn < startscn or nextstart < endscn or nextend < nextstart

order by a.c_cserialno, a.endscn, a.startscn;

thx very much for everything.

top.gun

"can someone help to check whether it is a bug??"

You have a support agreement with Oracle, so raise an SR with them.

Unknown

we don't have metalink account. can you help?