更新期间 Oracle 日期损坏

发布于 2024-11-29 06:46:21 字数 1172 浏览 4 评论 0原文

我正在将一些数据从一个 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

350k 记录中大约 5000 条显示此问题。

谁能解释这是怎么发生的？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

⒈起吃苦の倖褔 2024-12-06 06:46:21

更新：

我在 Oracle 支持网站上没有找到任何对此特定类型的 DATE 损坏的已发布参考。（它可能就在那里，我的快速搜索只是没有找到它。）

用于检查数据库是否损坏日期的 Baddate 脚本 [ID 95402.1]
2790435.8]

Bug 2790435 - 具有并行 SELECT 和类型转换的串行 INSERT 可以插入损坏的数据 [ ID DUMP() 函数的输出显示日期值确实无效：

Typ=12 Len=7: 120,110,11,18,13,0,16

我们期望分钟字节应该是 1 到 60 之间的值，而不是零。

DATE 值的 7 个字节依次表示世纪（+100）、年（+100）、月、日、小时（+1）、分钟（+1）、秒（+1）。

我唯一一次看到像这样的无效 DATE 值，当 DATE 值作为绑定变量从 Pro*C 程序提供时（其中绑定值以内部 7 字节表示形式提供，完全绕过了正常的验证例程）捕获无效日期，例如 2 月 30 日）

考虑到您发布的 Oracle 语法，没有理由期待您所看到的行为。

这要么是一个虚假的异常（内存损坏？），要么如果这是可重复的，那么它就是 Oracle 代码中的一个缺陷（bug）。如果这是 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'

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'

回复收藏 0 原文

桃酥萝莉 2024-12-06 06:46:21

我在 Pro*C 中也看到过与 spence7593 类似的情况。
可以使用 DBMS_STATS 包以编程方式创建无效日期。
不确定是否有类似的机制可以逆转这种情况。

create or replace function stats_raw_to_date (p_in raw) return date is
  v_date date;
  v_char varchar2(25);
begin
  dbms_stats.CONVERT_RAW_VALUE(p_in, v_date);
  return v_date;
exception
  when others then return null;
end;
/

select stats_raw_to_date(utl_raw.cast_to_raw(
          chr(120)||chr(110)||chr(11)||chr(18)||chr(13)||chr(0)||chr(16)))
from dual;

I've seen similar things to spence7593, again with Pro*C.
It is possible to create invalid dates programmatically using a DBMS_STATS package.
Not sure if there is a similar mechanism to reverse that.

create or replace function stats_raw_to_date (p_in raw) return date is
  v_date date;
  v_char varchar2(25);
begin
  dbms_stats.CONVERT_RAW_VALUE(p_in, v_date);
  return v_date;
exception
  when others then return null;
end;
/

select stats_raw_to_date(utl_raw.cast_to_raw(
          chr(120)||chr(110)||chr(11)||chr(18)||chr(13)||chr(0)||chr(16)))
from dual;

回复收藏 0 原文

~没有更多了~