主页 / dba / 问题 / 56038
Accepted
DHW
Asked: 2014-01-03 17:27:42 +0800 CST

在不终止服务器的情况下捕获表更改

  • 772

我有一个使用 Oracle 的应用程序。在许多事情中，此应用程序具有时间表条目。主管以适当的费率（直接或加班）输入员工的工作时间，分配给收费代码 - 并保存。

我们需要每天向第三方报告（上传）小时数据。如果上传后更改小时数，我们需要提交调整上传，并附上 delta。

起初我以为我会用触发器来处理这个问题。在时间卡表中添加时间戳列，每次上传时，我们都会更新所有未加盖时间戳的时间卡的时间戳。然后，只要发生插入或更新，触发器就会触发......如果有时间戳，它会添加旧值和新值之间的增量并将其存储在新表中 - 我们将其作为调整上传。在我的脑海里——这很好而且很整洁。

但是，在仔细检查数据（查看 ROWID）后，我意识到每当给定时间表中的任何值发生更改并执行 SAVE 操作时，应用程序都会发出 DELETE 操作，然后是 INSERT 操作。所以任何时间戳都会被抹去——我所能做的就是用一堆无用的数据来膨胀我的调整表。

因此，我开始认为，如果我每次报告小时数时都创建 Timecard 表的快照......那么我可以简单地将 timecard 与快照进行比较 - 并获得调整。然后我只需在调整后刷新快照。

快照选项让我担心 - 在我正在尝试这些东西的测试服务器上 - 我只有 2500 条记录可供使用 - 在生产中它接近一百万。另外，我不确定是否删除所有记录，然后在一个大小真的很聪明的表上执行 INSERT INTO 语句？

我查看了 CREATE SNAPSHOT - 因为我认为日志会比创建重复表更有效 - 但我没有看到您可以像查询常规表一样查询快照。

另一种选择是将时间卡表结构复制到一个新表（timecardsnap），然后在时间卡表上创建一个触发器（对于每一行），然后引入一些逻辑来检查它是否已经被导出 - 如果它有, 插入增量 .. 如果它没有更新现有数据。但是，如果更改了收费代码，这就会出现问题——因为我最终会重复值。鉴于其 DELETE / INSERT - 两个独立的操作 - 不容易查看旧值与新值。

解决此问题且不会对服务器性能产生负面影响的最佳方法是什么？

编辑：Oracle 10g。

更新 - 1 月 3 日 - 下午 5 点 MST

我开始玩临时表的想法。我基本上把表可能的操作分成了三种可能的操作类型，并问自己每个应该执行什么动作。

删除时 - 我们需要取消刚刚删除的所有时间 插入时 - 我们需要将新的时间添加到下一次导出中，减去已经上传的时间。我们还需要确保不存在尚未导出的先前添加。

为此，我创建了两张表：一张用于存储 PENDING 更改，另一张用于存储 EXPORTED 数据。在应用程序中添加和删除行时，挂起的表会发生变化，而 EXPORTED 保持静态。

我使用术语“拆分”来指代我们的关键字段——EMPID、WORKDAY、COSTCODE、DEPT，因为它们基本上定义了我们记录值（小时）的粒度级别。

所以现在我有了两个表 - 我创建了一个触发器。触发器基本上说：

如果 DELETE 操作：

  • 将刚刚删除的所有小时数 (OLD) 乘以 -1
  • 将小时数添加到 PENDING 表。

如果 INSERT 操作：

  • 从 PENDING 表中删除与相同拆分匹配的所有小时
  • EXPORTED 表中匹配相同拆分的总小时数。
  • 将新插入的小时数 (NEW) 和已导出的小时数的增量添加到 PENDING 表。

然后，每当发生导出时，我们只需将 PENDING 表中的所有时间按拆分汇总，并附加到 EXPORTED 表。

那应该可以解决问题。我还将编写一个 UPDATE 触发器，以防应用程序确实在其他地方对 TIMECARD 表进行更新......只是为了确定。

更新 - 1 月 3 日 - 下午 5:45 MST

我阅读了闪回并得到了一个测试查询 - 它很快而且我喜欢这样一个事实，即没有任何机会被错过。我想我会用这个作为最终解决方案——但同时我会实现我所拥有的。我将设置另一个表来记录导出数据时的 TIMESTAMPS。这样，如果 DBA 增加可用的闪回空间 - 我已经记录了我的导出点。

oracle snapshot

2 个回答

  1. Best Answer

    仅从逻辑角度来看，您将必须拥有 2 个数据副本，或每次更改的日志（包括删除/插入）。你可以像你说的那样使用表/触发器，使用闪回查询（顺便说一下，这将在 11g 上工作，假设当天的所有存档日志仍然在线 - 而且，根据我的经验，它并不昂贵取决于查询的任务）

    你也可以用不同的方式处理它，虽然这种方法更复杂，但在某种程度上它可能会更快，特别是如果对哪些行“在范围内”有一些限制 - 意思是，如果你可以放置某种代码忽略表中的绝大多数行（比如他们不能更新超过一个月的小时数或类似的规则）。

    您创建了第二个表，其中包含更新“范围内”的所有行 - 它只是另一个表的普通旧副本，因为它现在就坐。

    当您的作业运行时，它首先从表中删除“超出范围”的记录，然后运行一个查询来获取已编辑/删除的行。

    select t1.a as a1, t2.a as a2, t1.b as b1, t2.b as b2 
from t1
left join t2 on t2.id=t1.id
...

    开始使用此信息构建您的文件确保使用新值更新 t2（并可能删除已删除的记录）

    然后你运行另一个获取插入行的信息也将这个信息添加到你的文件中同样，将这些行插入到表 2 中。

    最后，这是否更快是一个硬币翻转 - 但至少它会在你可以安排它时运行 - 但闪回查询基本上是相同的事情并让 oracle 处理它。

    在我看来，仅使用闪回查询要简单得多，而且可能更不容易出错。

    • 1

  2. 就像我之前说的，有一个更简单的方法：闪回查询。
    http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS618

    引用 Balazs Papp 的话：

    闪回查询是在 Oracle 9i 中引入的，而闪回是在 10g 中引入的，但它们是不同的东西。闪回查询使用内部撤消机制，因此您可以从撤消中查询对象的过去状态，这对您来说应该足够了（这甚至被数据库使用，例如当您回滚事务时）。默认情况下，对于过去可以查询多远没有限制或保证，但您可以通过 undo_retention 和保留保证来控制它。这需要足够大的 undo 表空间来存储指定保留时间内的所有 undo。

    • 0

相关问题