Re: FLASHBACK ANY TABLE

From: Kevin Jernigan <kevin.jernigan_at_oracle.com>
Date: Wed, 18 Mar 2015 18:04:05 -0700
Message-ID: <550A2085.6050407_at_oracle.com>

The way Oracle Database works, there won't be any inconsistent results, as long as they reliably record the exact query time for the query that produced the chart. Flashback Query will show the data that was in the table(s) as of the timestamp (or SCN) given to the query. If a transaction commits after that timestamp, it won't be included in the results of the flashback query; if a transaction commits before that timestamp, it will be included.

-KJ

-- 
Kevin Jernigan
Senior Director Product Management
Advanced Compression, Hybrid Columnar
Compression (HCC), Database File System
(DBFS), SecureFiles, Database Smart Flash
Cache, Total Recall, Database Resource
Manager (DBRM), Direct NFS Client (dNFS),
Continuous Query Notification (CQN),
Index Organized Tables (IOT), Information
Lifecycle Management (ILM)
+1-650-607-0392 (o)
+1-415-710-8828 (m)

On 3/18/15 5:59 PM, Kumar Madduri wrote:


>  I am concerned still about concurrency issues and inconsistent results.

>

> On Wed, Mar 18, 2015 at 5:46 PM, Kevin Jernigan 

> <kevin.jernigan_at_oracle.com <mailto:kevin.jernigan_at_oracle.com>> wrote:

>

>     So, this isn't FLASHBACK TABLE, this is FLASHBACK QUERY, which

>     will work with undo data if it hasn't aged out, and if they need

>     to extend it further back in time, they can enabled Flashback Data

>     Archive on the tables to be queried...KJ

>

>     -- 

>     Kevin Jernigan

>     Senior Director Product Management

>     Advanced Compression, Hybrid Columnar

>     Compression (HCC), Database File System

>     (DBFS), SecureFiles, Database Smart Flash

>     Cache, Total Recall, Database Resource

>     Manager (DBRM), Direct NFS Client (dNFS),

>     Continuous Query Notification (CQN),

>     Index Organized Tables (IOT), Information

>     Lifecycle Management (ILM)

>     +1-650-607-0392  <tel:%2B1-650-607-0392>  (o)

>     +1-415-710-8828  <tel:%2B1-415-710-8828>  (m)

>

>     On 3/18/15 5:39 PM, Kumar Madduri wrote:

>>     This is the response we got from the developer. I think there

>>     should be a better approach than this one though. It is not

>>     guanteed to work always and they should probably have a logic to

>>     see if data is stale and requery instead of tryinig to use

>>     flashback to show them 'old' data which would be misleading as well.

>>     But here is the developers response for which I replied on

>>     similar lines (above)

>>

>>     *Custom App*which is developed pointing to real-time custom Gift

>>     data (XXDL..tables) will show *Graph / Bar-Chart* first as you

>>     can see below in screenshot and when user wants to see actual

>>     data/gift transactions for any bar in chart, he/she has to click

>>     bar and report will appear down below the chart, but what if

>>     person spent few minutes in looking at chart and clicking bar

>>     after few minutes to see data/transaction behind the bar. *_Data

>>     in the report will be out of sync with chart _*if any new

>>     transaction created/updated between time the person generated

>>     chart and click the bar in the chart to see data in report.

>>

>>     For example, as per screenshot, Report is showing 5 transactions

>>     when user clicked 5th bar in chart. Assume that this Chart is

>>     generated around 10 AM and user spent 5 minutes in chart to

>>     understand/review chart/for some reason and assume 2 new

>>     transactions got created around 10:02 AM. Now, At 10:05 AM, when

>>     user click bar, report will be generated showing 7 transactions

>>     (including 2 new transactions), but graph will still shows 5

>>     which will be out of sync with report. But if report is designed

>>     to generate/show data as of 10:00 AM instead of current time

>>     (which 10:05 AM), user would see only 5 transaction which will be

>>     in sync with Graph.

>>

>>     So here is *implementation approach* we took to resolve this issue.

>>

>>     Time will be noted down in apex variable , say

>>     *x_chart_generated_time*, when chart is generated and report will

>>     be generated using flasback sql as shown below.

>>

>>     SELECT * FROM XXDL.XXDL_GIFT_TRANSMITTALS *as of timestamp

>>     x_chart_generated_time*

>>

>>             Instead of,

>>

>>            SELECT * FROM XXDL.XXDL_GIFT_TRANSMITTALS

>>

>>

>>     On Wed, Mar 18, 2015 at 5:27 PM, Mark Burgess

>>     <mark_at_burgess-consulting.com.au

>>     <mailto:mark_at_burgess-consulting.com.au>> wrote:

>>

>>         Kumar,

>>

>>         I can’t see any reason why you would want to give flashback

>>         table to the APPS schema - the potential for damage on the

>>         core eBS tables is too high. A flashback table on an eBS

>>         table would likely introduce major data integrity issues let

>>         alone support issues.

>>

>>         Perhaps flashback query is something that the developers are

>>         looking for?

>>

>>         Regards,

>>

>>         Mark

>>

>>         > On 18 Mar 2015, at 10:50 pm, Kumar Madduri

>>         <ksmadduri_at_gmail.com <mailto:ksmadduri_at_gmail.com>> wrote:

>>         >

>>         > Hello:

>>         > Our developers have requested flashback any table to be

>>         given to apps (in an ebusiness environment). They would not

>>         have the apps password in production. I think they are using

>>         it to build some feature in a custom app.

>>         > Regardless of their motivation, I think this is a security

>>         risk because why would a developer want this privilege  in a

>>         production environment.

>>         >

>>         > I cant think like a hacker but it does not sound right to me.

>>         > Want to confirm with the list if I am missing something ?

>>         >

>>         > Thank you

>>         > Kumar

>>

>>

>

>



--
http://www.freelists.org/webpage/oracle-l

Received on Thu Mar 19 2015 - 02:04:05 CET