This discussion is archived
2 Replies Latest reply: Dec 4, 2012 6:52 AM by 976234 RSS

moved post about flashback

976234 Newbie
Currently Being Moderated
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                                   
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
*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


  • Correct Answers - 10 points
  • Helpful Answers - 5 points