Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Purpose
Pin and unpin objects in memory
AUTHID
DEFINER
Dependencies
DBMS_GSM_DBADMIN
DBMS_STANDARD
V$SQLAREA
DBMS_OUTPUT
DBMS_UTILITY
X$KGLOB
DBMS_SQLTCB_INTERNAL
V$DB_OBJECT_CACHE
Documented
Yes
First Available
10.1
Security Model
Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE
and GSMADMIN_INTERNAL roles
dbms_shared_pool.markhot(
schema IN VARCHAR2,
objname IN VARCHAR2,
namespace IN NUMBER DEFAULT 1, -- library cache namespace to search
global IN BOOLEAN DEFAULT TRUE); -- If TRUE mark hot on all RAC instances
TBD
Overload 2
dbms_shared_pool.markhot(
hash IN VARCHAR2, -- 16-byte hash value for the object
namespace IN NUMBER DEFAULT 1,
global IN BOOLEAN DEFAULT TRUE);
CREATE OR REPLACE TRIGGER pin_markhot_objects
AFTER STARTUP ON DATABASE
BEGIN
dbms_shared_pool.markhot(hash=>'01630e17906c4f222031266c21b49303',namespace=>0);
dbms_shared_pool.markhot(hash=>'119df082543f104e29cad00ee793c8aa',namespace=>0);
dbms_shared_pool.markhot(hash=>'251d24517d18ee7b2154e091b80e64d2',namespace=>0);
dbms_shared_pool.markhot(hash=>'28104e170c4020b7d6991509b4886443',namespace=>0);
dbms_shared_pool.markhot(hash=>'3362900d064bc7d9a1812303ea49391e',namespace=>0);
END;
/
Purge the named object or particular heap(s) of the object
Overload 1
dbms_shared_pool.purge(
name IN VARCHAR2,
flag IN CHAR DEFAULT 'P',
heaps IN NUMBER DEFAULT 1);
Note: heaps to purge. e.g if heap 0 and heap 6 are to be purged.
1<<0 | 1<<6 => hex 0x41 => decimal 65. so specify heaps=>65.
Default is 1 i.e heap 0 which means the whole object will be purged.
In some versions this may not work unless you set event 5614566 so
I have shown that in this demo. It is not necessary in 11gR1 or above.
conn sys@pdbdev as sysdba
alter session set events '5614566 trace name context forever';
SELECT /* find me */ COUNT(*)
FROM dba_all_tables t, dba_indexes i
WHERE t.table_name = i.table_name;
SELECT address, hash_value, sql_text
FROM v$sqlarea
WHERE sql_text LIKE '%find me%';