Updated on 2025-05-29 GMT+08:00

Global PL/SQL Cache Functions

invalidate_plsql_object(), invalidate_plsql_object(schema, objname, objtype)

Description: Invalidates objects in the global PL/SQL cache. This function is available only when enable_global_plsqlcache is set to on. The current distributed version does not support this function. The user who calls this function must have the SYSADMIN permission.

Parameters: This function is an overloaded function. If there is no input parameter, all global cache objects in all databases are invalidated.

If the parameters schema, objname, and objtype are specified, the specified global cache object in the current database becomes invalid. schema indicates the name of the schema to which the object belongs. objname indicates the object name. objtype indicates the object type. If the object type is a package, the value is package. If the object type is a function or stored procedure, the value is function.

gs_plsql_memory_object_detail(db_oid, obj_oid, obj_type)

Description: Obtains the valid memory usage of packages and functions created during stored procedures. When enable_global_plsqlcache is set to off, this function displays the memory usage of stored procedures in the current session. Cross-session query is not supported. The current distributed version does not support this function. The user who calls this function must have the SYSADMIN permission.

Parameters: This function requires the db_oid, obj_oid, and obj_type input parameters. If the input parameters do not match, an empty line is returned. For details about the parameters, see Table 1 gs_plsql_memory_object_detail parameters. If the db_oid and obj_oid input parameters are set to 0, the valid memory usage of all packages and functions in the cache is queried by default.

Return type: tuple

Table 1 gs_plsql_memory_object_detail parameters

Parameter

Type

Description

Value Range

db_oid

uint32

The valid memory usage of the stored procedure in the database whose OID is db_oid. The default value 0 indicates all database instances in the current cache.

0 to 2^32 – 1.

obj_oid

uint32

OID of the queried object. The default value 0 indicates all packages and functions in the current cache.

0 to 2^32 – 1.

obj_type

text

Type of the queried object. The target object is a package or function.

all: queries all object types by default.

pkg: queries the valid memory usage of packages that meet the conditions.

func: queries the valid memory usage of functions that meet the conditions.

func_in_pkg: queries the valid memory usage of functions in the package that meets the conditions.

The gs_plsql_memory_object_detail function displays the valid memory usage that meets the query conditions in the database, as described in Table 2 gs_plsql_memory_object_detail return values.

Table 2 gs_plsql_memory_object_detail return values

Name

Type

Description

object_oid

uint32

OID of the queried memory object.

context_name

text

Name of a memory object.

item

text

Name of the queried memory object item.

searchpath

text

Schema and other environment variables of the compilation product accessed by the memory object.

guc

uint64

Environment parameter when an object is created, that is, the value of behavior_compat_flags.

file

text

File where the memory object is created.

line

uint32

Number of lines in the file where the memory object is created.

size

uint32

Size of a memory object.

db_oid

uint32

OID of the database where the queried memory object is located.