flashback query输出结果错误？

显示全部楼层 · 2011-8-31 15:27:58

本帖最后由 Kevin__Zhang 于 2012-6-18 16:56 编辑
SYS @ test8 >create table testk.ttt as select * from dba_objects;
Table created.
-----过10分钟:
SYS @ test8 > select OBJ#,DATAOBJ#,OWNER#,NAME,CTIME,status,TYPE#fromobj$where name='TTT';
OBJ# DATAOBJ# OWNER# NAME
CTIME
STATUSTYPE#
---------- ---------- ---------- -------------------------------------------------- --------------- ---------- ----------
6081860818 49 TTT
18-JUN-12
1
2
SYS @ test8 > select OBJ#,DATAOBJ#,OWNER#,NAME,CTIME,status,TYPE#fromobj$ as of timestamp to_timestamp(sysdate-1/(60*24*60)) where name='TTT';
no rows selected
SYS @ test8 > select OBJ#,DATAOBJ#,OWNER#,NAME,CTIME,status,TYPE#fromobj$ as of timestamp to_timestamp(sysdate-0)where name='TTT';
no rows selected
闪回没有输出结果，Why?
============================================
解决。2了。重新读了一遍是自己写的SQL有问题，没格式化时间，而当前数据库的默认时间设置是：
nls_date_format
stringDD-MON-RR
nls_timestamp_format
stringDD-MON-RR HH.MI.SSXFF AM
所以获取sysdate时只到day，再用to_timestamp转换后，小时分钟秒都丢了。解决方法就是去掉to_timestamp函数，或者：
SYS @ test8 > alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SYS @ test8 > alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
然后再次查询：
SYS @ test8 > select OBJ#,DATAOBJ#,OWNER#,NAME,CTIME,status,TYPE#fromobj$ as of timestamp to_timestamp(sysdate-1/(60*24*60)) where name='TTT';
OBJ# DATAOBJ# OWNER# NAME
CTIME
STATUSTYPE#
---------- ---------- ---------- -------------------------------------------------- ------------------- ---------- ----------
6081860818 49 TTT
2012-06-18 01:42:33
1
2
这样结果就正确了。

千问 · 2011-8-31 15:27:58

