This is interesting. I have always thought oracle latches were kind of mysterious, for lack of a technical term. Don’t get me wrong, I understand semaphores (I have programmed with these before), and “test and set” operations on certain types of computer processors. However, oracle’s rules are so intertwined that I never have a clear answer on when which latch is taken.<\/p>\n
However, I can definitely say that when you grant privileges on an object in a production database in the middle of a busy day, you could have problems.\u00a0 You probably know where this is going.<\/p>\n
Today, an application installer was installing some packages in a new schema.\u00a0 He was granting select\/insert\/update\/delete on several tables in the data schema. We started seeing a large number of “library cache lock” wait events for users in another schema that were accessing the tables on which select privileges were being granted to the new schema.<\/p>\n
It turns out that this operation will actually stamp the LAST_DDL_TIME column of DBA_OBJECTS.\u00a0 It doesn’t invalidate the object in the STATUS column of DBA_OBJECTS, but there is a latch that existing sessions request as a result of this “invalidation”.<\/p>\n
See Metalink note 444560.1 for more information.<\/p>\n
“Be very careful with altering, granting or revoking privileges on database objects that frequently used stored PL\/SQL is dependent on. In fact, resolving this issue mostly depends on application project and system maintenance practices.”<\/p>\n","protected":false},"excerpt":{"rendered":"
This is interesting. I have always thought oracle latches were kind of mysterious, for lack of a technical term. Don’t get me wrong, I understand semaphores (I have programmed with these before), and “test and set” operations on certain types…<\/p>\n