-
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
-
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"
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|