Global PL/SQL Cache Functions

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

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

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

Example:

This function does not return the invalidation result. You can query the invalidation result in the gs_glc_memory_detail view. If the object is not invalidated, you can find it in valid state in the view. If the object is invalidated, there is no such state.

invalidate_plsql_object belongs to the pg_catalog schema. It can be called even if no schema is specified.

-- If the cache information of function f3 is displayed in the view, the function is valid.
gaussdb=# SELECT * FROM gs_glc_memory_detail WHERE type='func' or type='pkg';
 contextname | database | schema  | type | status  |        location        |   env    | usedsize
-------------+----------+---------+------+---------+------------------------+----------+----------
 pkg1        | testdb | public  | pkg  | valid   | in_global_hash_table   |        0 |   184176
 f3          | testdb | public  | func | valid   | in_global_hash_table   |        0 |    47584
-- To invalidate function f3, specify the schema, function name, and type. Then, query the view again, the status of f3 is not valid.
gaussdb=# SELECT invalidate_plsql_object('public','f3','function');
 invalidate_plsql_object
-------------------------
(1 row)
-- To invalidate a package, set the parameters as follows:
gaussdb=# call pg_catalog.invalidate_plsql_object('public','pkg1','package');
 invalidate_plsql_object
-------------------------
(1 row)

-- If there is no input parameter, all cache objects will be invalidated.
gaussdb=# SELECT invalidate_plsql_object();
 invalidate_plsql_object
-------------------------
(1 row)