Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, ukemelem.

Asked: November 13, 2020 - 9:14 am UTC

Answered by: Connor McDonald - Last updated: November 17, 2020 - 1:55 am UTC

Category: Database Administration - Version: 12cR2

Viewed 100+ times

You Asked

Am trying to view the database to see the transaction that has taken place in the database. but whenever i query the flashback_transaction_query, it returns 'no rows selected' pls, is there something i need to do that am not doing, i need your help. below is the sql statement i ran to view the table.

SQL> ed
Wrote file afiedt.buf

  1  select name, course, id, versions_operation,versions_starttime as starttime, versions_xid from dept
  2  versions between timestamp
  3  to_timestamp('13-nov-2020 02:58:45', 'dd-mon-yyyy hh24:mi:ss')
  4* and to_timestamp('13-nov-2020 03:09:07', 'dd-mon-yyyy hh24:mi:ss')
SQL> /

NAME    COURSE       ID V STARTTIME               VERSIONS_XID
---------- ---------- ---------- - --------------------------------------------------------------------------- ----------------
Stephen    Mech Eng        5 U 13-NOV-20 03.00.56 AM             0200010015050000
Moses    Chem101        2 D 13-NOV-20 02.59.57 AM             03000F00DD040000
Moses    Chem101        2
Belle    chem101        3
Moniq    Mech Eng        5
Serah    Phy201        2 I 13-NOV-20 03.03.20 AM             0700150038040000
Ian    Phy101        1 I 13-NOV-20 03.03.20 AM             0700150038040000
Ehiedu    micro203        4
Loveth    Med         6

9 rows selected.
SQL> ed
Wrote file afiedt.buf

  1  select xid, start_scn,logon_user, table_name, undo_sql from flashback_transaction_query
  2* where xid=hextoraw('0200010015050000')
SQL> /

no rows selected

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.SQL> select xid, start_scn,logon_user, table_name, undo_sql from flashback_transaction_query
  2  where xid=hextoraw('0200010015050000');

no rows selected

SQL> select * from dba_flashback_txn_report;

no rows selected


am using ORACLE 12cR2 on oracle linux operating system. Thank you.


and we said...

There are two possibilities here

1) You need to have supplemental logging enabled for this to work. Have you done that?

2) Bug 10358019 was logged a while back for this issue, but was *meant* to be fixed in 12.1.

So if you *do* have supplemental logging enabled, I think you'll need to log a call with Support because the expectation is that if you can print out a XID from VERSIONS BETWEEN than you should be able to see it with flashback_transaction_query.


and you rated our response

  (1 rating)

Reviews

flashback transaction query

November 16, 2020 - 7:57 am UTC

Reviewer: ebite friday from Nigeria

Thanks for the reply, I have enabled all the supplemental logging still showing no rows selected.
Connor McDonald

Followup  

November 17, 2020 - 1:55 am UTC

It only takes effect from the moment you enabled it - you can't go back past that now.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.