General Information
Library Note
Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com . Request a Workshop for
your organization today.
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;