General Information
Library Note
Morgan's Library Page Header
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
SQL Plan Directives internal support
AUTHID
DEFINER
Dependencies
DBA_SQL_PLAN_DIRECTIVES
DBMS_STATS
OPT_FINDING_OBJ$
DBA_SQL_PLAN_DIR_OBJECTS
DBMS_STATS_INTERNAL
PLITBLM
DBMS_ASSERT
DBMS_SYS_ERROR
V$FIXED_TABLE
DBMS_AUTO_INDEX_INTERNAL
OBJ$
_BASE_OPT_FINDING_OBJ_COL
DBMS_LOB
OPT_DIRECTIVE$
_user_stat
DBMS_SPD
OPT_FINDING$
_user_stat_varray
DBMS_SPD_LIB
Documented
No
First Available
Not known
Security Model
Owned by SYS with no privileges granted
Source
{ORACLE_HOME}/rdbms/admin/prvsspdi.plb
Subprograms
ALTER_SPD
Alters a SQL Plan Directive
dbms_spd_internal.alter_spd(
dirid IN NUMBER,
state IN NUMBER,
auto_drop IN BOOLEAN,,
enabled IN BOOLEAN,,
operation IN NUMBER);
TBD
FLUSH_SPD_CALLOUT
Undocumented
dbms_spd_internal.flush_spd_callout;
exec dbms_spd_internal.flush_spd_callout ;
GET_SPD_TEXT
Returns SQL Plan Directive text
dbms_spd_internal.get_spd_text(dirid IN NUMBER) RETURN CLOB;
-- source: catspd.sql
SELECT d.dir_id, d.type, d.enabled,
CASE when d.internal_state = 'HAS_STATS' or d.redundant = 'YES' THEN 'SUPERSEDED'
WHEN d.internal_state in ('NEW', 'MISSING_STATS', 'PERMANENT') THEN 'USABLE'
ELSE 'UNKNOWN'
END CASE,
d.auto_drop, f.reason, d.created, d.last_modified, d.last_used,
xmltype('<spd_note>' || '<internal_state>' || d.internal_state || '</internal_state>'
|| '<redundant>' || d.redundant || '</redundant>' || '<spd_text>' ||
sys.dbms_spd_internal.get_spd_text (d.dir_id) || '</spd_text>' || '</spd_note>') notes
FROM sys."_BASE_OPT_DIRECTIVE" d, sys."_BASE_OPT_FINDING" f
WHERE d.f_id = f.f_id;
GET_VEC_SET_IDS
Undocumented
dbms_spd_internal.get_vec_set_ids(
vec IN RAW,
vec_size IN BINARY_INTEGER)
RETURN sys.dbms_spd_internal.num_tab;
-- source: catspd.sql
SELECT
f.f_id,
f.f_obj#,
f.obj_type,
f.notes,
c.column_value
FROM
sys."_BASE_OPT_FINDING_OBJ" f,
TABLE(sys.dbms_spd_internal.get_vec_set_ids (f.col_list, f.cvec_size)) c
WHERE f.col_list IS NOT NULL;
IMPORT_SPD_CALLOUT
Undocumented
dbms_spd_internal.import_spd_callout(
dirs IN sys.dbms_spd_internal.dirtab,
stgtab IN VARCHAR2)
RETURN BINARY_INTEGER;
TBD
INIT_SCRATCH_TAB
Undocumented
dbms_spd_internal.init_scratch_tab;
exec dbms_spd_internal.init_scratch_tab ;
PL/SQL procedure successfully completed.
INSERT_OBJECT
Undocumented
dbms_spd_internal.insert_object(
ownu IN VARCHAR2,
tabu IN VARCHAR2);
TBD
INSERT_OBJECT_FOR_DP
Undocumented
dbms_spd_internal.insert_object_for_dp(
objlist_tabf IN VARCHAR2,
operation IN NUMBER);
TBD
REMAP_SPD
Undocumented
dbms_spd_internal.remap_spd(
stattabq IN VARCHAR2,
src_ownu IN VARCHAR2,
tgt_ownu IN VARCHAR2,
src_tabu IN VARCHAR2,
tgt_tabu IN VARCHAR2)
RETURN NUMBER;
TBD
UB8_TO_SQLID
Undocumented
dbms_spd_internal.ub8_to_sqlid(sqlidnum IN NUMBER) RETURN VARCHAR2;
-- source: catspd.sql
SELECT
d.dir_id, NULL, dbms_spd_internal.ub8_to_sqlid (fo.f_obj#), NULL,
'SQL STATEMENT', NULL, NULL
FROM sys."_BASE_OPT_DIRECTIVE" d, sys."_BASE_OPT_FINDING_OBJ" fo
WHERE d.f_id = fo.f_id and d.type = 'DYNAMIC_SAMPLING_RESULT'
AND fo.obj_type = 4;