Oracle DBMS_LIBCACHE Demos

Oracle DBMS_LIBCACHE Version 11.2.0.3

General Information

Purpose

This package provides a mechanism for applications to view and compile cursors in the local library cache using cursors selected from the library cache on a remote instance. This package is intended for use with RAC and is not part of the default installation.

Note

-- 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.

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	V$SQL2

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.

Source

{ORACLE_HOME}/rdbms/admin/dbmslibc.sql

Subprograms

COMPILE_FROM_REMOTE

COMPILE_FROM_REMOTE_JOB

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