Oracle | DBMS_SPD

Oracle DBMS_SPD_INTERNAL Version 21c

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	IMPORT_SPD_CALLOUT	INSERT_OBJECT_FOR_DP
FLUSH_SPD_CALLOUT	INIT_SCRATCH_TAB	REMAP_SPD
GET_SPD_TEXT	INSERT_OBJECT	UB8_TO_SQLID
GET_VEC_SET_IDS

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;