删除表分区以避免错误 ORA-00054
在这种情况下我需要你的意见。我会尝试解释一下这个场景。我有一个 Windows 服务，定期将数据存储在 Oracle 数据库中。存储此数据的表按日期分区（间隔-日期范围分区）。该数据库还有一个 dbms_scheduler 作业，除其他操作外，它还可以截断和删除旧分区。
这种方法已经工作了一段时间，但最近我遇到了 ORA-00054 错误。经过一番调查后，通过以下步骤重现了该错误：
- 打开一个 sqlplus 会话，禁用自动提交，然后在 分区表，不提交更改；
- 打开另一个 sqlplus 会话并截断/删除旧分区（DDL 如果我没记错的话，操作会自动提交）。我们 然后会得到 ORA-00054 错误。
有一些限制值得一提：
- 我没有数据库的 DBA 访问权限；
- 这是一个遗留应用程序，完整的重构不是 可行的;
那么，您认为有什么方法可以删除这些旧分区，而不会有遇到 ORA-00054 错误的风险，并且无需 DBA 的干预？我可以直接删除数据，但是空分区的数量每天都会增加。
非常感谢。
I need your opinion in this situation. I’ll try to explain the scenario. I have a Windows service that stores data in an Oracle database periodically. The table where this data is being stored is partitioned by date (Interval-Date Range Partitioning). The database also has a dbms_scheduler job that, among other operations, truncates and drops older partitions.
This approach has been working for some time, but recently I had an ORA-00054 error. After some investigation, the error was reproduced with the following steps:
- Open one sqlplus session, disable auto-commit, and insert data in the
partitioned table, without committing the changes; - Open another sqlplus session and truncate/drop an old partition (DDL
operations are automatically committed, if I’m not mistaken). We
will then get the ORA-00054 error.
There are some constraints worthy to be mentioned:
- I don’t have DBA access to the database;
- This is a legacy application and a complete refactoring isn’t
feasible;
So, in your opinion, is there any way of dropping these old partitions, without the risk of running into an ORA-00054 error and without the intervention of the DBA? I can just delete the data, but the number of empty partitions will grow everyday.
Many thanks in advance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
此错误意味着某人（或某物）正在处理您尝试删除的分区中的数据。也就是说，锁是在分区级别授予的。如果没有人使用该分区，您的工作可能会放弃它。
现在您说这是一个遗留应用程序，您不想或不能重构它。很公平。但是，如果您有一个进程正在切换其他进程正在使用的数据，那么显然有些事情是不对的。我不同意 @tbone 的建议，即循环直到锁被释放：你不能仅仅删除某人正在使用的数据来确定为什么他们仍在使用他们显然不应该使用的数据。
因此，第一步是找出锁定会话正在做什么。为什么他们还在修改你的后台工作想要退休的数据？这是一个脚本，它将帮助您确定哪个会话拥有锁定 。
除非您“没有数据库的 DBA 访问权限”。嗯嗯，那是一卷卷发。基本上这不是一个没有 DBA 访问权限就可以解决的问题。
看来您有几个问题需要处理。对您来说不幸的是，它们是政治和建筑而不是技术，我们无能为力来进一步帮助您。
This error means somebody (or something) is working with the data in the partition you are trying to drop. That is, the lock is granted at the partition level. If nobody was using the partition your job could drop it.
Now you say this is a legacy app and you don't want to, or can't, refactor it. Fair enough. But there is clearly something not right if you have a process which is zapping data that some other process is using. I don't agree with @tbone's suggestion of just looping until the lock is released: you can't just get rid of data which somebody is using with establishing why they are still working with data that they apparently should not be using.
So, the first step is to find out what the locking session is doing. Why are they still amending this data your background job wants to retire? Here's a script which will help you establish which session has the lock.
Except that you "don't have DBA access to the database". Hmmm, that's a curly one. Basically this is not a problem which can be resolved without DBA access.
It seems like you have several issues to deal with. Unfortunately for you, they are political and architectural rather than technical, and there's not much we can do to help you further.
如何将 truncate 或 drop 包装在 pl/sql 中，在循环中尝试该操作，在尝试之间等待 x 秒，以获得最大尝试次数。然后使用 dbms_scheduler 调用该过程/函数。
How about wrapping the truncate or drop in pl/sql that tries the operation in a loop, waiting x seconds between tries, for a max num of tries. Then use dbms_scheduler to call that procedure/function.
也许这个可以提供帮助。看来和你描述的问题是同一个问题。
（如果可以的话，请忽略漫画sans）：）
Maybe this can help. Seems to be the same issue as the one that you discribe.
(ignore the comic sans, if you can) :)