更新期间Oracle日期损坏 [英] Oracle date corruption during update
问题描述
我正在将一些数据从一个oracle模式/表迁移到同一数据库上的新模式/表.
迁移脚本执行以下操作:
create table newtable as select
...
cast(ACTIVITYDATE as date) as ACTIVITY_DATE,
...
FROM oldtable where ACTIVITYDATE > sysdate - 1000;
如果我查看原始数据，看起来很好-这是一条记录:
select
activitydate,
to_char(activitydate, 'MON DD,YYYY'),
to_char(activitydate, 'DD-MON-YYYY HH24:MI:SS'),
dump(activitydate),
length(activitydate)
from orginaltable where oldpk = 1067514
结果:
18-NOV-10 NOV 18,2010 18-NOV-2010 12:59:15 Typ=12 Len=7: 120,110,11,18,13,60,16
已迁移的数据，表明数据已损坏:
select
activity_date,
to_char(activity_date, 'MON DD,YYYY'),
to_char(activity_date, 'DD-MON-YYYY HH24:MI:SS'),
dump(activity_date),
length(activity_date)
from newtable
where id = 1067514
结果:
18-NOV-10 000 00,0000 00-000-0000 00:00:00 Typ=12 Len=7: 120,110,11,18,13,0,16
35万条记录中有大约5000条显示此问题.
谁能解释这是怎么发生的?
更新:
我在Oracle支持站点上找不到任何有关此特定类型的DATE损坏的已发布参考. (可能在那儿，我的快速搜索并没有把它打开.)
- Baddate脚本来检查数据库的损坏日期[ID 95402.1]
- 错误2790435-具有并行SELECT和类型转换的串行INSERT可以插入损坏的数据[ID 2790435.8]
DUMP()函数的输出显示日期值确实无效:
Typ=12 Len=7: 120,110,11,18,13,0,16
我们希望分钟字节的值应该在1到60之间，而不是零.
DATE值的7个字节依次表示世纪(+100)，年(+100)，月，日，小时(+1)，分钟(+1)，秒(+1). /p>
当我从Pro * C程序(其中绑定值以内部7字节表示形式提供，完全绕过正常值)提供DATE值作为绑定变量时，我唯一看到这样的无效DATE值的情况捕获无效日期的验证例程，例如2月30日)
考虑到您发布的Oracle语法，没有理由期待您看到的行为.
这是一个虚假的异常(内存损坏?)，或者如果这是可重复的，那么这就是Oracle代码中的一个缺陷(错误).如果这是Oracle代码中的缺陷，则最有可能的怀疑对象是未修补发行版中的新"功能.
(我知道CAST是标准的SQL函数，在其他数据库中已有很长的历史了.我想我是老派，并且从未将它引入我的Oracle语法表中.我不知道什么版本的Oracle就是引入了CAST，但在发布的第一个版本中我会远离它.)
大的危险信号"(另一位评论者指出)是CAST( datecol AS DATE).
您可能希望优化器将其视为等同于date_col ...，但过去的经验表明，TO_NUMBER( number_col )实际上被优化器解释为TO_NUMBER( TO_CHAR ( number_col ) ).
我怀疑不必要的CAST可能会发生类似的情况.
根据您显示的一条记录，我怀疑问题出在显示错误的原因是分钟或秒的值为"59"，而小时的值可能为"23".
我会尝试检查将分钟，小时或秒存储为0的地方:
SELECT id, DUMP(activitydate)
FROM newtable
WHERE DUMP(activitydate) LIKE '%,0,%'
OR DUMP(activitydate) LIKE '%,0'
I'm migrating some data from one oracle schema/table to a new schema/table on the same database.
The migration script does the following:
create table newtable as select
...
cast(ACTIVITYDATE as date) as ACTIVITY_DATE,
...
FROM oldtable where ACTIVITYDATE > sysdate - 1000;
If I look at the original data, it looks fine - here's one record:
select
activitydate,
to_char(activitydate, 'MON DD,YYYY'),
to_char(activitydate, 'DD-MON-YYYY HH24:MI:SS'),
dump(activitydate),
length(activitydate)
from orginaltable where oldpk = 1067514
Result:
18-NOV-10 NOV 18,2010 18-NOV-2010 12:59:15 Typ=12 Len=7: 120,110,11,18,13,60,16
The migrated data, showing that the data is corrupt:
select
activity_date,
to_char(activity_date, 'MON DD,YYYY'),
to_char(activity_date, 'DD-MON-YYYY HH24:MI:SS'),
dump(activity_date),
length(activity_date)
from newtable
where id = 1067514
Result:
18-NOV-10 000 00,0000 00-000-0000 00:00:00 Typ=12 Len=7: 120,110,11,18,13,0,16
Around 5000 out of 350k records show this problem.
Can anyone explain how this happened?
UPDATE:
I don't find any published reference to this specific type of DATE corruption on the Oracle support site. (It may be there, my quick searches just didn't turn it up.)
- Baddate Script To Check Database For Corrupt dates [ID 95402.1]
- Bug 2790435 - Serial INSERT with parallel SELECT and type conversion can insert corrupt data [ID 2790435.8]
The output from the DUMP() function is showing the date value is indeed invalid:
Typ=12 Len=7: 120,110,11,18,13,0,16
We expect that the minutes byte should be a value between one and sixty, not zero.
The 7 bytes of a DATE value represent, in order, century(+100), year(+100), month, day, hour(+1), minutes(+1), seconds(+1).
The only time I have seen invalid DATE values like this when a DATE value was being supplied as a bind variable, from a Pro*C program (where the bind value is supplied in the internal 7 byte representation, entirely bypassing the normal validation routines that catch invalid dates e.g. Feb 30)
There is no reason to expect the behavior you're seeing, given the Oracle syntax you posted.
This is either a spurious anomaly (memory corruption?) or if this is repeatable, then it's a flaw (bug) in the Oracle code. If it's a flaw in the Oracle code, the most likely suspects would be "newish" features in an un-patched release.
(I know CAST is a standard SQL function that's been around for ages in other databases. I guess I'm old school, and have never introduced it into my Oracle-syntax repertoire. I don't know what version of Oracle it was that introduced the CAST, but I would have stayed away from it in the first release it appeared in.)
The big 'red flag' (that another commenter noted) is that CAST( datecol AS DATE).
You would expect the optimizer to treat that as equivalent to date_col ... but past experience shows us that TO_NUMBER( number_col ) is actually interpreted by the optimizer as TO_NUMBER( TO_CHAR ( number_col ) ).
I suspect something similar might be going on with that unneeded CAST.
Based on that one record you showed, I suspect the issue is with values with a "59" value for minutes or seconds, and possibly a "23" value for hours, would be the ones that show the error.
I would try checking for places where the minutes, hour or seconds are stored as 0:
SELECT id, DUMP(activitydate)
FROM newtable
WHERE DUMP(activitydate) LIKE '%,0,%'
OR DUMP(activitydate) LIKE '%,0'
这篇关于更新期间Oracle日期损坏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
