Which lock/pin mode is required for which type of action?
All DDL operations (For example, when a package is recompiled, when a grant is given on an object, a truncate on a table, etc.)
require a exclusive lock and pin on the object that needs to be processed.
The DDL cursors themselves require a null lock (localisation job in library cache) and an exclusive pin when executing.
All operations that use an objects (e.g. when a view is used, when a procedure is executed, etc.) require a null lock and shared pin on those objects.
This is also applicable for all dependent objects (e.g. view based on another view, package using another package or view).
Query and DML cursors requires a null lock and shared pin when executing.
The locks/pins are held for the duration of the SQL statement, and released at the end of it.
