Oracle授予让步和副作用
每天在大型 Oracle 生产数据库 (10g) 上工作时，我们注意到诸如
- 授予用户对表的读取权限、
- 在表上创建触发器之类的
操作会锁定该表，并使所有坚持该表的游标无效。
如果表很大（> 2000 万行）并且许多用户正在处理它，这会产生巨大的后果。
我的问题是：为什么 Oracle 锁定表（毕竟我们没有改变它的结构，只是授予用户读取它的权限）以及为什么它需要使游标无效？
有没有办法让这样的动作变得“更柔和”？
提前致谢。
替代问题：有没有办法知道特定表上打开了多少个游标，以便最大限度地减少失效对该对象的影响？
Working every day on a large Oracle production database (10g), we noticed that operations like
- granting an user read privileges on a table
- creating a trigger on a table
lock that table, and invalidate all the cursors insisting on it.
This has tremendous consequences if the table is big (> 20 million rows) and many users are working on it.
My question is: why is Oracle locking the table (we are not altering its structure, after all, but just giving an user the permission to read it) and why does it need to invalidate the cursors?
Is there a way to make actions like those "softer"?
Thanks in advance.
Alternative question: is there a way to know how many cursors are open on a specific table, in order to minimize the impact invalidation has on that object?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
也许一次向组授予权限，然后在将来向用户授予成员资格，而不是直接表权限。我知道答案很简单，但我的印象是你知道自己在做什么并且可以弄清楚细节。
Maybe granting rights to groups one time, and then in the future granting membership rights to users, instead of direct table rights. Pretty bare answer I know, but I get the impression you know what you are doing and can figure out the details.
消除基于授权的失效：
创建 xxx_READONLY 角色，其中 xxx 是一些适当有意义的值，并向该角色授予对所有适当表的选择访问权限，并在用户需要时将该角色添加到用户。
消除基于触发器创建的 DDL 锁：
上次我真正检查过，触发器是解释代码，而过程和包是编译代码。因此，在触发器中执行复杂的逻辑通常是不受欢迎的。人们可以在触发器中调用过程或包方法，并且将触发器逻辑封装在过程/包中可以减轻或消除针对基表的 DDL 锁。
Eliminating invalidations based on grants:
Create xxx_READONLY roles where the xxx is some appropriately meaningful value, and grant select access to all the appropriate tables to the role, and add the role to the users when they need them.
Eliminating DDL locks based on trigger creation:
Last time I really checked, trigger were interpreted code, whereas procedures and packages were compiled code. Therefore, performing complicated logic in triggers is generally frowned upon. One can invoke a procedure or package method within triggers, and having your trigger logic be encapsulated in a procedure/package may mitigate or eliminate the DDL locks against the base tables.
我可以看到添加触发器将需要锁定，并且使游标无效或等待所有打开的游标关闭，至少如果游标可能用于需要执行新触发器的操作的话。
很难看出为什么授予读取权限会有类似的要求，可能只是实现的副作用。 MJB 的答案似乎是解决这个问题的一个不错的方法（并且在很多情况下无论如何都是一个很好的做法，简化了访问权限的管理）。
I can see how adding a trigger would require a lock and either invalidating the cursors or waiting for all the open ones to close, at least if the cursors could potentially be used for an action that would require execution of the new trigger.
It's hard to see a reason why granting read privileges would have similar requirements, probably just a side-effect of the implementation. MJB's answer seems like a decent way to deal with that (and in lots of cases is a good practice anyway, simplifies administration of access privileges).
无法帮助解决第一个问题，但对于替代方案，我在此处找到了一些有用的命令和此处
Can't help with the first question, but for the alternative I found a couple useful commands here and here
MJB 的团体事务将是解决拨款问题的最佳解决方案，对于“触发”问题，我建议分离业务逻辑，以便它执行神奇“触发”会执行的任何操作，尤其是在超过 2000 万行的表上。
The groups thing from MJB would be the best work around to the grant issue, and for "trigger" issues I would recommend separating the business logic such that it performs anything that a magical "trigger" would do, especially on a 20 mil+ row table.
1）我的问题是，授予对表的读取访问权限是为什么要直接授予对表的读取权限，而不是创建角色，授予对表的角色读取权限，然后向用户授予（或删除）角色？这将消除授权上的锁定表问题。
2) Oracle 将在创建触发器时锁定表，因为触发器在安装时可以更改表。所有 DDL 都会锁定表，创建事务，以准确了解何时可以触发触发器（或其他更改）。我怀疑补助金也是如此。
如果您不断地从表中添加/删除触发器，我会从触发器中删除您要更改的代码，并将其放入单独的 PL/SQL 过程中。然后根据需要更新程序。这将导致触发器无效（并需要重新编译），这是自动完成的。
我确信有一种方法可以使用 Oracle 数据字典中的 v$ 视图来确定针对给定表打开了哪些游标。
1) My question granting read access to a table would be why are you granting read directly to the table and not creating a role, granting read on the role to the table, then granting (or removing) the role to the users? This will remove the locking table problem on grants.
2) Oracle will lock the table on creating a trigger because the trigger can alter the table when installed. All DDL will lock the table, creating a transaction, to know exactly when it can engage the trigger (or other changes). I suspect this is also true for the grants.
If you are continually adding/removing triggers from the table, I would remove the code you are changing from the trigger and put it into a separate PL/SQL procedure. Then update the procedure as needed. This will cause the trigger to become invalid (and require recompiling) which is done automatically.
I sure there is a way to figure out which cursors are opened against a given table, using the v$ views in the Oracle data dictionary.