DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: 10g - Flashback Versions Query help

  1. #1
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204

    10g - Flashback Versions Query help

    The 10g flashback versions query sounds really cool. But I must be doing something wrong.
    --START SCRIPT---------------------
    drop table exchanges purge;
    create table exchanges (currency varchar2(4), rate number(15,10));
    insert into exchanges values ('EURO', 1.1012);
    commit;
    update exchanges set rate = 1.1014;
    commit;
    update exchanges set rate = 1.1013;
    commit;
    delete exchanges;
    commit;
    insert into exchanges values ('EURO',1.1016);
    commit;
    update exchanges set rate = 1.1011;
    commit;
    select versions_starttime,
    versions_endtime,
    versions_xid,
    versions_operation,
    rate
    from exchanges
    versions between timestamp minvalue and maxvalue
    order by versions_starttime;
    --END SCRIPT---------------------

    --START MY RESULTS---------------
    VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID VERSIONS_OPERATION RATE
    1.1011
    --END MY RESULTS-----------------
    According to http://otn.oracle.com/pub/articles/1...k1_10gdba.html
    I'm supposed to get:
    VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V RATE
    ---------------------- ---------------------- ---------------- - ----------
    01-DEC-03 03.57.12 PM 01-DEC-03 03.57.30 PM 0002002800000C61 I 1.1012
    01-DEC-03 03.57.30 PM 01-DEC-03 03.57.39 PM 000A000A00000029 U 1.1014
    01-DEC-03 03.57.39 PM 01-DEC-03 03.57.55 PM 000A000B00000029 U 1.1013
    01-DEC-03 03.57.55 PM 000A000C00000029 D 1.1013
    01-DEC-03 03.58.07 PM 01-DEC-03 03.58.17 PM 000A000D00000029 I 1.1016
    01-DEC-03 03.58.17 PM 000A000E00000029 U 1.1011
    --
    Would someone please run this script for me on your system and let me know if you get the same results I'm getting.
    Thank you,
    -Ken
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Not tested yet.. BUT I guess it depends on value of UNDO_RETENTION. Check it.
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  3. #3
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Yea, it hangs on to versions if there is room in UNDO (via UNDO_RETENTION).

    Check this out.. If I run each statement in a script, then I get NOTHING for versions, If I run it slowly, commit after each statement, AND check each operations' results in another session, it works perfectly! (good enough to demo)

    This is a cool feature. Just need to figure it out it's characteristics, and maybe this is a bug.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width