以下是trace文件显示的对这一行所在块的CR的构造:
ktrget2(): started for block objd: 0x00000012
env [0x2aed485043d8]: (scn: 0x0000.002297a8xid: 0x0000.000.00000000uba: 0x00000000.0000.00statement num=0parent xid: 0x0000.000.00000000st-scn: 0x0000.00000000hi-scn: 0x0000.00000000ma-scn: 0x0000.00000000flg: 0x00000800)
ktrexf(): returning 9 on:0xb804fc4cr-scn: 0xffff.ffffffffxid: 0x0000.000.00000000uba: 0x00000000.0000.00cl-scn: 0xffff.ffffffffsfl: 0
showing 0x73fe9388 c09ee1 (3) dscn ffffffff:ffff bcrp 0:1, bestcrp (nil)
new dscn ffff:ffffffff ret=2
kcbchg updating CR fields for 0x75ff2a58, 4248760; 0:2297a8
kcbchg new CR fields for 0x75ff2a58, 4248760; 0:2297a8
showing 0x6afac5c8 c09e5e (3) dscn ffffffff:ffff bcrp 0:1, bestcrp (nil)
new dscn ffff:ffffffff ret=2
kcbchg updating CR fields for 0x75ff2a58, 4248760; 0:2297a8
kcbchg new CR fields for 0x75ff2a58, 4248760; 0:2297a8
showing 0x6afac5c8 c09e5e (3) dscn ffffffff:ffff bcrp 0:1, bestcrp (nil)
new dscn ffff:ffffffff ret=2
kcbchg updating CR fields for 0x75ff2a58, 4248760; 0:2297a8
kcbchg new CR fields for 0x75ff2a58, 4248760; 0:2297a8
showing 0x66f8b428 c09e5d (3) dscn ffffffff:ffff bcrp 0:1, bestcrp (nil)
new dscn ffff:ffffffff ret=2
kcbchg updating CR fields for 0x75ff2a58, 4248760; 0:2297a8
kcbchg new CR fields for 0x75ff2a58, 4248760; 0:2297a8
showing 0x72fd7a18 c09e5b (3) dscn ffffffff:ffff bcrp 0:1, bestcrp (nil)
new dscn ffff:ffffffff ret=2
kcbchg updating CR fields for 0x75ff2a58, 4248760; 0:2297a8
kcbchg new CR fields for 0x75ff2a58, 4248760; 0:2297a8
showing 0x72fd7a18 c09e5b (3) dscn ffffffff:ffff bcrp 0:1, bestcrp (nil)
new dscn ffff:ffffffff ret=2
kcbchg updating CR fields for 0x75ff2a58, 4248760; 0:2297a8
kcbchg new CR fields for 0x75ff2a58, 4248760; 0:2297a8
showing 0x72fd7a18 c09e5b (3) dscn ffffffff:ffff bcrp 0:1, bestcrp (nil)
new dscn ffff:ffffffff ret=2
kcbchg updating CR fields for 0x75ff2a58, 4248760; 0:2297a8
kcbchg new CR fields for 0x75ff2a58, 4248760; 0:2297a8
showing 0x70fbff48 c000f9 (3) dscn ffffffff:ffff bcrp 0:1, bestcrp (nil)
new dscn ffff:ffffffff ret=2
kcbchg updating CR fields for 0x75ff2a58, 4248760; 0:2297a8
kcbchg new CR fields for 0x75ff2a58, 4248760; 0:2297a8
showing 0x70fbff48 c000f9 (3) dscn ffffffff:ffff bcrp 0:1, bestcrp (nil)
new dscn ffff:ffffffff ret=2
kcbchg updating CR fields for 0x75ff2a58, 4248760; 0:2297a8
kcbchg new CR fields for 0x75ff2a58, 4248760; 0:2297a8
showing 0x72fed3a8 c00097 (3) dscn ffffffff:ffff bcrp 0:1, bestcrp (nil)
new dscn ffff:ffffffff ret=2
kcbchg updating CR fields for 0x75ff2a58, 4248760; 0:2297a8
kcbchg new CR fields for 0x75ff2a58, 4248760; 0:2297a8
..............
..............
kcbchg updating CR fields for 0x75ff2a58, 4248760; 0:2297a8
kcbchg new CR fields for 0x75ff2a58, 4248760; 0:2297a8
showing 0x6cf858b8 c0e09b (3) dscn ffffffff:ffff bcrp 0:1, bestcrp (nil)
new dscn ffff:ffffffff ret=2
kcbchg updating CR fields for 0x75ff2a58, 4248760; 0:2297a8
kcbchg new CR fields for 0x75ff2a58, 4248760; 0:2297a8
showing 0x79fb0f68 c0e097 (3) dscn ffffffff:ffff bcrp 0:1, bestcrp (nil)
new dscn ffff:ffffffff ret=2
kcbchg updating CR fields for 0x75ff2a58, 4248760; 0:2297a8
kcbchg new CR fields for 0x75ff2a58, 4248760; 0:2297a8
showing 0x79fb0f68 c0e097 (3) dscn ffffffff:ffff bcrp 0:1, bestcrp (nil)
new dscn ffff:ffffffff ret=2
kcbchg updating CR fields for 0x75ff2a58, 4248760; 0:2297a8
kcbchg new CR fields for 0x75ff2a58, 4248760; 0:2297a8
showing 0x78f73a88 c0e096 (3) dscn ffffffff:ffff bcrp 0:1, bestcrp (nil)
new dscn ffff:ffffffff ret=2
kcbchg updating CR fields for 0x75ff2a58, 4248760; 0:2297a8
kcbchg new CR fields for 0x75ff2a58, 4248760; 0:2297a8
showing 0x74fcbe78 c0e08a (3) dscn ffffffff:ffff bcrp 0:1, bestcrp (nil)
new dscn ffff:ffffffff ret=2
kcbchg updating CR fields for 0x75ff2a58, 4248760; 0:2297a8
kcbchg new CR fields for 0x75ff2a58, 4248760; 0:2297a8
ktrgcm(): completed for block objd: 0x00000012
ktrget2(): completed forblockobjd: 0x00000012

千问 · 2011-8-31 15:27:58

本帖最后由 Kevin__Zhang 于 2012-6-18 16:37 编辑
解决。我2了。重新读了一遍自己写的SQL，居然带上了to_timestamp函数。
去掉to_timestamp，解决问题。

千问 · 2011-8-31 15:27:58

帮顶一下观望