实施通用归档流程的最佳方式/技术
我们希望淘汰 Oracle 仓库中的旧数据。
为了给出一个非常简单的概述，建议使用 PL SQL 存储过程开发一个流程，在给定源/目标表等参数的情况下，使用 Oracle ALL_TAB_COLUMNS 视图构建镜像源表的目标表。
如果先前运行中存在目标表，则建议的解决方案包括将源表的当前架构与目标（存档）表进行比较，如果发现差异，则使表同步。我确信所提议的功能存在局限性，但是虽然该规范在该领域似乎相当雄心勃勃，但我怀疑他们是否会在 PL-SQL 中重写 Red Gate 的 SQL Compare 实用程序。
我想我有两个问题。
1) PL/SQL 真的是用于此类任务的正确语言吗？对我来说，存储过程用于快速输入和快速输出数据操作，复杂的逻辑属于我认为功能更齐全的客户端语言，例如 C# 或其他一些 .NET 语言。我预计会有一个 10,000 行、缩进不良的单个存储过程，并且我对必须检查它感到畏缩。我知道 Oracle SP/Pkg 不必如此，但是，出于某种原因，我们的开发人员在使用 PL\SQL 时往往不如在 .NET 中编写时模块化。我欢迎您的建议和选择的理由。
2) 是否有可用于归档目的的 Oracle 实用程序（我认为我们使用的是 10g）？有人有什么建议吗？
在提供评论时，我会对任何不重复的价值进行投票。
谢谢。
We are looking to retire old data in out Oracle warehouse.
To give a very simplified overview, a proposal was suggested to develop a process using PL SQL Stored Procedures that, given source/dest table, etc., parameters, use the Oracle ALL_TAB_COLUMNS view to construct a target table that mirrors the source table.
If the dest table exists from a previous run, the proposed solution includes comparing the current schema of the source table to the target (archive) table and, if differences are found, bringing the tables into sync. I am sure that limitations in the proposed functionality exist, but while the spec appeared to be pretty ambitious in this area, I doubt if they were going to re-write Red Gate's SQL Compare utility in PL-SQL.
I guess I have two questions.
1) Is PL/SQL really the right language to use for such a task. To me, stored procedures are used for quick-in and quick-out data operations and complex logic belongs what I would consider a more fully functional client language such as C# or some other .NET language. I anticipate a 10,000-line, poorly indented single stored procedure and I cringe at having to review it. I know Oracle SP/Pkgs need not be that way, but, for some reason, our developers tend to be less modular when using PL\SQL than when writing in .NET . I would welcome your recommendations and reasons for your choice.
2) Are there Oracle utilities (I think we are on 10g) that can be utilized for archiving purposes? Does anyone have any suggest advise?
I'll up vote any non-repeated worth while comment offered.
Thx.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
PL/SQL 不仅仅用于“快进快出”数据操作。其上构建了非常丰富的应用程序。对于此类任务，PL/SQL 本身并没有什么问题。也就是说，如果您预计 PL/SQL 中的 10K 行过程写得不好，请不要使用它。让你的程序员做他们最擅长的事情。
PL/SQL is NOT just for "quick-in and quick-out" data operations. There a very substantial apps built on it. There is nothing inherently wrong with PL/SQL for this kind of task. That said, if you anticipate a poorly written 10K line procedure in PL/SQL, don't use it. Let your programmers do what they do best.
首先，这听起来像是 PL-SQL 的任务。可以强制执行非模块化代码问题，并且使用 PL-SQL 将为您提供更好的结果并且更易于编写。
至于概念本身，如果架构被更新，您所做的任何解决方案都会遇到问题 - 同步将失败，或者更糟糕的是，它不会并且数据将被损坏。
拥有一个复制服务器，在其中从主服务器添加“删除旧记录”，并且仅在离线服务器上执行插入/更新怎么样？
这将使您既可以拥有所有数据，又可以使实时数据更小。
First, this sound like a task for PL-SQL. The non modular code issue can be enforced, and using PL-SQL will give you better results and easier to be written.
As for the concept itself, any solution you`ll do will have a problem if the schema will be updated - the sync will fails, or worse, it won't and the data will be corrupted.
What about having a replication server where you add a "delete old records" from the main server, and only perform the insert/update on the offline server?
This will allow you to both have all the data and keep the live one smaller.
无论您“做什么”，都需要手工完成。
淘汰 RDBMS 中的数据充满危险。因为您通常不能只归档单个表。您还需要归档它的所有依赖表。
然后是架构更改问题。与其说是让您的存档与不断发展的模式保持同步，不如说是让您的工具与过时的模式保持同步。这并不是说您可以将当前的应用程序指向“旧数据”并期望它一定能工作。让您的应用程序与当前数据保持同步已经够困难的了，更不用说让它对旧数据表现得合理了。
如果您要选择数据的子集，那么手动编写选择和插入语句、确保完整性、检查值等比依赖某些人为的工具更安全，实际上也更容易。乍一看似乎很困难，但实际上很乏味。
但一旦完成，您将可以更好地控制导出和合并数据的内容和方式。
用 PL/SQL 编写它很聪明，因为这是一个数据库操作。为什么要将所有数据从服务器中拖出来只是为了将其重新塞回服务器中。当这一切都说完了之后，PL/SQL 的东西可能会有更好的整体性能。
至于确保模块化、缩进等，嗯，这就是棒球棒被发明的原因。
However you "do it", it will need to be done by hand.
Retiring data in a RDBMS is fraught with peril. Because you typically can't just archive a single table. You need to archive all of it's dependent tables as well.
Then there's the schema change issue. Not so much keeping your archive in sync with you evolving schema, but keeping your tools in sync with obsolete schemas. It's not like you can point your current applications at the "old data" and expect it to necessarily work. Hard enough to keep your apps up to date with current data, much less having it behave reasonably with old data.
If you're doing select subsets of your data, it's just simply safer, and actually easier, to craft the select and insert statements by hand, ensuring integrity, checking values, etc. than to rely on some contrived tool. It may seem arduous up front, but it's really just tedious.
But once done, you'll have much more control over what and how data is being exported and merged.
Writing it in PL/SQL is smart simply because this is a database operation. Why drag all of the data out of the server just to stuff it back in to it. The PL/SQL stuff will likely have better overall performance when this is all said and done.
As for ensuring modularity, indention, etc., well, that's why baseball bats were invented.
你说这是一个数据仓库。您使用分区吗？如果是这样，分区方案是否标识了您要归档的行？如果两个问题的答案都是“是”，则 分区交换可能是您正在寻找的功能。
You say this is a data warehouse. Are you using partitioning? If so, does the partitioning scheme identify the rows you want to archive? If the answer to both questions is "yes" then partition exchange could be the feature you're searching for.
也许我没有正确阅读要求，但简单的就
足够了吗？如果 dest_table 已经存在，则先删除它？
Maybe I'm not reading the requirements correctly but wouldn't a simple
suffice? with a drop first on the dest_table if it already exists?