Oracle DBMS_LIBCACHE
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose Intended for use with RAC, and not part of the default installation, provides mechanism for applications to view and compile cursors in the local library cache using cursors selected from the library cache on a remote instance.
AUTHID DEFINER
Dependencies
DBA_USERS DBMS_STANDARD GV$SQLTEXT_WITH_NEWLINES
DBMS_LIBCACHE$DEF DBMS_SYS_SQL GV$SQL_BIND_METADATA
DBMS_OUTPUT GV$PARAMETER UTL_RAW
DBMS_SQL GV$SQL  
Documented Yes
First Available 2000
Security Model Owned by SYS with EXECUTE granted to PUBLIC.

According to the docs the session executing the code must have the SELECT_CATALOG_ROLE role as well as the ability to ALTER the target schema and a valid database link to the same user in the source schema. So why is execute granted to PUBLIC? I have no idea. I suspect no one at Oracle does either.
Source {ORACLE_HOME}/rdbms/admin/dbmslibc.sql
-- In 12.1.0.2 we wrote the following: Attempting to compile this package using dbmslibc.sql produces the following errors:

98/20 PLS-00201: identifier 'DBMS_LIBCACHE$DEF.DB_LINK' must be declared

160/3 PL/SQL: Declaration ignored

161/19 PLS-00201: identifier 'DBMS_LIBCACHE$DEF.DB_LINK' must be declared

-- you must run catlibc.sql before attempting to install this package.

The database link parameter is mandatory and points to the instance that will be used for extracting the SQL statements. The user must have the role SELECT_ON_CATALOG at the source instance. For improved security, the connection may use a password file or LDAP authentication. The database link is mandatory only for releases with dbms_libcache$def.ACCESS_METHOD = DB_LINK_METHOD.

-- as of 12.2.0.1 things get worse and as this is package has not been maintained since 2001 it will stay in the Library
-- but be sure to test it thoroughly before depending upon any of its functionality


SQL> @?/rdbms/admin/dbmslibc.sql

Warning: Package created with compilation errors.
Synonym created.
Grant succeeded.
Grant succeeded.
SP2-0310: unable to open file "prvtlibc.plb"

-- attempting to compile the two parts separately also fails and results in the following:

SQL> SELECT object_name, object_type, status
  2  FROM cdb_objects
  3  WHERE object_name LIKE '%DBMS_LIBCACHE%';

OBJECT_NAME        OBJECT_TYPE   STATUS
------------------ ------------- -------
DBMS_LIBCACHE      PACKAGE       VALID
DBMS_LIBCACHE      SYNONYM       VALID
DBMS_LIBCACHE      PACKAGE BODY  INVALID
DBMS_LIBCACHE$DEF  PACKAGE       VALID
DBMS_LIBCACHE$DEF  SYNONYM       VALID
Subprograms
 
COMPILE_FROM_REMOTE
Call when the library cache on the source instance is stable, and whenever the target library cache is restarted dbms_libcache.compile_from_remote(
p_db_link                IN dbms_libcache$def.db_link%type,
p_username               IN VARCHAR2 DEFAULT NULL,
p_threshold_executions   IN NATURAL  DEFAULT 3,
p_threshold_sharable_mem IN NATURAL  DEFAULT 1000,
p_parallel_degree        IN NATURAL  DEFAULT 1);
-- prepares the database's library cache
exec dbms_libcache.compile_from_remote('CACHELINK');

-- prepares the library cache for a named schema's entries
exec dbms_libcache.compile_from_remote('CACHELINK', 'UWCLASS');

-- prepares the library cache for a named schema's statements with greater than 10 executions
exec dbms_libcache.compile_from_remote('CACHELINK', 'UWCLASS', 10);

-- as above but additionally specifying a minimum cursor size of 4K
exec dbms_libcache.compile_from_remote('CACHELINK', 'UWCLASS', 10, 4096);
 
COMPILE_FROM_REMOTE_JOB
Called as a standalone job allows for parallel execution of the compilation task dbms_libcache.compile_from_remote_job(
p_db_link     IN     dbms_libcache$def.db_link%TYPE,
p_SQL_tab     IN OUT dbms_libcache$def.sql_tab,
p_lower_bound IN     BINARY_INTEGER,
p_upper_bound IN     BINARY_INTEGER);
TBD

Related Topics
Built-in Functions
Built-in Packages
Database Links
DBMS_LIBCACHE$DEF
RAC
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx