Working every single day on the large Oracle production database (10g), we observed that procedures like
- granting an user read rights on the table
- developing a trigger on the table
lock that table, and invalidate all of the cursors insisting onto it.
It has tremendous effects when the table is large (> 20 million rows) and several customers are focusing on it.
My real question is: exactly why is Oracle securing the table (we're not changing its structure, in the end, but simply giving an user the permission to see it) and why must it invalidate the cursors?
It is possible to method to make actions like individuals "much softer"?
Thanks ahead of time.
Alternative question: it is possible to method to know the number of cursors are open on the specific table, to be able to minimize the impact invalidation is wearing that object?
Maybe granting privileges to groups once, after which later on granting membership privileges to customers, rather than direct table privileges. Pretty bare answer I understand, however i obtain the impression guess what happens you do and may determine the particulars.
I can tell how adding a trigger would need a lock and only invalidating the cursors or waiting for the open ones to shut, a minimum of when the cursors may potentially be utilized to have an action that will require execution from the new trigger.
It's difficult to determine grounds why granting read rights might have similar needs, most likely only a side-effect from the implementation. MJB's answer appears just like a decent way to cope with that (and in several cases is a great practice anyway, simplifies administration of access rights).
Getting rid of invalidations according to grants or loans:
Create xxx_READONLY roles in which the xxx is a few properly significant value, and grant choose use of all of the appropriate tables towards the role, and add the role towards the customers once they need them.
Getting rid of DDL locks according to trigger creation:
Before I truly checked, trigger were construed code, whereas methods and packages were put together code. Therefore, carrying out complicated logic in triggers is usually frowned upon. It's possible to invoke a process or package method within triggers, and getting your trigger logic be exemplified inside a procedure/package may mitigate or get rid of the DDL locks from the base tables.
The particular groups factor from MJB will be the best product around towards the grant problem, as well as for "trigger" issues I would suggest separating the company logic so that it works something that an enchanting "trigger" would do, especially on the 20 mil+ row table.
1) My question granting read use of a table could be the reason for granting read straight to the table and never developing a role, granting continue reading the role towards the table, then granting (or getting rid of) the role towards the customers? This can take away the securing table problem on grants or loans.
2) Oracle will lock the table on developing a trigger since the trigger can transform the table when installed. All DDL will lock the table, developing a transaction, to understand exactly if this can engage the trigger (or any other changes). I suspect this is especially true for that grants or loans.
If you're constantly adding/getting rid of triggers in the table, I'd take away the code you're altering in the trigger and set it right into a separate PL/SQL procedure. Then update the process when needed. This makes the trigger being invalid (and require recompiling) that is done instantly.
I sure there's a method to discover which cursors are opened up against confirmed table, while using v$ sights within the Oracle data dictionary.