Skip to Main Content
  • Questions
  • No data is logged in flashback_transaction_query view

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Jinseul.

Asked: June 09, 2017 - 1:44 am UTC

Last updated: June 15, 2017 - 11:39 am UTC

Version: 12.1.0

Viewed 1000+ times

You Asked

Hi,
I have been using flashback_transaction_query view since the previous version
but in 12.1.0, I got the strange situation that is not explained with any reference.

As far as I know, user has to enable supplemental logging to log some flashback data (such as undo sql ...) since 11 version.
In 11g version, there is no issue to use the view (flashback_transaction_query) by enabling minimum supplemental logging.
but after using 12 version,
I couldn't get any data in flashback_transaction_query view even though I enabled supplemental logging and archive mode(not actually necessary)
I selected with sys account and it is a common user (also gave select transaction privilege)
I can execute select statement itself (select * from flashback_transaction_query) but no data is shown in this view even after making a transaction.

I also have two DBs that are 12.1 version.
This issue only happens only in one of DBs that is CDB. (the other is non-CDB and no issue)
As I checked 12.1.0 reference, it is described that data dictionary and undo tablespace are shared for all common users.

Is there something I need to do additionally to log transaction history in flashback_transaction_query?
Which variables or privileges are related to this view (flashback_transaction_query)?

I appreciate your help in advance!

and Connor said...

flashback_transaction_query is part of flashback data archive, which is not supported in various levels in 12.1

See here for details

https://docs.oracle.com/database/121/ADFNS/adfns_flashback.htm#ADFNS01013

Fixed in 12.2

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Multitenant

Need more information on Multitenant? Check out the Multitenant docs for the Oracle Database