了解 Oracle 中的 ORA_ROWSCN 行为 [英] Understanding the ORA_ROWSCN behavior in Oracle

问题描述

所以这本质上是关于查找重复记录.

我们每天从文本文件中导入数据，最终我们两次导入了分布在 182 个文件中的 10163 条记录.在运行上述查询以查找重复项时，我们得到的记录总数为 10174，比文件中包含的记录多 11 条.我假设 2 条记录的可能性完全相同，并且在查询中也是有效的记录.所以我认为最好使用时间戳字段并简单地找到今天运行的所有记录(因此最终添加了重复的行).我使用以下查询使用 ORA_ROWSCN:

We perform data imports from text files everyday and we ended up importing 10163 records spread across 182 files twice. On running the query mentioned above to find duplicates, the total count of records we got is 10174, which is 11 records more than what are contained in the files. I assumed about the posibility of 2 records that are exactly the same and are valid ones being accounted for as well in the query. So I thought it would be best to use a timestamp field and simply find all the records that ran today (and hence ended up adding duplicate rows). I used ORA_ROWSCN using the following query:

select count(*) from my_table
where TRUNC(SCN_TO_TIMESTAMP(ORA_ROWSCN)) = '01-MAR-2012'
;

但是，计数仍然更多，即 10168.现在，通过在包含所有文件的文件夹中运行以下命令，我很确定文件中的总行数为 10163.wc -l *.txt.

However, the count is still more i.e. 10168. Now, I am pretty sure that the total lines in the file is 10163 by running the following command in the folder that contains all the files. wc -l *.txt.

是否可以找出哪些行实际上插入了两次?

Is it possible to find out which rows are actually inserted twice?

推荐答案

默认情况下，ORA_ROWSCN 存储在块级别，而不是行级别.如果表最初是在启用 ROWDEPENDENCIES 的情况下构建的，则它仅存储在行级别.假设您可以在一个块中放入表格的多行，并且您没有使用 APPEND 提示在表格的现有高水位标记上方插入新数据，则您可能会插入将新数据放入已经有一些现有数据的块中.默认情况下，这将更改块中每一行的 ORA_ROWSCN，导致您的查询计数的行数比实际插入的行数多.

By default, ORA_ROWSCN is stored at the block level, not at the row level. It is only stored at the row level if the table was originally built with ROWDEPENDENCIES enabled. Assuming that you can fit many rows of your table in a single block and that you're not using the APPEND hint to insert the new data above the existing high water mark of the table, you are likely inserting new data into blocks that already have some existing data in them. By default, that is going to change the ORA_ROWSCN of every row in the block causing your query to count more rows than were actually inserted.

由于 ORA_ROWSCN 只保证是最后一次有 DML 时的上限，因此通过添加 CREATE_DATE 列默认为 SYSDATE 或在 INSERT 运行后依赖于 SQL%ROWCOUNT(假设，当然，您使用的是单个 INSERT 语句来插入所有行).

Since ORA_ROWSCN is only guaranteed to be an upper-bound on the last time there was DML on a row, it would be much more common to determine how many rows were inserted today by adding a CREATE_DATE column to the table that defaults to SYSDATE or to rely on SQL%ROWCOUNT after your INSERT ran (assuming, of course, that you are using a single INSERT statement to insert all the rows).

通常，使用 ORA_ROWSCN 和 SCN_TO_TIMESTAMP 函数将是一种识别行何时插入的有问题的方法，即使表是用 构建的行依赖.ORA_ROWSCN 返回一个 Oracle SCN，它是一个系统更改号.这是特定更改(即交易)的唯一标识符.因此，SCN 和时间之间没有直接联系——我的数据库生成 SCN 的速度可能比你的快一百万倍，我的 SCN 1 可能与你的 SCN 1 相差几年.Oracle 后台进程 SMON 维护一个将 SCN 值映射到近似时间戳的表，但它只在有限的时间内维护该数据——否则，您的数据库最终会得到一个仅存储 SCN 到时间戳映射的数十亿行表.如果该行被插入超过一周前(确切的限制取决于数据库和数据库版本)，SCN_TO_TIMESTAMP 将无法将 SCN 转换为时间戳并将返回一个错误.

Generally, using the ORA_ROWSCN and the SCN_TO_TIMESTAMP function is going to be a problematic way to identify when a row was inserted even if the table is built with ROWDEPENDENCIES. ORA_ROWSCN returns an Oracle SCN which is a System Change Number. This is a unique identifier for a particular change (i.e. a transaction). As such, there is no direct link between a SCN and a time-- my database might be generating SCN's a million times more quickly than yours and my SCN 1 may be years different from your SCN 1. The Oracle background process SMON maintains a table that maps SCN values to approximate timestamps but it only maintains that data for a limited period of time-- otherwise, your database would end up with a multi-billion row table that was just storing SCN to timestamp mappings. If the row was inserted more than, say, a week ago (and the exact limit depends on the database and database version), SCN_TO_TIMESTAMP won't be able to convert the SCN to a timestamp and will return an error.

这篇关于了解 Oracle 中的 ORA_ROWSCN 行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！