Oracle | DBMS_SPD

Oracle DBMS_SPD_INTERNAL Version 21c

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	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;