Database Reference
In-Depth Information
These packages are cached but are not permanently pinned. If these packages were recompiled then the memory
version of the package would be replaced with the new version. If an attacker wanted to pin the package permanently
so it was kept in memory despite a
CREATE OR REPLACE
statement on that package then they would use this code to
pin it:
execute dbms_shared_pool.keep('DBMS_UTILITY')
So let's modify our select statement to just capture the pinned packages:
SQL> SELECT SUBSTR(owner,1,10) Owner,
2 SUBSTR(type,1,12) Type,
3 SUBSTR(name,1,20) Name,
4 executions,
5 sharable_mem Mem_used,
6 SUBSTR(kept||' ',1,4) "Kept?"
7 FROM v$db_object_cache
8 WHERE type in ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE')
9 and owner in ('SYS','SYSTEM')
10 and executions != 0
11 and kept='YES'
12 ORDER BY name;
/
OWNER TYPE NAME EXECUTIONS MEM_USED Kept
---------- ------------ -------------------- ------------ ------------ ----
SYS PACKAGE BODY DBMS_UTILITY 1,268,666 53,336 YES
SYS PACKAGE DBMS_UTILITY 30 57,328 YES
SQL>
OWNER TYPE NAME EXECUTIONS MEM_USED Kept
---------- ------------ -------------------- ------------ ------------ ----
SYS PACKAGE BODY DBMS_UTILITY 1,268,666 53,336 YES
SYS PACKAGE DBMS_UTILITY 30 57,328 YES
That's great! There is only one pinned package—the one we just pinned. Now let's unpin it:
execute dbms_shared_pool.unkeep('DBMS_UTILITY')
SELECT SUBSTR(owner,1,10) Owner,
SUBSTR(type,1,12) Type,
SUBSTR(name,1,20) Name,
executions,
sharable_mem Mem_used,
SUBSTR(kept||' ',1,4) "Kept?"
FROM v$db_object_cache
WHERE type in ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE')
and owner in ('SYS','SYSTEM')
and executions != 0
and kept='YES'
ORDER BY name;
/
