(转)關於flashback query的ORA-01466錯誤
摘自:http://blog.sina.com.cn/s/blog_70a2bdb80100pqid.html
- 使用Oracle 10g 新特性flashback query來查詢過去修改並已提交的記錄時,遇到以下錯誤:
- SQL> select employee_id, salary from hr.employees
as of timestamp(to_timestamp('08-MAR-11 04.15.00','DD-MON-YY HH24.MI.SS'))
2 3 ;
select employee_id, salary from hr.employees
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed - 根據官方文檔所提示,所查詢的表其結構在flashback查詢之前已被修改。 即使用flashback query指定回退查詢的時間點在該表當前結構之前。
- ORA-01466: unable to read data - table definition has changed
Cause: Query parsed after tbl (or index) change, and executed w/old snapshot
Action: commit (or rollback) transaction, and re-execute
在發生錯誤的SQL代碼中排錯後發現,要flashback query的時間點08-MAR-11 04.15.00,其實是下午04點。而後面的日期格式'DD-MON-YY HH24.MI.SS'中小時格式(HH24)採用的是二十四小時制,所以正確寫法應該如下:
to_timestamp('08-MAR-11 16.15.00','DD-MON-YY HH24.MI.SS')
發生錯誤是因為04.15.00那一刻,hr.employees表還沒有創建,產生ORA-01466錯誤。所謂細節體現功底。
posted on 2013-12-14 17:02 newmanzhang 阅读(1401) 评论(0) 编辑 收藏 举报