2 Replies Latest reply: Dec 4, 2012 8:52 AM by 976234 RSS

    moved post about flashback

    976234
      Hi
      I have a little problem with question about Flashback Version Query (it's from SQL Expert Certified).
      This is the question:
      Which statment is true regarding Flashback Version Query (select the best choise)
      a) It returns version of rows only within transaction
      b) It can bu used in subqueries contained only in SELECT statment
      c) It will return an error if undo retention time is less thea the lower bound time od SCN specified
      d) It retrieves all versions including the deleted as well as subsequentaly inserted version of the rows.
      Correct answer (according to author): d

      In my opinion

      Answer A is incorrect if we assume that "within" mean between two commit. In other way if we assume that it means committed operacion for me it's OK
      Answer B is incorrect
      Answer C is correct (in my opinion). For example:
      select * from test_fq 
      versions between timestamp 
      systimestamp - interval '0 0:01:30' DAY to second
      and systimestamp ;
      
      
      
         CHAT_ID CHAT_USER YACKING                                
      ----------- --------- ----------------------------------------
                2 TOMEK     xxxx                                     
               21 TOMEK     haha21                                   
      but
      select * from test_fq 
      versions between timestamp 
      systimestamp - interval '0 0:51:30' DAY to second
      and systimestamp ;
      
      ORA-30052: niepoprawne wyrażenie dolnej granicy migawki
      30052. 00000 -  "invalid lower limit snapshot expression"
      *Cause:    The lower limit snapshot expression was below the UNDO_RETENTION
                 limit.
      *Action:   Specify a valid lower limit snapshot expression.
      Answer D - I think it is incorrect beacuse we don't see all versions, only committed ones...


      Thank you in advance for your help

      Edited by: Tomi13 on 2012-12-03 10:37

      Edited by: Tomi13 on 2012-12-03 10:58

      Edited by: Tomi13 on 2012-12-04 04:24