21
DBMS_LIBCACHE

The PL/SQL package DBMS_LIBCACHE prepares the library cache on an Oracle instance by extracting SQL and PL/SQL from a remote instance and compiling this SQL locally without execution. The value of compiling the cache of an instance is to prepare the information the application requires to execute in advance of failover or switchover.

Compiling a shared cursor consists of open, parse, and bind operations, plus the type-checking and execution plan functions performed at the first execution. All of these steps are executed in advance by the package DBMS_LIBCACHE for SELECT statements. The open and parse functions are executed in advance for PL/SQL and DML. For PL/SQL, executing the parse phase has the effect of loading all library cache heaps other than the MCODE.

This chapter discusses the following topics:

• Requirements

• Summary of DBMS_LIBCACHE Subprograms

Requirements

To execute DBMS_LIBCACHE you must directly access the same objects as do SQL statements. You can best accomplish this by utilizing the same user id as the original system on the remote system. When there are multiple schema users, DBMS_LIBCACHE should be called for each. Alternately, DBMS_LIBCACHE may be called with the generic user PARSER. However, this user cannot parse the SQL that uses objects with access granted though roles. This is a standard PL/SQL security limitation.

Summary of DBMS_LIBCACHE Subprograms

Table 21-1 DBMS_SESSION Subprograms

Subprogram	Description
"COMPILE_CURSORS_FROM_REMOTE Procedure"	Extracts SQL in batch from the source instance and compiles the SQL at the target instance.