查找行删除的 PL/SQL 源
我在 10g 数据库中继承了一些令人讨厌的 PL/SQL 代码，该代码不正确地从特定表中删除行。
我知道正在发生删除，因为我设置了一个触发器，用于在删除行时记录行详细信息。
显然，简单的答案是“搜索“删除自”。但是，所涉及的过程之一包含 18 页 PL/SQL，并且该过程有大约 25 个参数。代码中的删除通过一些非常详细的方式指定要删除的行。 语句
Oracle 中是否有工具可以帮助我查明导致删除的
？ ，但我不认为存在这种情况，
有人告诉我打开跟踪可能会有所帮助，但这也可能是一项相当大的练习，具体取决于有多少跟踪输出，
我还想到也许锁定表并查看在哪里 。抛出错误，但鉴于代码无疑在删除之前执行了大量插入，我不知道该错误是否有任何帮助
。
I've inherited some nasty PL/SQL code in a 10g database which is deleting rows from a particular table improperly.
I know the deletion is occurring because I've set up a trigger that records the row details when a row is deleted.
Obviously the simple answer would be "search for "delete from." However, just one of the procedures involved contains 18 pages of PL/SQL and the procedure has around 25 parameters. The deletes in the code specify what rows to delete via some very complex joins which involve many parameters. It's nasty, nasty code that will take some time to debug.
Are there tools in Oracle which might help me pinpoint the statement causing the delete?
What would be perfect is a trigger that records a stack trace on deletion, but I don't think that exists.
I've been told that turning on trace may help, but it may also be quite an exercise depending on how much trace output there is.
I also thought of perhaps locking the table and seeing where an error is thrown, but given that the code undoubtedly does lots of inserts before the deletion, I don't know the error would be any help.
All thoughts appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
听起来您正在处理大型 ETL 流程？我可能会开始这样做：
将源数据的（非常）小子集复制到 QA 或开发环境中，包括我知道将被删除的几行，以及我知道不会删除的几行（可能是来自前一天备份的数据）。
使用 PL/SQL 调试器运行代码，并在每个 SQL“删除”语句处设置断点。每次“删除”发生后，检查您的行是否仍然存在。使用像 TOAD 这样的 IDE 可以更容易地做到这一点，但也可以仅使用 Oracle 技术来完成。请参阅http://www.adp-gmbh.ch/ora/plsql/debug。 html。
正如您提到的，还有 PL/SQL 跟踪，它对于识别较小代码片段中的此类问题很有用。这些文件变得非常大非常快，因此像以前一样使用一小部分数据进行跟踪可能是明智的。
有关异常堆栈跟踪，请查看此处 - http://paranoid-engineering.blogspot.com/2008/07/oracle-exception-handling-stack-trace.html
您也许可以添加一些额外的代码来处理异常，然后“锁定”表以生成“删除”异常
。您可能必须在每个“DELETE”sql 语句之后求助于旧的备用数据库 - DBMS_OUTPUT。
It sounds like you're working with a large ETL process? I would probably start to go about this by:
Copying a (very) small subset of the source data into a QA or development environment, including a few rows I know will be deleted, and a few that I know won't (perhaps data from a day-ago backup).
Run the code using the PL/SQL debugger with breakpoints at each SQL "delete" statement. Check to see if your rows are still there after each "delete" occurs. This is a lot easier to do with an IDE like TOAD, but can be done using just oracle technologies. See http://www.adp-gmbh.ch/ora/plsql/debug.html.
There is also the PL/SQL trace, as you mentioned, which can be useful for identifying issues like this in smaller pieces of code. These files get very big very fast, so it may be wise to do the trace with the small subset of data as before.
For an exception stack trace, take a look here as well - http://paranoid-engineering.blogspot.com/2008/07/oracle-exception-handling-stack-trace.html
You may be able to add some additional code to handle exceptions and then "lock" the table to generate that "delete" exception
You may have to resort to the old standby - DBMS_OUTPUT after each "DELETE" sql statement.
您可能想要考虑在该表上设置审核，至少在您试图弄清楚究竟发生了什么时。 10g具有细粒度审计和DML审计。这是一个有用的链接。
这是一个更完整示例，还显示了详细的输出。
希望有帮助
You might want to look into setting up auditing on that table, at least while you're trying to figure out exactly whats happening. 10g has fine grained auditing and DML auditing. Here's a helpful link.
Here's a more complete example, showing detailed output as well.
Hope it helps
将包规范添加到您的应用程序中，以保留简单的状态变量，例如，
在 PL/SQL 代码中的每次删除之前，将此状态设置为允许您识别删除语句的内容，例如，
您可以读取并保存 app_state 的值。 g_state 在您已有的触发器中。
这允许您监视/记录哪个删除语句删除了哪一行。
Add a package spec to your application that keeps a simple state variable, e.g.
Before each delete in the PL/SQL code, set this state to something that allows you to identify the delete statement, e.g.
You can read and save the value of app_state.g_state in the trigger you already have.
This allows you to monitor/log which delete statement deleted which row.
函数
DBMS_UTILITY.FORMAT_CALL_STACK创建当前执行的堆栈跟踪。您可以从触发器调用它并将结果写入单独的表（在自治事务中）。The function
DBMS_UTILITY.FORMAT_CALL_STACKcreates a stack trace of the current execution. You could call it from your trigger and write the result to a separate table (in an autonomous transaction).