Oracle DBMS_STATS_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 Internal support package for CBO optimizer stats collection in conjunction with DBMS_STATS.

Because this package is completely undocumented a few small liberties have been in the object description column based on either the object's name or the results of working with the object itself. Some errors should be assumed and corrections are welcomed.
AUTHID DEFINER
Data Types
AGGCOLREC COLHISTTAB NUMTAB
AGGCOLTAB COLREC TABREC
CHREC COLTAB TABTAB
CHTAB (table of CHREC) IDENTAB T_CACHESTATIN
CLOBTAB INDREC T_CACHESTATOUT
COLHISTREC INDTAB T_CACHESTATOUTSET

Speculations: TOBJN is Table Object Number, ICOLN is Internal Column Number
br> TYPE aggcolrec IS RECORD(
intcol# NUMBER
nnv     NUMBER
nmin    NUMBER
nmax    NUMBER
minval  RAW
maxval  RAW
acl     NUMBER
ndv     NUMBER);

TYPE aggcoltab IS TABLE OF aggcolrec;

TYPE numtab IS ...

TYPE pararray IS ...

TYPE reportingcontext IS ...

TYPE varchartab IS ...

sys.ctab IS TABLE OF sys.crec;
Dependencies SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_STATS_INTERNAL'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_STATS_INTERNAL';

-- returns 199 objects
DBA_OPTSTAT_OPERATION_TASKS DBA_AUTOTASK_OPERATION V_$STATS_ADVISOR_RECS
DBA_AUTOTASK_JOB_HISTORY V_$STATS_ADVISOR_FINDINGS V_$STATS_ADVISOR_RULES
Documented No
Exceptions
Error Code Reason
ORA-02149  Specified partition does not exist
ORA-27475 unknown job <job_name_string>
First Available 8.1.5
Security Model Owned by sys with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/prvtstas.plb
{ORACLE_HOME}/rdbms/admin/prvtstai.plb
Subprograms
ADDOBJTONUMTAB GET_ADVISOR_REC_ID IS_TABLE_EMPTY
ADD_PARAM GET_ADVISOR_REC_NAME IS_TEMP_TAB
ADD_SUBSCRIBER GET_ADVISOR_REPORT_CBK IS_URGENT_ERROR
ADVISOR_CHECK_OBJ_FILTER_VALID GET_ADVISOR_RTL_NAME JOB_CLASS_EXISTS
ADVISOR_CHECK_RESUME GET_ADVISOR_RULE_DESC MANAGE_COL_TRACKING_LEVEL_JOB
ADVISOR_CLEANUP_OBJ_FILTER GET_ADVISOR_RULE_ID MERGE_HLL_FROM_DISKS
ADVISOR_CLEAR_DIRECTIVES GET_ADVISOR_RULE_NAME MIN_NUM
ADVISOR_CLEAR_FILTERS GET_ADVISOR_RULE_OBJ_ID MONITORING_MAN_SIGNAL_SAFEMODE
ADVISOR_EXPAND_DIRECTIVES GET_ADVISOR_TASK_ID NOTES_STRING_TO_XML
ADVISOR_GET_MAX_OBJ_ID GET_ADVISOR_TASK_NAME OBJECT_EXISTS
ADVISOR_GET_OBJECT_NUMBER GET_ADVISOR_TASK_OWNER OPEN_ADVISOR_ACTION_OBJ_CUR
ADVISOR_GET_OBJ_DIR GET_ADVISOR_TASK_PRIVILEGE OPEN_ADVISOR_FINDING_OBJ_CUR
ADVISOR_GET_OPR_DIR GET_AGG_CHT_WITH_DISK OPEN_ALL_EMPTY_OBJS_CUR
ADVISOR_GET_RULE_DIR GET_AGG_CLIST_WITH_DISK OPEN_ALL_OBJECTS_CUR
ADVISOR_GET_UNIQUE_DIR_NAME GET_AGG_COLSTATS OPEN_ALL_STALE_OBJS_CUR
ADVISOR_IS_SYSTEM_RULE GET_AGG_NDV OPEN_COLSTATS_DICT_CUR
ADVISOR_REPORT_HEADER GET_ALL_ADVISOR_RULE_IDS OPEN_COLSTATS_HIST_CUR
ADVISOR_REPORT_SUMMARY GET_APPROX_NDV_ALGORITHM OPEN_CUR_ALL_LOCAL_INDEX_PARTS
ADVISOR_RULE_HAS_FINDING GET_BLKCNT OPEN_EXTN_HIST_CUR
ADVISOR_SETUP_OBJ_FILTER GET_CALIB_OPID_CALLOUT OPEN_FXT_COLSTATS_HIST_CUR
ADVISOR_SKIP_CHECKED_RULES GET_COLNAME OPEN_FXT_STATS_DICT_CUR
ADVISOR_TASK_EXISTS GET_COLNUM OPEN_FXT_STATS_HIST_CUR
AGGREGATE_INDEX_STATS GET_COLTYPE OPEN_GET_IND_PARTS_CUR
AGGREGATE_INDSTATS GET_COL_EXPR OPEN_GET_IND_SUBPARTS_CUR
AGG_PARTITION_ROWCNT GET_COUNT_OF_COLS OPEN_GET_OPR_PARAMS
AGG_PDB_SHARD_TSTATS GET_CURRENT_JOB_NAME OPEN_GET_TARGET_PARTITIONS_CUR
ALTER_PARAMETER_VAL GET_CURRENT_SESSION_ID OPEN_GET_TARGET_SUBPARTS_CUR
APPR_NDV_ENABLED GET_DB_BLOCK_SIZE OPEN_INDEX_CUR
BITCLR GET_DEFAULT$ OPEN_OPERATIONS_CUR
BITOR GET_DEFAULT_ADVISOR_EXECUTION OPEN_TAB_STATS_DICT_CUR
CAN_DERIVE_COL_HISTOGRAM GET_DEFAULT_DOP OPEN_TAB_STATS_HIST_CUR
CCT GET_EXPORT_SQL_CLAUSES PAD_CHR
CHECKAUTOJOB GET_EXP_LIST PARSE_HASHVAL
CHECK_ADVISOR_INPUT_VALIDITY GET_FIRST_PART_COL PARSE_INCREMENTAL_STALENESS
CHECK_ADVISOR_OBJ_FILTER GET_FXT_OBJ PART_TYPES
CHECK_ADVISOR_OPR_FILTER GET_FXT_TYP PENDING_STATS_ENABLED
CHECK_ADVISOR_RULE_FILTER GET_HIST_DENSITY POPULATE_OBJ_LIST_TAB
CHECK_ANALYZE_DONE GET_IDENTIFIER POPULATE_TEMP_INSERT
CHECK_AUTO_STATS_ENABLED GET_IDX_TABPART POST_PROCESS_HIGHF_AUTOTASK
CHECK_DOMIDX_PARTITIONS GET_INDEX_BLOCK_COUNT PQFLAGS
CHECK_DOMIDX_PARTS GET_INDEX_LOCK_FLAG PREPARE_METHOD_OPT_TEXT
CHECK_DOMIDX_SUBPARTITIONS GET_INDEX_PART_NAME PREP_INSERT_SYNOPSIS_ONLINE
CHECK_GLOBAL_TAB_STALENESS GET_INDSTATS_DICT PREP_SAVE_CS
CHECK_GRANULARITY GET_IND_COLS PRORATE_GLOBAL_NDV
CHECK_HIST_MISSING GET_IND_COL_INFO PURGE_PENDING_STATS
CHECK_INCREMENTAL_STALENESS GET_IND_SHARE_TYPE PURGE_STATS_AUX
CHECK_MISSING_CSTATS GET_INTCOL PURGE_STAT_TABLE
CHECK_VOLATILE GET_IOT_MAPPING_TABLE PURGE_SYNOPSIS
CHECK_WINDOW_CAPTURE_MODE GET_MBRC PURGE_SYNOPSIS_BY_MMON
CLEAR_MON_COL_USAGE_FLAG GET_MV_ENQUE QOSPSETPGAFLG
COLHASBASESTATS GET_NUM_ROWS RECLAIM_SYNOPSIS_SPACE
COLHASHISTOGRAM GET_NUM_ROWS_AND_BLK_CNT RECORD_OP_FAILURE
COLUMN_EXISTS GET_OBJLIST_TEXT REC_EXTN_DDL
COL_STATS_FROM_LOAD GET_OBJNUM REMAP_INDEX_NAMES
COMPOSE_HASHVAL_CLOB_REC GET_OBJ_NAME_USER_NAME REMOVE_SUBSCRIBER
COMPUTE_DOP GET_OLDEST_HISTORY_TIME REPORTING_MAN_LOG_OP
CONTROL_PARALLEL GET_OSTZHM REPORTING_MAN_LOG_TASK
COUNT_PDB_OR_SHARD GET_OWNER_ID REPORTING_MAN_LOG_TASK_CALLOUT
CREATE_ADVISOR_OBJECT GET_PARAM REPORTING_MAN_UPDATE_TASK
CREATE_TEMP GET_PARAMETER_VAL RESTORE_STATS_OK
DBMS_ASSERT_SIMPLE_SQL_NAME GET_PARAM_DEFAULT REVOKE_CONC_PRIV_FROM_USER
DEBUG_FLAG_IS_SET GET_PARAM_NUMVAL SAME_COL
DECODE_GRANULARITY GET_PARAM_PROP SAME_PART
DELETE_CACHE_HISTORY GET_PARTN_LOCK_FLAG SAME_TAB_COLS
DELETE_COL_USAGE GET_PART_COLS SAVE_AS_PENDING_COL_STATS
DELETE_EXPR_STATS GET_PREFS SAVE_AS_PENDING_INDEX_STATS
DELETE_FROM_USTATS GET_PREV_PART SAVE_AS_PENDING_TABLE_STATS
DELETE_PARTITION_SYNOPSIS GET_PROCRATE_ID SCHEMA_EXISTS
DELETE_PROC_RATE_CALLOUT GET_QA_CONTROL SEGMENT_NUMBER_BLOCKS
DELETE_SINGLE_COL_GROUP_USAGE GET_SC_1201 SET_ADVISOR_EXEC_PROPERTIES
DELETE_STATS_OPERATION GET_SC_1202 SET_ANDV_ALGO_INTERNAL_OBSERVE
DELETE_TABLE_STATS_MODEL GET_SC_MAX SET_APPR_NDV
DEL_SESSION_COL_STATS GET_STALE_REASON SET_DEBUG_FLAGS
DERIVE_GLOBAL_HISTOGRAM GET_STAT_EXTENSION SET_INCREMENTAL_INTERNAL_CONTROL
DML_STATTAB_PREFS GET_SYNOPSIS_DEGREE SET_MON_COL_USAGE_FLAG
DML_TABLE_PREFS GET_SYNOPSIS_GROUP_NUM SET_PARAM
DQ GET_TABLE_BLOCK_COUNT SET_PROCESSING_RATE_AUX
DROP_OLD_TEMP GET_TABLE_DEGREE SET_PROCESSING_RATE_CALLOUT
DROP_STATS_MODEL_AUTOTXN GET_TABLE_PREFS SET_QA_CONTROL
DROP_TEMP GET_TABLE_PROPERTY SET_STATS_TARGET_STATUS
DUMP_ALERT_TSES GET_TABLE_STATS_SIMPLE SET_SYS_FLAGS
DUMP_PQ_SESSTAT GET_TAB_NUM_SEGMENTS SET_TEMP_DOP
DUMP_QUERY GET_TAB_PROPERTY SET_TRACE_LEVEL
DUMP_TRACE GET_TAB_PROPERTY_AND_FLAGS SHARDEDCATALOG_OR_ROOT
DUMP_TRACE_TS GET_TAB_SHARE_TYPE SIMILAR_OBJECT_EXISTS
ESCAPE_XML GET_TAB_SHARE_TYPE_VIEW SIMILAR_SCHEMA_EXISTS
EST_USING_NONEXACT_HISTORY GET_USER_NUM STATS_SENSITIVE_TAB
EXECUTE_ADVISOR_AUTO_TASK GRANT_CONC_PRIV_TO_USER STORE_SYSTEM_STATS
EXPORT_COLSTATS_DIRECT HYBRID_HIST_ENABLED SYSAUX_OFFLINE
EXPORT_COL_USAGE IMPORT_COL_USAGE_FOR_DIR TABHASGLOBALSTATS
EXPORT_FXT_COLSTATS_DIRECT IMPORT_MON_INFO TABLE_PREFS_EXISTS
EXTRACT_STR IMPORT_STATS_HISTORY TABPARTSHAVESTATS
FILL_IN_PARAMS IMPORT_SYNOPSES_STATS TAB_STATS_STALE
FILL_IN_PARAMS_WITH_NO_PREFS INCREMENTAL_INTERNAL_ENABLED TEST_GATHER_SQL_STATS
FILL_SYNOPSIS INDEX_MAX_KEYSIZE_OK TEST_MMON_AUTOJOB
FIXQ INDHASGLOBALSTATS TOP_FREQ_HIST_ENABLED
FIX_IND_NAMES INDPARTSHAVESTATS TO_BOOL_TYPE
FIX_TAB_NAMES INIT_ID_MAP_TAB TO_CASCADE_TYPE
FLUSH_CACHE_STATS INIT_OBJ_LIST_TAB TO_DEGREE_TYPE
FORMAT_CACHE_ROWS INIT_STATS_ARG_TAB TO_ESTIMATE_PERCENT_TYPE
GATHER_DATABASE_STATS_MODE INSERT_ADVISOR_FILTER_OBJ TO_STALE_PERCENT_TYPE
GATHER_FXT_STATS_OK INSERT_INTO_USTATS TO_TABLE_CACHED_BLOCKS_TYPE
GATHER_INDEX INSERT_ONLY TRACE
GATHER_PROCESSING_RATE_AUX INS_SESSION_HG_STATS TRACE_ENABLED
GATHER_PROCESSING_RATE_JOB INT_ASSERT TRACE_ERROR
GATHER_SCAN_RATE ISSHARDEDCATALOG TRACE_TS
GATHER_SCAN_RATE_BY_MMON IS_DUPLICATED_OPR TRANSLATE_EV_COLNAME
GATHER_SQL_STATS IS_HADOOP_TABLE TRANSLATE_EV_TO_TBL
GATHER_TABLE_STATS_MODEL IS_INDEX_UNIQUE TRUNCATE_TABLE
GENERATE_OP_ID IS_MV_TABLE_BUSY TRUNCATE_TEMP
GENERATE_PARAMS_XML_INT IS_ORACLE_OWNED UPCASE
GET_ADVISOR_DEFAULT_INCLUDE IS_PARAM_VALUE_DEFAULT UPDATE_ADVISOR_DEFAULT_INCLUDE
GET_ADVISOR_FINDING_DEF_ID IS_PARTITIONED_TAB UPDATE_OP_TERMINATION_STATUS
GET_ADVISOR_FINDING_ID IS_PART_DEFAULT UPDATE_ROOT_OR_COORD_DML
GET_ADVISOR_FINDING_NAME IS_PART_TYP_SAME UPDATE_SYNOPSIS_HEAD
GET_ADVISOR_NUM_FINDINGS IS_QA_MODE UPDATE_SYNOPSIS_PDB_SHARD
GET_ADVISOR_OBJECT_TYPE_ID IS_RES_MAN_ON UPDATE_TARGET_LIST
GET_ADVISOR_OPR_FILTER IS_SENSITIVE_COL UPDATE_TASK_TERMINATION_STATUS
GET_ADVISOR_PARAM_VAL IS_STALE VALIDATE_REALTIME_STATS_PREF
GET_ADVISOR_RAT_ID IS_STALE_IN_CONTAINER VIEW_COLUMN_EXISTS
GET_ADVISOR_REC_CBK IS_STALE_STRICT WRITE_CLOB_TO_FILE
 
ADDOBJTONUMTAB
Undocumented dbms_stats_internal.addObjToNumTab(
numcol IN OUT dbms_stats_internal.numtab,
objnum IN     NUMBER);
TBD
 
ADD_PARAM
Undocumented dbms_stats_internal.add_param(
pname     IN VARCHAR2,
pvalnum   IN NUMBER,
pvalchar  IN VARCHAR2,
updtime   IN TIMESTAMP WITH TIME ZONE,
isdefault IN NUMBER);
TBD
 
ADD_SUBSCRIBER
Undocumented dbms_stats_internal.add_subscriber(queue_subscriber IN sys.aq$_agent);
TBD
 
ADVISOR_CHECK_OBJ_FILTER_VALID
Raises ORA-20001 if the named object is not in the named schema dbms_stats_internal.advisor_check_obj_filter_valid(
ownname IN VARCHAR2,
objname IN VARCHAR2);
exec dbms_stats_internal.advisor_check_obj_filter_valid(USER, 'TAB$');

PL/SQL procedure successfully completed.

exec dbms_stats_internal.advisor_check_obj_filter_valid(USER, 'TAB$$');
BEGIN dbms_stats_internal.advisor_check_obj_filter_valid(USER, 'TAB$$'); END;
*
ERROR at line 1:
ORA-20001: Statistics Advisor: filter: object TAB$$ does not exist for owner SYS
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22019
ORA-06512: at line 1
 
ADVISOR_CHECK_RESUME
Returns true if the identified advisor task has resumed

Returns ORA-01403 if the task is not found.
dbms_stats_internal.advisor_check_resume(task_id IN NUMBER) RETURN BOOLEAN;
BEGIN
  IF NOT dbms_stats_internal.advisor_check_resume(350) THEN
    dbms_output.put_line('Advisor Task ' || TO_CHAR(350) || ' Has Not Resumed');
  END IF;
END;
/
 
ADVISOR_CLEANUP_OBJ_FILTER
Removes a rule (filter) from an advisor job dbms_stats_internal.advisor_cleanup_obj_filter(rule_id IN NUMBER);
exec dbms_stats_internal.advisor_cleanup_obj_filter(9999);

PL/SQL procedure successfully completed.
 
ADVISOR_CLEAR_DIRECTIVES
Clears the directives (rules) for a specific task and operation dbms_stats_internal.advisor_clear_directives(
task_id        IN NUMBER,
operation_type IN VARCHAR2);
TBD
 
ADVISOR_CLEAR_FILTERS
Removes all Advisor filters (rules) dbms_stats_internal.advisor_clear_filters;
exec dbms_stats_internal.advisor_clear_filters;

PL/SQL procedure successfully completed.
 
ADVISOR_EXPAND_DIRECTIVES
Undocumented dbms_stats_internal.advisor_expand_directives(
task_id        IN NUMBER,
operation_type IN VARCHAR2,
privilege      IN NUMBER);
TBD
 
ADVISOR_GET_MAX_OBJ_ID
Undocumented dbms_stats_internal.advisor_get_max_obj_id(task_id IN NUMBER) RETURN NUMBER;
SELECT dbms_stats_internal.advisor_get_max_obj_id(1)
FROM dual;

DBMS_STATS_INTERNAL.ADVISOR_GET_MAX_OBJ_ID(1)
---------------------------------------------
                                          108
 
ADVISOR_GET_OBJECT_NUMBER
Undocumented dbms_stats_internal.advisor_get_object_number(
ownname IN VARCHAR2,
objname IN VARCHAR2)
RETURN dbms_stats_internal.numtab;
TBD
 
ADVISOR_GET_OBJ_DIR
Undocumented dbms_stats_internal.advisor_get_obj_dir(
ownname       IN  VARCHAR2,
objname       IN  VARCHAR2,
rule_name     IN  VARCHAR2,
opr_type      IN  VARCHAR2,
task_id       IN  NUMBER,
exist         OUT BOOLEAN,
include       OUT BOOLEAN,
curr_dir_name OUT VARCHAR2);
TBD
 
ADVISOR_GET_OPR_DIR
Undocumented dbms_stats_internal.advisor_get_opr_dir(
opr_name      IN  VARCHAR2,
opr_notes     IN  VARCHAR2,
rule_name     IN  VARCHAR2,
opr_type      IN  VARCHAR2,
task_id       IN  NUMBER,
exist         OUT BOOLEAN,
include       OUT BOOLEAN,
curr_dir_name OUT VARCHAR2);
TBD
 
ADVISOR_GET_RULE_DIR
Undocumented dbms_stats_internal.advisor_get_rule_dir(
rule_name      IN  VARCHAR2,
operation_type IN  VARCHAR2,
task_id        IN  NUMBER,
exist          OUT BOOLEAN,
include        OUT BOOLEAN,
curr_dir_name  OUT VARCHAR2);
TBD
 
ADVISOR_GET_UNIQUE_DIR_NAME
Undocumented dbms_stats_internal.advisor_get_unique_dir_name(task_id IN NUMBER) RETURN VARCHAR2;
DECLARE
 retVal VARCHAR2(30);
BEGIN
  retVal := dbms_stats_internal.advisor_get_unique_dir_name(350);
  dbms_output.put_line(retVal);
END;
/
2

PL/SQL procedure successfully completed.
 
ADVISOR_IS_SYSTEM_RULE
Undocumented dbms_stats_internal.advisor_is_system_rule(rule_id IN NUMBER) RETURN BOOLEAN;
BEGIN
  IF dbms_stats_internal.advisor_is_system_rule(350) THEN
    dbms_output.put_line('Exists');
  ELSE
    dbms_output.put_line('Does Not Exist');
  END IF;
END;
/
Does Not Exist

PL/SQL procedure successfully completed.
 
ADVISOR_REPORT_HEADER
Undocumented dbms_stats_internal.advisor_report_header(
report_xml     IN OUT XMLTYPE,
task_id        IN     NUMBER,
execution_name IN     VARCHAR2,
level_flags    IN     NUMBER,
privilege      IN     NUMBER);
TBD
 
ADVISOR_REPORT_SUMMARY
Undocumented dbms_stats_internal.advisor_report_summary(
report_xml     IN OUT XMLTYPE,
task_id        IN     NUMBER,
execution_name IN     VARCHAR2,
level_flags    IN     NUMBER,
privilege      IN     NUMBER,
num_findings   IN     NUMBER);
TBD
 
ADVISOR_RULE_HAS_FINDING
Undocumented dbms_stats_internal.advisor_rule_findings(
task_id   IN NUMBER,
exec_name IN VARCHAR2,
rule_id   IN NUMBER);
TBD
 
ADVISOR_SETUP_OBJ_FILTER
Undocumented dbms_stats_internal.advisor_setup_obj_filter(
task_id       IN NUMBER,
rule_id       IN NUMBER,
opr_type      IN VARCHAR2,
ignore_filter IN BOOLEAN);
TBD
 
ADVISOR_SKIP_CHECKED_RULES
Undocumented dbms_stats_internal.advisor_skip_checked_rules(task_id IN NUMBER);
exec dbms_stats_internal.advisor_skip_checked_rules(350);
BEGIN dbms_stats_internal.advisor_skip_checked_rules(350); END;
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 24468
ORA-06512: at line 1
 
ADVISOR_TASK_EXISTS
Determines whether the specified advisor task exists dbms_stats_internal.advisor_task_exists(task_name IN VARCHAR2) RETURN BOOLEAN;
BEGIN
  IF dbms_stats_internal.advisor_task_exists(3) THEN
    dbms_output.put_line('Exists');
  ELSE
    dbms_output.put_line('Does Not Exist');
  END IF;
END;
/
Does Not Exist

PL/SQL procedure successfully completed.
 
AGGREGATE_INDEX_STATS
Undocumented dbms_stats_internal.aggregate_index_stats(
ownname IN VARCHAR2,
indname IN VARCHAR2);
exec dbms_stats_internal.aggregate_index_stats('UWCLASS', 'IX_TEST');
starting SYS.IX_TEST
before check

PL/SQL procedure successfully completed.
 
AGGREGATE_INDSTATS
Undocumented dbms_stats_internal.aggregate_indstats(
iobjn IN     NUMBER,
level IN OUT NUMBER,
nlb   IN OUT NUMBER,
nrw   IN OUT NUMBER,
albk  IN OUT NUMBER,
adbk  IN OUT NUMBER,
clf   IN OUT NUMBER);
TBD
 
AGG_PARTITION_ROWCNT
Undocumented dbms_stats_internal.agg_partition_rowcnt(tab_num IN NUMBER) RETURN NUMBER;
TBD
 
AGG_PDB_SHARD_TSTATS
Undocumented dbms_stats_internal.agg_pdb_shard_tstats(
owner              IN  VARCHAR2,
tab_name           IN  VARCHAR2,
tab_type           IN  NUMBER,
nrows              OUT NUMBER,
nblks              OUT NUMBER,
im_imcu_count      OUT NUMBER,
im_block_count     OUT NUMBER,
pdb_or_shard_count IN  NUMBER);
TBD
 
ALTER_PARAMETER_VAL
Alter the value of a parameter dbms_stats_internal.alter_parameter_val(
pname    IN  VARCHAR2,
pvalchar IN  VARCHAR2,
pvalnum  IN  NUMBER,
session  IN  BOOLEAN,
curpval  OUT VARCHAR2);
TBD
 
APPR_NDV_ENABLED (new 21c)
Undocumented dbms_stats_internal.appr_ndv_enabled RETURN BOOLEAN;
BEGIN
  IF dbms_stats_internal.appr_ndv_enabled THEN
    dbms_output.put_line('Enabled');
  ELSE
    dbms_output.put_line('Disabled');
  END IF;
END;
/
Enabled

PL/SQL procedure successfully completed.
 
BITCLR
Undocumented dbms_stats_internal.bitclr(
v1 IN BINARY_INTEGER,
v2 IN BINARY_INTEGER)
RETURN BINARY_INTEGER;
SELECT dbms_stats_internal.bitclr(3, 2)
FROM dual;

DBMS_STATS_INTERNAL.BITCLR(3,2)
-------------------------------
                              1


SELECT dbms_stats_internal.bitclr(3, 99)
FROM dual;

DBMS_STATS_INTERNAL.BITCLR(3,99)
--------------------------------
                               0
 
BITOR
BITOR function dbms_stats_internal.bitor(
v1 IN BINARY_INTEGER,
v2 IN BINARY_INTEGER)
RETURN BINARY_INTEGER;
SELECT dbms_stats_internal.bitor(1, 0)
FROM dual;

DBMS_STATS_INTERNAL.BITOR(1,0)
------------------------------
                             1


SELECT dbms_stats_internal.bitor(0, 0)
FROM dual;

DBMS_STATS_INTERNAL.BITOR(0,0)
------------------------------
                             0


SELECT dbms_stats_internal.bitor(1, 1)
FROM dual;

DBMS_STATS_INTERNAL.BITOR(1,1)
------------------------------
                             1
 
CAN_DERIVE_COL_HISTOGRAM
Undocumented dbms_stats_internal.can_derive_col_histogram(
nnv                     IN     NUMBER,
tobjn                   IN     NUMBER,
icol                    IN     NUMBER,
colsize                 IN OUT NUMBER,
derive_global_histogram    OUT BOOLEAN);
TBD
 
CCT
Undocumented dbms_stats_internal.cct(ownerin IN VARCHAR2);
exec dbms_stats_internal.cct('SYS');

PL/SQL procedure successfully completed.
 
CHECKAUTOJOB
Undocumented dbms_stats_internal.checkautojob RETURN BOOLEAN;
BEGIN
  IF dbms_stats_internal.checkautojob THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
F

PL/SQL procedure successfully completed.
 
CHECK_ADVISOR_INPUT_VALIDITY
Validates the value of an Advisor input dbms_stats_internal.check_advisor_input_validity(
task_id        IN NUMBER,
execution_name IN VARCHAR2,
report_type    IN VARCHAR2);
TBD
 
CHECK_ADVISOR_OBJ_FILTER
Undocumented dbms_stats_internal.check_advisor_obj_filter(
rule_id IN NUMBER,
obj_num IN NUMBER,
batched IN NUMBER)
RETURN VARCHAR2;
TBD
 
CHECK_ADVISOR_OPR_FILTER
Undocumented dbms_stats_internal.check_advisor_opr_filter(
rule_id   IN NUMBER
task_id   IN NUMBER
opr_type  IN VARCHAR2
opr_name  IN VARCHAR2
target    IN VARCHAR2
param_val IN VARCHAR2
username  IN VARCHAR2
privilege IN NUMBER)
RETURN VARCHAR2;
TBD
 
CHECK_ADVISOR_RULE_FILTER
Creates an Advisor rule filter dbms_stats_internal.check_advisor_rule_filter(
rule_id  IN NUMBER,
task_id  IN NUMBER,
opr_type IN VARCHAR2)
RETURN VARCHAR2;
TBD
 
CHECK_ANALYZE_DONE
Returns TRUE if stats were collected more recently than the "start_time" parameter. Outputs TRUE if the object was found to exist dbms_stats_internal.check_analyze_done(
objnum     IN  NUMBER,
start_time IN  DATE,
not_found  OUT BOOLEAN);
RETURN BOOLEAN;
conn sys@pdbdev as sysdba

SELECT object_id
FROM dba_objects
WHERE object_name = 'SERVERS';

DECLARE
  outVal BOOLEAN;
  retVal BOOLEAN;
BEGIN
  retVal := dbms_stats_internal.check_analyze_done(76842, TRUNC(SYSDATE-10/1440), outVal);

  IF retVal THEN
    dbms_output.put_line('Returned True');
  ELSE
    dbms_output.put_line('Returned False');
  END IF;

  IF outVal THEN
    dbms_output.put_line('Not Found');
  ELSE
    dbms_output.put_line('Found');
  END IF;
END;
/

exec dbms_stats.gather_table_stats('UWCLASS', 'SERVERS');
 
CHECK_AUTO_STATS_ENABLED
Undocumented dbms_stats_internal.check_auto_stats_enabled;
exec dbms_stats_internal.check_auto_stats_enabled;

PL/SQL procedure successfully completed.
 
CHECK_DOMIDX_PARTITIONS
Undocumented dbms_stats_internal.check_domidx_partitions(
idx_objno  IN NUMBER,
part_objno IN NUMBER);
RETURN NUMBER;
TBD
 
CHECK_DOMIDX_PARTS
Undocumented but appears related to domain indexes and partitioned tables dbms_stats_internal.check_domidx_parts(
idx_objno   IN NUMBER,
part_objno  IN NUMBER,
iinfo_flags IN NUMBER)
RETURN NUMBER;
TBD
 
CHECK_DOMIDX_SUBPARTITIONS
Undocumented dbms_check_domidx_subpartitions(
idx_objno  IN NUMBER,
part_objno IN NUMBER,
is_subpart IN BOOLEAN);
RETURN NUMBER;
TBD
 
CHECK_GLOBAL_TAB_STALENESS
Undocumented dbms_stats_internal.check_global_tab_staleness(share_type IN NUMBER);
TBD
 
CHECK_GRANULARITY
Undocumented dbms_stats_internal.check_granularity(
granularity IN VARCHAR2,
granchk     IN VARCHAR2)
RETURN BOOLEAN;
TBD
 
CHECK_HIST_MISSING
Undocumented dbms_stats_internal.check_hist_missing(
clist_hist IN TABLE sys.dbms_stats_internal.chtab,
obj        IN NUMBER,
rowcnt     IN NUMBER)
RETURN NUMBER;
TBD
 
CHECK_INCREMENTAL_STALENESS
Undocumented dbms_stats_internal.check_incremental_staleness(
stalenessu   IN VARCHAR2,
stalenesschk IN VARCHAR2)
RETURN BOOLEAN;
TBD
 
CHECK_MISSING_CSTATS (new 21c)
Undocumented dbms_stats_internal.check_missing_cstats(owner IN VARCHAR2);
exec dbms_stats_internal.check_missing_cstats('C##UWCLASS');

PL/SQL procedure successfully completed.
 
CHECK_VOLATILE
Undocumented dbms_stats_internal.check_volatile(tobjn IN NUMBER) RETURN VARCHAR2;
SELECT dbms_stats_internal.check_volatile(350)
FROM dual;

DBMS_STATS_INTERNAL.CHECK_VOLATILE(350)
----------------------------------------
F
 
CHECK_WINDOW_CAPTURE_MODE
Undocumented dbms_stats_internal.check_window_capture_mode RETURN BINARY_INTEGER;
SELECT dbms_stats_internal.check_window_capture_mode
FROM dual;

CHECK_WINDOW_CAPTURE_MODE
-------------------------
                        0
 
CLEAR_MON_COL_USAGE_FLAG
Clears the column monitoring usage flag dbms_stats_internal.clear_mon_col_usage_flag(session IN BOOLEAN);
exec dbms_stats_internal.clear_mon_col_usage_flag(TRUE);
 
COLHASBASESTATS
Returns TRUE if stats have been collected on the identified column dbms_stats_internal.colhasbasestats(
tobjn IN NUMBER,
icoln IN NUMBER)
RETURN BOOLEAN;
conn sys@pdbdev as sysdba

SELECT object_id
FROM dba_objects
WHERE object_name = 'SERVERS';

SELECT col#, name
FROM col$
WHERE obj#=76842;

BEGIN
  IF dbms_stats_internal.colhasbasestats(76842, 1) THEN
    dbms_output.put_line('This column base stats collected');
  ELSE
    dbms_output.put_line('This column does not have base stats collected');
  END IF;
END;
/
 
COLHASHISTOGRAM
Returns TRUE if the column has a histogram, FALSE if it does not dbms_stats_internal.colhashistogram(
tobjn IN NUMBER,
icoln IN NUMBER)
RETURN BOOLEAN;
conn sys@pdbdev as sysdba

SELECT object_id
FROM dba_objects
WHERE object_name = 'SERVERS';

SELECT col#, name
FROM col$
WHERE obj#=76842;

BEGIN
  IF dbms_stats_internal.colhashistogram(76842, 1) THEN
    dbms_output.put_line('This column has a histogram');
  ELSE
    dbms_output.put_line('This column does not have a histogram');
  END IF;
END;
/
 
COLUMN_EXISTS
Undocumented dbms_stats_internal.column_exists(
owner   IN VARCHAR2,
tabname IN VARCHAR2,
colname IN VARCHAR2)
RETURN BOOLEAN;
conn sys@pdbdev as sysdba

BEGIN
  IF dbms_stats_internal.column_exists('UWCLASS', 'SERVERS', 'LATITUDE') THEN
    dbms_output.put_line('The LATITUDE Column Exists');
  ELSE
    dbms_output.put_line('The LATITUDE Column Does Not Exist');
  END IF;
END;
/
 
COL_STATS_FROM_LOAD
Undocumented dbms_stats_internal.col_stats_from_load(
baseobj    IN NUMBER,
obj        IN NUMBER,
clist_hist IN dbms_stats_internal.chtab);
TBD
 
COMPOSE_HASHVAL_CLOB_REC
Undocumented dbms_stats_internal.compose_hashval_clob_rec(
tobjn     IN NUMBER,
fobjn     IN NUMBER,
group_num IN NUMBER);
RETURN PIPELINED dbms_stats_internal.synhashvaltab;
TBD
 
COMPUTE_DOP
Compute the degree of parallelism dbms_stats_internal.compute_dop(
orig_dop      IN NUMBER,
nblks         IN NUMBER,
min_px_blocks IN NUMBER)
RETURN NUMBER;
SELECT dbms_stats_internal.compute_dop(dbms_stats_internal.get_default_dop, 8, 201)
FROM dual;

DBMS_STATS_INTERNAL.COMPUTE_DOP(DBMS_STATS_INTERNAL.GET_DEFAULT_DOP,8,201)
--------------------------------------------------------------------------
                                                                       1
 
CONTROL_PARALLEL
Undocumented dbms_stats_internal.control_parallel(
enable  IN BOOLEAN,
pqflags IN BINARY_INTEGER)
RETURN BOOLEAN;
TBD
 
COUNT_PDB_OR_SHARD
Undocumented

The demo, at right isn't a proper usage but demonstrates the syntax is valid
dbms_stats_internal.count_pdb_or_shard(
ownname  IN VARCHAR2,
tabname  IN VARCHAR2,
tab_type IN VARCHAR2)
RETURN NUMBER;
SELECT dbms_stats_internal.count_pdb_or_shard(USER, 'OBJ$', 1)
FROM dual;

DBMS_STATS_INTERNAL.COUNT_PDB_OR_SHARD(USER,'OBJ$',1)
-----------------------------------------------------
                                                    0
 
CREATE_ADVISOR_OBJECT
Create an Advisor object dbms_stats_internal.create_advisor_object(
task_id  IN NUMBER,
obj_type IN VARCHAR2)
RTURN NUMBER;
TBD
 
CREATE_TEMP
Undocumented dbms_stats_internal.create_temp(
seltxt   IN  VARCHAR2,
fromtxt  IN  VARCHAR2,
wheretxt IN  VARCHAR2,
ttabname OUT VARCHAR2,
uname    IN  VARCHAR2);
TBD
 
DBMS_ASSERT_SIMPLE_SQL_NAME
Call to the DBMS_ASSERT package dbms_stats_internal.dbms_assrt_simple_sql_name(str IN VARCHAR2) RETURN VARCHAR2;
SELECT dbms_stats_internal.dbms_assert_simple_sql_name('SYS.OBJ$')
FROM dual;
SELECT dbms_stats_internal.dbms_assert_simple_sql_name('SYS.OBJ$')
*
ERROR at line 1:
ORA-20001: SYS.OBJ$ is an invalid identifier
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22383


SELECT dbms_stats_internal.dbms_assert_simple_sql_name('OBJ$')
FROM dual;

DBMS_STATS_INTERNAL.DBMS_ASSERT_SIMPLE_SQL_NAME('OBJ$')
-------------------------------------------------------
OBJ$
 
DEBUG_FLAG_IS_SET (new 21c)
Undocumented dbms_stats_internal.debug_flag_is_set(f IN NUMBER) RETURN BOOLEAN;
TBD
 
DECODE_GRANULARITY
Undocumented dbms_stats_internal.decode_granularity(
tobjn       IN NUMBER,
granularity IN VARCHAR2)
RETURN BINARY_INTEGER;
DECLARE
 retVal PLS_INTEGER;
BEGIN
  retVal := dbms_stats_internal.decode_granularity(76842, 'Z');
  dbms_output.put_line(retVal);
END;
/
11

PL/SQL procedure successfully completed.
 
DELETE_CACHE_HISTORY
Undocumented dbms_stats_internal.delete_cache_history(
styp  IN NUMBER,
owner IN VARCHAR2,
tab   IN VARCHAR2,
part  IN VARCHAR2,
spart IN VARCHAR2);
TBD
 
DELETE_COL_USAGE
Undocumented dbms_stats_internal.delete_col_usage(
ownname        IN VARCHAR2,
tabname        IN VARCHAR2,
purge_old_only IN BOOLEAN);
conn sys@pdbdev as sysdba

exec dbms_stats_internal.delete_col_usage('UWCLASS', 'SERVERS', TRUE);
 
DELETE_EXPR_STATS
Undocumented dbms_stats_internal.delete_expr_stats(
ownname     IN VARCHAR2,
tabname     IN VARCHAR2,
snapshot_id IN NUMBER);
TBD
 
DELETE_FROM_USTATS
Deletes the designated row from USTATS$ dbms_stats_internal.delete_from_ustats(
obj_num IN NUMBER,
intcol  IN NUMBER);
col statistics format a10

SELECT * FROM sys.ustats$;

  OBJ# INTCOL# STATSTYPE#   PROPERTY STATISTICS     SPARE1     SPARE2
------ ------- ---------- ---------- ---------- ---------- ----------
 23695       0      23815          5


exec dbms_stats_internal.delete_from_ustats(23695, 0);

PL/SQL procedure successfully completed.

SELECT * FROM sys.ustats$;

no rows selected
 
DELETE_PARTITION_SYNOPSIS
Undocumented dbms_stats_internal.delete_partition_synopsis(
tobjn       IN NUMBER,
groups      IN sys.dbmsstatnumtab,
clist_syn   IN sys.dbmsstatnumtab,
dop         IN NUMBER,
hdaction    IN NUMBER,
allowcommit IN BOOLEAN);
TBD
 
DELETE_PROC_RATE_CALLOUT
Undocumented

Opened SR 3-9974418850 on 5 Dec 2014
dbms_stats_internal.delete_proc_rate_callout(stat_sourceid IN NUMBER)
RETURN BINARY_INTEGER;
SELECT dbms_stats_internal.delete_proc_rate_callout(3)
FROM dual;

DBMS_STATS_INTERNAL.DELETE_PROC_RATE_CALLOUT(1)
-----------------------------------------------
                                              1


-- all values tested other than 1, 2, or 3 produce an ORA-00600

SELECT dbms_stats_internal.delete_proc_rate_callout(8)
FROM dual;
SELECT dbms_stats_internal.delete_proc_rate_callout(8)
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkecResetValue: invalid source id], [8], [], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 14936
 
DELETE_SINGLE_COL_GROUP_USAGE
Undocumented dbms_stats_internal.delete_single_col_group_usage(
objn      IN NUMBER,
col_group IN VARCHAR2);
TBD
 
DELETE_STATS_OPERATION
Undocumented dbms_stats_internal.delete_stats_operation(opid IN NUMBER);
exec dbms_stats_internal.delete_stats_operation(1);

PL/SQL procedure successfully completed.
 
DEL_SESSION_COL_STATS
Delete session-level column statistics dbms_stats_internal.del_session_col_stats(cstats IN dbms_stats_internal.coltab);
TBD
 
DERIVE_GLOBAL_HISTOGRAM
Undocumented dbms_stats_internal.derive_global_histogram(
tobjn       IN     NUMBER,
intcoln     IN     NUMBER,
cht         IN OUT dbms_stats_internal.colhisttab,
mnb         IN     NUMBER,
cind        IN     NUMBER,
freq        IN OUT BOOLEAN,
ndv         IN     NUMBER,
nmin        IN     NUMBER,
nnv         IN     NUMBER,
ssize       IN OUT NUMBER,
ssizesq     IN OUT NUMBER,
popcnt      IN OUT NUMBER,
popcntsq    IN OUT NUMBER,
auto_sample IN     BOOLEAN);
TBD
 
DML_STATTAB_PREFS
Undocumented dbms_stats_internal.dml_stattab_prefs(
dml_type IN VARCHAR2,
ownname  IN VARCHAR2,
tabname  IN VARCHAR2,
stattab  IN VARCHAR2,
p_statid IN VARCHAR2,
statown  IN VARCHAR2,
statver  IN VARCHAR2);
TBD
 
DML_TABLE_PREFS
Undocumented dbms_stats_internal.dml_table_prefs(
dml_type  IN VARCHAR2,
ownnameu  IN VARCHAR2,
tabnameu  IN VARCHAR2,
pnameu    IN VARCHAR2,
valcharu  IN VARCHAR2,
isdefault IN NUMBER);
TBD
 
DELETE_TABLE_STATS_MODEL (new 21c)
Undocumented dbms_stats_internal.delete_table_stats_model(objn IN NUMBER);
exec dbms_stats_internal.delete_table_stats_model(84134);

PL/SQL procedure successfully completed.
 
DQ
Undocumented but the name reads in English as "DEQUEUE" so perhaps that is a clue dbms_stats_internal.dq(str IN VARCHAR2) RETURN VARCHAR2;
/* the following is extracted from $ORACLE_HOME/rdbms/admin/catist.sql and edited to make it easier to understand the use of dbms_stats_internal.dq and formatted again to improve clarity */

SELECT /* PARTITIONS, NOT IOT */ u.name, o.name, o.subname, tp.part#,
CASE WHEN tp.analyzetime IS NULL THEN
       NULL
     WHEN ((m.inserts + m.deletes + m.updates) >
          tp.rowcnt *  TO_NUMBER(dbms_stats.get_prefs('STALE_PERCENT',
          dbms_stats_internal.dq(u.name), dbms_stats_internal.dq(o.name)))/100
          OR bitand(m.flags,1) = 1) THEN
       'YES'
     ELSE
       'NO'
     END
FROM sys.user$ u, sys.obj$ o, sys.tabpartv$ tp, sys.tab_stats$ ts,
     sys.tab$ tab, sys.mon_mods_all$ m
WHERE o.owner# = u.user#
AND o.obj# = tp.obj#
AND tp.bo# = tab.obj#
AND bitand(tab.property, 64) = 0
AND o.obj# = ts.obj# (+)
AND tp.obj# = m.obj# (+)
AND o.namespace = 1 and o.remoteowner IS NULL
AND o.linkname IS NULL
AND bitand(o.flags, 128) = 0 -- not in recycle bin
AND (o.owner# = userenv('SCHEMAID') OR tp.bo# IN (
  SELECT oa.obj#
  FROM sys.objauth$ oa
  WHERE grantee# IN (
    SELECT kzsrorol
    FROM x$kzsro))
OR /* user has system privileges */ EXISTS (
  SELECT NULL
  FROM v$enabledprivs
  WHERE priv_number in (-45 /* LOCK ANY TABLE */,
                        -47 /* SELECT ANY TABLE */,
                        -48 /* INSERT ANY TABLE */,
                        -49 /* UPDATE ANY TABLE */,
                        -50 /* DELETE ANY TABLE */)));
 
DROP_OLD_TEMP
Undocumented dbms_stats_internal.drop_old_temp(maxtabs IN BINARY_INTEGER);
exec dbms_stats_internal.drop_old_temp(1);

PL/SQL procedure successfully completed.
 
DROP_STATS_MODEL_AUTOTXN (new 21c)
Undocumented dbms_stats_internal.drop_stats_model_autotxn(model_name IN VARCHAR2);
TBD
 
DROP_TEMP
Undocumented dbms_stats_internal.drop_temp(ttabname IN VARCHAR2);
TBD
 
DUMP_ALERT_TSES
Undocumented dbms_stats_internal.drop_alert_tses(str IN VARCHAR2);
TBD
 
DUMP_PQ_SESSTAT
Undocumented dbms_stats_internal.dump_pq_sesstat(
comment IN VARCHAR2,
endtime IN BOOLEAN);
exec dbms_stats_internal.dump_pq_sesstat('DBMS_STATS_INTERNAL Test', TRUE);
 
DUMP_QUERY
Undocumented
Overload 1
dbms_stats_internal.dump_query(
sqltxt IN VARCHAR2,
fobjn  IN NUMBER);
TBD
Overload 2 dbms_stats_internal.dump_query(
sqltxt IN CLOB,
fobjn  IN NUMBER);
TBD
Overload 3 dbms_stats_internal.dump_query(
sqltxt IN dbms_sql.varchar2A,
fobjn  IN NUMBER);
TBD
 
DUMP_TRACE
Undocumented
Overload 1
dbms_stats_internal.dump_trace(str IN CLOB);
TBD
Overload 2 dbms_stats_internal.dump_trace(str IN VARCHAR2);
TBD
 
DUMP_TRACE_TS
Undocumented dbms_stats_internal.dump_trace_ts(str IN VARCHAR2);
TBD
 
ESCAPE_XML (new 21c)
Undocumented dbms_stats_internal.escape_xml(str IN VARCHAR2) RETURN VARCHAR2;
SELECT dbms_stats_internal.escape_xml('<PDRecord><PDName>Daniel Morgan</PDName></PDRecord>')
FROM dual;

DBMS_STATS_INTERNAL.ESCAPE_XML('<PDRECORD><PDNAME>DANIELMORGAN</PDNAME></PDRECORD>')
------------------------------------------------------------------------------------
&lt;PDRecord&gt;&lt;PDName&gt;Daniel Morgan&lt;/PDName&gt;&lt;/PDRecord&gt;
 
EST_USING_NONEXACT_HISTORY
Undocumented dbms_stats_internal.est_using_nonexact_history(
nblocks        IN     NUMBER,
cost_per_block IN OUT NUMBER)
RETURN NUMBER;
DECLARE
 retVal NUMBER;
 cpb    NUMBER := 2;
BEGIN
  retVal := dbms_stats_internal.est_using_nonexact_history(100, cpb);
  dbms_output.put_line(retVal);
  dbms_output.put_line(cpb);
END;
/
200
2
 
EXECUTE_ADVISOR_AUTO_TASK
Executes an advisor autotask dbms_stats_internal.execute_advisor_auto_task(client IN VARCHAR2);
exec dbms_stats_internal.execute_advisor_auto_task('UWTASK09);
 
EXPORT_COLSTATS_DIRECT
Undocumented dbms_stats_internal.export_colstats_direct(
owner         IN VARCHAR2,
tabname       IN VARCHAR2,
colname       IN VARCHAR2,
partname      IN VARCHAR2,
stattab       IN VARCHAR2,
statid        IN VARCHAR2,
statown       IN VARCHAR2,
version       IN NUMBER,
cascade_parts IN BOOLEAN);
TBD
 
EXPORT_COL_USAGE
Export column usage statistics dbms_stats_internal.export_col_usage(
stattabq IN VARCHAR2,
owner    IN VARCHAR2,
tabnameu IN VARCHAR2,
statid   IN VARCHAR2);
TBD
 
EXPORT_FXT_COLSTATS_DIRECT
Undocumented dbms_stats_internal.export_fxt_colstats_direct(
owner   IN VARCHAR2,
tabname IN VARCHAR2,
colname IN VARCHAR2,
stattab IN VARCHAR2,
statid  IN VARCHAR2,
statown IN VARCHAR2,
version IN NUMBER);
TBD
 
EXTRACT_STR
Undocumented dbms_stats_internal.extract_str(
sourcestr IN     VARCHAR2,
start_ix  IN OUT NUMBER,
str       IN OUT VARCHAR2)
RETURN BOOLEAN;
DECLARE
 sVal   NUMBER := 3;
 outVal VARCHAR2(200);
BEGIN
  IF dbms_stats_internal.extract_str('ABCDEFGHIJKLMNOPQRSTUVXWYZ', sVal, outVal) THEN
    dbms_output.put_line('T1: ' || TO_CHAR(sVal));
    dbms_output.put_line('T2: ' || outVal);
  ELSE
    dbms_output.put_line('F1: ' || TO_CHAR(sVal));
    dbms_output.put_line('F2: ' || outVal);
  END IF;
END;
/
F1: 27
F2:

PL/SQL procedure successfully completed.
 
FILL_IN_PARAMS
Undocumented dbms_stats_internal.fill_in_params RETURN dbms_stats_internal.pararray;
TBD
 
FILL_IN_PARAMS_WITH_NO_PREFS
Undocumented dbms_stats_internal.fill_in_params_with_no_prefs RETURN dbms_stats_internal.pararray;
TBD
 
FILL_SYNOPSIS
Undocumented dbms_stats_internal.fill_syopsis(
tobjn          IN NUMBER,
group_num      IN NUMBER,
intcol_num     IN NUMBER,
cursplit       IN NUMBER,
analyze_time   IN TIMESTAMP WITH TIME ZONE,
hashval        IN CLOB,
zero_col_stats IN BOOLEAN);
TBD
 
FIXQ
Undocumented dbms_stats_internal.fixq(str IN VARCHAR2) RETURN VARCHAR2;
TBD
 
FIX_IND_NAMES
Undocumented dbms_stats_internal.fix_ind_names(
stattabq IN VARCHAR2,
statidu  IN VARCHAR2,
own      IN VARCHAR2,
ind      IN VARCHAR2)
RETURN VARCHAR2;
TBD
 
FIX_TAB_NAMES
Undocumented dbms_stats_internal.fix_tab_names(
stattabq IN VARCHAR2,
statidu  IN VARCHAR2,
own      IN VARCHAR2,
ind      IN VARCHAR2);
TBD
 
FLUSH_CACHE_STATS
Undocumented dbms_stats_internal.flush_cache_stats(
styp             IN NUMBER,
nblklimit        IN NUMBER,
stats_inv_factor IN NUMBER);
TBD
 
FORMAT_CACHE_ROWS
Undocumented dbms_stats_internal.format_cache_rows(c IN REF CURSOR)
RETURN PIPELINED dbms_stats_internal.t_cacheStatOutSet;
TBD
 
GATHER_DATABASE_STATS_MODEL (new 21c)
Undocumented dbms_stats_internal.gather_database_stats_model;
exec dbms_stats_internal.gather_database_stats_model;

PL/SQL procedure successfully completed.
 
GATHER_FXT_STATS_OK
Undocumented dbms_stats_internal.gather_fxt_stats_ok(objn IN NUMBER) RETURN BOOLEAN;
SELECT name, object_id
FROM v$fixed_table
WHERE rownum < 11;

NAME          OBJECT_ID
------------- ---------
X$KQFTA       4294950912
X$KQFVI       4294950913
X$KQFVT       4294951149
X$KQFDT       4294950914
X$KQFCO       4294951036
X$KQFOPT      4294952712
X$KYWMPCTAB   4294952922
X$KYWMWRCTAB  4294953009
X$KYWMCLTAB   4294952923
X$KYWMNF      4294952924


DECLARE
 b BOOLEAN;
BEGIN
  IF dbms_stats_internal.gather_fxt_stats_ok(4294950912) THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
T

PL/SQL procedure successfully completed.
 
GATHER_INDEX
Undocumented dbms_stats_internal.gather_index(
tobjn     IN NUMBER,
get_index IN VARCHAR2)
RETURN VARCHAR2;
TBD
 
GATHER_PROCESSING_RATE_AUX
Undocumented but it appears that the correct name for the job is SYS.ST$PRATE_GATHER dbms_stats_internal.gather_processing_rate_aux(gatherfreq IN NUMBER);
exec dbms_stats_internal.gather_processing_rate_aux(0);
SQL> exec dbms_stats_internal.gather_processing_rate_aux(10);
BEGIN dbms_stats_internal.gather_processing_rate_aux(10); END;
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 19775
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 17634
ORA-06512: at line 1
 
GATHER_PROCESSING_RATE_JOB
Undocumented but it appears that the correct name for the job is SYS.ST$PRATE_GATHER dbms_stats_internal.gather_processing_rate_job(
gathermode IN VARCHAR2,
timelimit  IN NUMBER,
gatherfreq IN NUMBER);
exec dbms_stats_internal.gather_processing_rate_job('TEST', 10, 10);
BEGIN dbms_stats_internal.gather_processing_rate_job('TEST', 10, 10); END;

*
ERROR at line 1:
ORA-27475: unknown job "SYS"."ST$PRATE_GATHER"
ORA-06512: at "SYS.DBMS_ISCHED", line 274
ORA-06512: at "SYS.DBMS_SCHEDULER", line 753
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 17599
ORA-06512: at line 1
 
GATHER_SCAN_RATE
Sets the gather frequency for a target object number dbms_stats_internal.gather_scan_rate(
tobjn            IN VARCHAR2,
gather_frequency IN NUMBER);
exec dbms_stats_internal.gather_scan_rate(21921, 1);

PL/SQL procedure successfully completed.
 
GATHER_SCAN_RATE_BY_MMON
Gather MMON I/O scan rate dbms_stats_internal.gather_scan_rate_by_mmon;
exec dbms_stats_internal.gather_scan_rate_by_mmon;

PL/SQL procedure successfully completed.
 
GATHER_SQL_STATS
Undocumented dbms_stats_internal.gather_sql_stats(
sql_text    IN     CLOB,
user_name   IN     VARCHAR2,
bind_list   IN     sys.sql_binds,
options     IN     VARCHAR2,
rept        IN OUT sys.xmltype,
err_code       OUT NUMBER,
err_mesg       OUT VARCHAR2,
exec_userid IN     NUMBER);
DECLARE
 rept  XMLTYPE;
 ecode NUMBER;
 emesg VARCHAR2(60);
BEGIN
  dbms_stats_internal.gather_sql_stats('SELECT * FROM sys.tab$', USER, NULL, 'CASCADE', rept, ecode, emesg, 0);
END;
/
DECLARE
*
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19213: error occurred in XML processing at lines 1
LPX-00217: invalid character 2 (U+0002)
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 11963
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 12061
ORA-06512: at line 6


-- clearly more work needs to be done on this demo
 
GATHER_TABLE_STATS_MODEL (new 21c)
Undocumented dbms_stats_internal.gather_table_stats_model(
ownname       IN VARCHAR2,
tabname       IN VARCHAR2,
tobjn         IN NUMBER,
op_id         IN NUMBER,
model_control IN NUMBER,
from_database IN BOOLEAN);
TBD
 
GENERATE_OP_ID
Undocumented dbms_stats_internal.generate_op_id RETURN NUMBER;
SELECT dbms_stats_internal.generate_op_id
FROM dual;

GENERATE_OP_ID
--------------
          2772
 
GENERATE_PARAMS_XML_INT (new 21c)
Undocumented dbms_stats_internal.generate_params_xml_int(
params     IN OUT sys.dbms_stats_internal.varchartab,
params_xml IN OUT VARCHAR2,
owner      IN     VARCHAR2,
tabnameu   IN     VARCHAR2);
TBD
 
GET_ADVISOR_DEFAULT_INCLUDE
Undocumented dbms_stats_internal.get_advisor_default_include(
task_id   IN NUMBER,
opr_type  IN VARCHAR2,
rule_name IN VARCHAR2,
type      IN VARCHAR2)
RETURN BOOLEAN;
TBD
 
GET_ADVISOR_FINDING_DEF_ID
Undocumented dbms_stats_internal.get_advisor_finding_def_id(
task_id    IN NUMBER,
exec_name  IN VARCHAR2,
finding_id IN NUMBER)
RETURN NUMBER;
TBD
 
GET_ADVISOR_FINDING_ID
Undocumented dbms_stats_internal.get_advisor_finding_id(finding_name IN VARCHAR2) RETURN NUMBER;
SELECT DISTINCT finding_id, finding_name
FROM v$stats_advisor_findings
WHERE rownum < 11
ORDER BY 1;

FINDING_ID FINDING_NAME
---------- ----------------------------------------
         0
         1 F_MaintenanceWindowNotCreated
         2 F_MaintenanceWindowNotEnabled
         3 F_TaskNotEnabled
         4 F_OptimizerStatsOffForAutoTask
         5 F_AutoMaintenanceOff
         6 F_OptimizerAutoJobOff
         7 F_JobQueueProcessZero
         8 F_UseGatherTableStats
         9 F_ObjHasNoStats

SELECT dbms_stats_internal.get_advisor_finding_id('F_UseGatherTableStats')
FROM dual;

DBMS_STATS_INTERNAL.GET_ADVISOR_FINDING_ID('F_USEGATHERTABLESTATS')
-------------------------------------------------------------------
                                                                  8
 
GET_ADVISOR_FINDING_NAME
Returns the finding name for the corresponding ADDM finding dbms_stats_internal.get_advisor_finding_name(finding_id IN NUMBER) RETURN VARCHAR2;
SELECT DISTINCT finding_id, finding_name
FROM v$stats_advisor_findings
WHERE rownum < 11;

FINDING_ID FINDING_NAME
---------- ----------------------------------------
3 F_TaskNotEnabled
0
4 F_OptimizerStatsOffForAutoTask
9 F_ObjHasNoStats
6 F_OptimizerAutoJobOff
7 F_JobQueueProcessZero
8 F_UseGatherTableStats
1 F_MaintenanceWindowNotCreated
2 F_MaintenanceWindowNotEnabled
5 F_AutoMaintenanceOff


SELECT dbms_stats_internal.get_advisor_finding_name(2)
FROM dual;

DBMS_STATS_INTERNAL.GET_ADVISOR_FINDING_NAME(2)
------------------------------------------------
F_MaintenanceWindowNotEnabled
 
GET_ADVISOR_NUM_FINDINGS
Undocumented dbms_stats_internal.get_advisor_num_findings(
task_id        IN NUMBER,
execution_name IN VARCHAR2)
RETURN NUMBER;
TBD
 
GET_ADVISOR_OBJECT_TYPE_ID
Undocumented dbms_stats_internal.get_advisor_object_type_id(obj_type IN VARCHAR2) RETURN NUMBER;
TBD
 
GET_ADVISOR_OPR_FILTER
Undocumented dbms_stats_internal.get_advisor_opr_filter(
opr_id    IN     NUMBER,
opr_name  IN OUT VARCHAR2,
param_val IN OUT VARCHAR2);
TBD
 
GET_ADVISOR_PARAM_VAL
Returns the value of the corresponding ADDM advisor parameter dbms_stats_internal.get_advisor_param_val(
task_id    IN NUMBER,
param_name IN VARCHAR2)
RETURN VARCHAR2;
SELECT parameter_name
FROM DBA_ADVISOR_DEF_PARAMETERS
WHERE advisor_name = 'ADDM';

PARAMETER_NAME
-----------------
ANALYSIS_TYPE
DBIO_EXPECTED
DB_ELAPSED_TIME
DB_ID
HISTORY_TABLE
INSTANCES
SCOPE_TYPE
SCOPE_VALUE


SELECT dbms_stats_internal.get_advisor_param_val(350, 'ANALYSIS_TYPE')
FROM dual;

DBMS_STATS_INTERNAL.GET_ADVISOR_PARAM_VAL(350,'ANALYSIS_TYPE')
---------------------------------------------------------------
PERIOD
 
GET_ADVISOR_RAT_ID
Undocumented but is likely associated with Real Application Testing dbms_stats_internal.get_advisor_rat_id(rtl_name IN VARCHAR2) RETURN NUMBER;
TBD
 
GET_ADVISOR_REC_CBK
Undocumented dbms_stats_internal.get_advisor_rec_cbk(rec_def_id IN NUMBER) RETURN VARCHAR2;
TBD
 
GET_ADVISOR_REC_ID
Undocumented dbms_stats_internal.get_advisor_rec_id(rec_name IN VARCHAR2) RETURN NUMBER;
SELECT rec_id, rec_name
FROM v$stats_advisor_recs
ORDER BY 1;

 REC_ID REC_NAME
------- -----------------------
      0
      1 Rec_SetParameterToValue
      2 Rec_CreateMaintenanceWindow
      3 Rec_EnableMaintenanceWindow
      4 Rec_EnableAutoTask
      5 Rec_EnableStatsForAutoTask
      6 Rec_UseGatherSchemaStats
      7 Rec_FileSR
      8 Rec_IncreaseMaintenanceWindow
      9 Rec_SetConcurrent
     10 Rec_SetDefaultGlobalPref
     11 Rec_TurnOnSPD
     12 Rec_GatherOnNoStatsObj
     13 Rec_RegatherOnStaleStatsObj
     14 Rec_NoSetStats
     15 Rec_InvestigateCorrectOpr
     16 Rec_UseDefaultParam
     17 Rec_CopyNewPartStats
     18 Rec_BadStats
     19 Rec_ConsistentRefStats
     20 Rec_ConsistentIndexStats
     21 Rec_TruncateInsteadOfDrop
     22 Rec_SetAutostatsTargetauto
     23 Rec_CheckOtherPartsStasHist
     24 Rec_PurgeFailed
     25 Rec_ReduceRetentionPeriod
     26 Rec_ObjUseDefaultPref
     27 Rec_AvoidAnalyzeTable
     28 Rec_AvoidFreqStatsCollection
     29 Rec_GatherStatsAfterBulkDML
     30 Rec_UseIncremental
     31 Rec_NotUseIncremental
     32 Rec_UseAutoDegree
     33 Rec_LockVolatileTable
     34 Rec_UnlockNonVolatileTable


SELECT dbms_stats_internal.get_advisor_rec_id('Rec_PurgeFailed')
FROM dual;

DBMS_STATS_INTERNAL.GET_ADVISOR_REC_ID('REC_PURGEFAILED')
---------------------------------------------------------
                                                       24
 
GET_ADVISOR_REC_NAME
Undocumented dbms_stats_internal.get_advisor_rec_name(rec_id IN NUMBER) RETURN VARCHAR2;
SELECT MAX(rec_id)
FROM v_$stats_advisor_recs;

REC_ID
------
    34


SELECT dbms_stats_internal.get_advisor_rec_name(34)
FROM dual;

DBMS_STATS_INTERNAL.GET_ADVISOR_REC_NAME(34)
---------------------------------------------
Rec_UnlockNonVolatileTable
 
GET_ADVISOR_REPORT_CBK
Undocumented dbms_stats_internal.get_advisor_report_cbk(
rule_id        IN NUMBER,
finding_def_id IN NUMBER)
RETURN VARCHAR2;
TBD
 
GET_ADVISOR_RTL_NAME
Undocumented dbms_stats_internal.get_advisor_rtl_name(rat_id IN VARCHAR2) RETURN NUMBER;
TBD
 
GET_ADVISOR_RULE_DESC
Returns the description of a rule dbms_stats_internal.get_advisor_rule_desc(rule_id IN NUMBER) RETURN VARCHAR2;
SELECT dbms_stats_internal.get_advisor_rule_desc(1)
FROM dual;

DBMS_STATS_INTERNAL.GET_ADVISOR_RULE_DESC(1)
------------------------------------------------------------
Use Auto Job for Statistics Collection


SELECT dbms_stats_internal.get_advisor_rule_desc(23)
FROM dual;

DBMS_STATS_INTERNAL.GET_ADVISOR_RULE_DESC(23)
------------------------------------------------------------
Avoid using analyze table commands for statistics collection
 
GET_ADVISOR_RULE_ID
Returns the rule identifier for a named rule dbms_stats_internal.get_advisor_rule_id(rule_name IN VARCHAR2) RETURN NUMBER;
SELECT dbms_stats_internal.get_advisor_rule_id('UseAutoDegree')
FROM dual;

DBMS_STATS_INTERNAL.GET_ADVISOR_RULE_ID('USEAUTODEGREE')
--------------------------------------------------------
                                                      21
 
GET_ADVISOR_RULE_NAME
Undocumented dbms_stats_internal.get_advisor_rule_name(rule_id IN NUMBER) RETURN VARCHAR2;
SELECT dbms_stats_internal.get_advisor_rule_name(1) FROM dual;

DBMS_STATS_INTERNAL.GET_ADVISOR_RULE_NAME(1)
---------------------------------------------
USEAUTOJOB


SELECT dbms_stats_internal.get_advisor_rule_name(2) FROM dual;

DBMS_STATS_INTERNAL.GET_ADVISOR_RULE_NAME(2)
---------------------------------------------
COMPLETEAUTOJOB


SELECT dbms_stats_internal.get_advisor_rule_name(3) FROM dual;

DBMS_STATS_INTERNAL.GET_ADVISOR_RULE_NAME(3)
---------------------------------------------
MAINTAINSTATSHISTORY


SELECT dbms_stats_internal.get_advisor_rule_name(4) FROM dual;

DBMS_STATS_INTERNAL.GET_ADVISOR_RULE_NAME(4)
---------------------------------------------
USECONCURRENT


SELECT dbms_stats_internal.get_advisor_rule_name(5) FROM dual;

DBMS_STATS_INTERNAL.GET_ADVISOR_RULE_NAME(5)
---------------------------------------------
USEDEFAULTPREFERENCE


SELECT dbms_stats_internal.get_advisor_rule_name(6) FROM dual;

DBMS_STATS_INTERNAL.GET_ADVISOR_RULE_NAME(6)
---------------------------------------------
TURNONSQLPLANDIRECTIVE


SELECT dbms_stats_internal.get_advisor_rule_name(23) FROM dual;

DBMS_STATS_INTERNAL.GET_ADVISOR_RULE_NAME(23)
---------------------------------------------
AVOIDANALYZETABLE
 
GET_ADVISOR_RULE_OBJ_ID
Undocumented dbms_stats_internal.get_advisor_rule_obj_id(
task_id   IN NUMBER,
rule_id   IN NUMBER,
exec_name IN VARCHAR2);
TBD
 
GET_ADVISOR_TASK_ID
Returns the task ID given the task name and user name dbms_stats_internal.get_advisor_task_id(
task_name IN VARCHAR2,
user_name IN VARCHAR2)
RETURN NUMBER;
TBD
 
GET_ADVISOR_TASK_NAME
Returns the schema that owns the input task name dbms_stats_internal.get_advisor_task_name(task_id IN NUMBER) RETURN VARCHAR2;
SELECT dbms_stats_internal.get_advisor_task_name(350)
FROM dual;
 
GET_ADVISOR_TASK_OWNER
Returns the schema that owns the input task ID dbms_stats_internal.get_advisor_task_owner(task_id IN NUMBER) RETURN VARCHAR2;
SELECT dbms_stats_internal.get_advisor_task_owner(350)
FROM dual;
 
GET_ADVISOR_TASK_PRIVILEGE
Undocumented dbms_stats_internal.get_advisor_task_privilege(task_id IN NUMBER) RETURN NUMBER;
SELECT dbms_stats_internal.get_advisor_task_owner(350)
FROM dual;
 
GET_AGG_CHT_WITH_DISK
Undocumented dbms_stats_internal.get_agg_cht_with_disk(
objn   IN     NUMBER,
clist  IN OUT sys.ctab,
cind   IN     NUMBER,
cht    IN     sys.colhisttab,
nnv    IN     NUMBER,
mnb    IN     NUMBER,
chtmrg IN OUT sys.colhisttab);
TBD
 
GET_AGG_CLIST_WITH_DISK
Undocumented dbms_stats_internal.get_agg_clist_with_disk(
objn       IN NUMBER,
nrows      IN NUMBER,
nrows_disk IN NUMBER,
clist      IN sys.ctab)
RETURN sys.dbms_stats_internal.aggcoltab;
TBD
 
GET_AGG_COLSTATS
Undocumented dbms_stats_internal.get_agg_colstats(
tab_num            IN  NUMBER,
total_rows         IN  NUMBER,
ndv_needed         IN  BOOLEAN,
dop                IN  NUMBER,
hybrid_global_ndv  OUT BOOLEAN,
pdb_or_shard_count IN  NUMBER)
RETURN dbms_stats_internal.aggcoltab;
TBD
 
GET_AGG_NDV
Undocumented dbms_stats_internal.get_agg_ndv(
tobjn IN NUMBER,
icol  IN NUMBER)
RETURN NUMBER;
TBD
 
GET_ALL_ADVISOR_RULE_IDS
Returns a PL/SQL table of all advisor rule IDs dbms_stats_internal.get_all_advisor_rule_ids RETURN dbms_stats_internal.numtab;
TBD
 
GET_APPROX_NDV_ALGORITHM
Undocumented dbms_stats_internal.get_approx_ndv_algorithm(
incremental IN BOOLEAN,
tobjn       IN NUMBER,
ownname     IN VARCHAR2,
tabname     IN VARCHAR2)
RETURN NUMBER;
TBD
 
GET_BLKCNT
Returns the block count for one object dbms_stats_internal.get_blkcnt(tobjn IN NUMBER) RETURN NUMBER;
SELECT obj#, dataobj#
FROM tab$
WHERE obj# = (
  SELECT object_id
  FROM dba_objects
  WHERE object_name = 'OBJ$');

      OBJ#   DATAOBJ#
---------- ----------
        18         18


SELECT dbms_stats_internal.get_blkcnt(18)
FROM dual;

DBMS_STATS_INTERNAL.GET_BLKCNT(18)
----------------------------------


-- while this does not return an error it also does not return a value, not happy with this and will investigate more at a later date.
 
GET_CALIB_OPID_CALLOUT
Undocumented dbms_stats_internal.get_calib_opid_callout(
opname IN  VARCHAR2,
opid   OUT NUMBER,
popid  OUT NUMBER)
RETURN BINARY_INTEGER;
TBD
 
GET_COLNAME
Returns the name of a column based on the column id dbms_stats_internal.get_colname(
obj_num     IN NUMBER,
intcol_num  IN NUMBER,
virtual_col IN VARCHAR2)
RETURN VARCHAR2;
conn sys@pdbdev as sysdba

SELECT obj#, intcol#
FROM col$
WHERE rownum < 11;

   OBJ#  INTCOL#
------- --------
      2        1
      4        1
      4        2
      4        3
      4        4
      4        5
      4        6
      4        7
      4        8
      4        9


SELECT dbms_stats_internal.get_colname(4, 2, NULL)
FROM dual;

DBMS_STATS_INTERNAL.GET_COLNAME(4,2,NULL)
-----------------------------------------
DATAOBJ#
 
GET_COLNUM
Returns the column position for the named column table dbms_stats_internal.get_colnum(
owner   IN VARCHAR2,
tabname IN VARCHAR2,
colname IN VARCHAR2)
RETURN NUMBER;
conn sys@pdbdev as sysdba

SELECT dbms_stats_internal.get_colnum('UWCLASS', 'SERVERS', 'SRVR_ID') FROM dual;

SELECT dbms_stats_internal.get_colnum('UWCLASS', 'SERVERS', 'NETADDRESS') FROM dual;
 
GET_COLTYPE
Returns the integer value of a data type for the identified column table (1 for VARCHAR2, 2 for NUMBER, etc.) dbms_stats_internal.get_coltype(
owner   IN VARCHAR2,
tabname IN VARCHAR2,
colname IN VARCHAR2)
RETURN NUMBER;
conn sys@pdbdev as sysdba

SELECT dbms_stats_internal.get_coltype('UWCLASS', 'SERVERS', 'SRVR_ID') FROM dual;

SELECT dbms_stats_internal.get_coltype('UWCLASS', 'SERVERS', 'NETADDRESS') FROM dual;
 
GET_COL_EXPR
Undocumented dbms_stats_internal.get_col_expr(
col_rowid    IN ROWID,
col_property IN NUMBER)
RETURN CLOB;
TBD
 
GET_COUNT_OF_COLS
In theory it would seem that it counts virtual and non-virtual columns but it appears to have a bug. Given that this is an  undocumented internal package I not be opening an SR. dbms_stats_internal.get_count_of_cols(
owner       IN VARCHAR2,
tabname     IN VARCHAR2,
virtual     IN BOOLEAN,
non_virtual IN BOOLEAN)
RETURN BINARY_INTEGER;
conn sys@pdbdev as sysdba

CREATE TABLE uwclass.testtab (
col1 NUMBER,
col2 NUMBER,
vcol NUMBER AS (col1+col2));

DECLARE
 retVal PLS_INTEGER;
BEGIN
  retVal := dbms_stats_internal.get_count_of_cols('UWCLASS', 'TESTTAB', FALSE, FALSE);
  dbms_output.put_line(retVal);
  retVal := dbms_stats_internal.get_count_of_cols('UWCLASS', 'TESTTAB', TRUE, FALSE);
  dbms_output.put_line(retVal);
  retVal := dbms_stats_internal.get_count_of_cols('UWCLASS', 'TESTTAB', FALSE, TRUE);
  dbms_output.put_line(retVal);
  retVal := dbms_stats_internal.get_count_of_cols('UWCLASS', 'TESTTAB', TRUE, TRUE);
  dbms_output.put_line(retVal);
END;
/
 
GET_CURRENT_JOB_NAME
Returns the name of the current job if one exists: Otherwise NULL dbms_stats_internal.get_current_job_name(job_name OUT VARCHAR2);
DECLARE
 outVal VARCHAR2(128);
BEGIN
  dbms_stats_internal.get_current_job_name(outVal);
  dbms_output.put_line('Job Name: ' || outVal);
END;
/
Job Name:

PL/SQL procedure successfully completed.
 
GET_CURRENT_SESSION_ID
Undocumented

Overload 1
dbms_stats_internal.get_current_session_id(
sesid  OUT BINARY_INTEGER,
sesser OUT BINARY_INTEGER);
SELECT osuser, sid, serial#, program
FROM v$session
WHERE service_name NOT LIKE '%BACK%';

OSUSER                                SID    SERIAL# PROGRAM
------------------------------ ---------- ---------- -------------
perrito5\oracle                       222       6797 sqlplusw.exe

DECLARE
 sid PLS_INTEGER;
 ses PLS_INTEGER;
BEGIN
  dbms_stats_internal.get_current_session_id(sid, ses);
  dbms_output.put_line('SESID:  ' || TO_CHAR(sid));
  dbms_output.put_line('SESSER: ' || TO_CHAR(ses));
END;
/
SESID: 222
SESSER: 6797

PL/SQL procedure successfully completed.
Overload 2 dbms_stats_internal.get_current_session_id(sesid OUT BINARY_INTEGER);
SELECT osuser, sid, serial#, program
FROM v$session
WHERE service_name NOT LIKE '%BACK%';

OSUSER                                SID    SERIAL# PROGRAM
------------------------------ ---------- ---------- -------------
perrito5\oracle                       134       4239 sqlplusw.exe

DECLARE
 sesid PLS_INTEGER;
BEGIN
  dbms_stats_internal.get_current_session_id(sesid);
  dbms_output.put_line('SESID:  ' || TO_CHAR(sesid));
END;
/
SESID: 222

PL/SQL procedure successfully completed.
 
GET_DB_BLOCK_SIZE
Returns the database block size in bytes. Not sure how it handles a database with multiple block sizes as I don't have one available now for testing dbms_stats_internal.get_db_block_size RETURN NUMBER;
SELECT dbms_stats_internal.get_db_block_size
FROM dual;

GET_DB_BLOCK_SIZE
-----------------
             8192
 
GET_DEFAULT$
Undocumented dbms_stats_internal.get_default$(colrowid IN ROWID) RETURN CLOB;
DECLARE
 rid    ROWID;
 outVal CLOB;
BEGIN
  SELECT MAX(rowid)
  INTO rid
  FROM col$;

  outVal := dbms_stats_internal.get_default$(rid);
  dbms_output.put_line(outVal);
END;
/
"PROVISION_MIN"/6 0+ "METADATA_EXP_MIN"/60 + "COPY_TO_NFS_MIN"/60 + "COPY_FROM_NFS_MIN"/60 + "METADATA_IMP_MIN"/60 + "VALIDATION_MIN"/60 + "CONFIGURE_MIN"/60

PL/SQL procedure successfully completed.
 
GET_DEFAULT_ADVISOR_EXECUTION
Returns the execution name for an identified task ID dbms_stats_internal.get_default_advisor_execution(task_id IN NUMBER) RETURN VARCHAR2;
SELECT dbms_stats_internal.get_default_advisor_execution(350)
FROM dual;

DBMS_STATS_INTERNAL.GET_DEFAULT_ADVISOR_EXECUTION(350)
-------------------------------------------------------
EXEC_375
 
GET_DEFAULT_DOP
Returns the default Degree Of Parallelism for the instance dbms_stats_internal.get_default_dop RETURN BINARY_INTEGER;
SELECT dbms_stats_internal.get_default_dop
FROM dual;
 
GET_EXPORT_SQL_CLAUSES
Undocumented dbms_stats_internal.get_export_sql_clauses(
fin_sc   IN     NUMBER,
db_sc    IN     NUMBER,
sql_type IN     VARCHAR2,
flags    IN     NUMBER,
ins_list IN OUT VARCHAR2,
sel_list IN OUT VARCHAR2,
sc_pred  IN OUT VARCHAR2);
TBD
 
GET_EXP_LIST
Undocumented dbms_stats_internal.get_exp_list(
objn       IN NUMBER,
listlen    IN NUMBER,
snapshotid IN NUMBER,
flags      IN NUMBER)
RETURN VARCHAR2;
TBD
 
GET_FIRST_PART_COL
Undocumented Returns the first partition key column from a partitioned table dbms_stats_internal.get_first_part_col(
ownname IN VARCHAR2,
tabname IN VARCHAR2,
objtype IN VARCHAR2)
RETURN VARCHAR2;
conn sys@pdbdev as sysdba

SELECT dbms_stats_internal.get_first_part_col('SH', 'SALES', 'PARTITION')
FROM dual;
 
GET_FXT_OBJ
Returns the object number of a fixed object dbms_stats_internal.get_fxt_obj(
owner   IN VARCHAR2,
tabname IN VARCHAR2)
RETURN NUMBER;
SELECT dbms_stats_internal.get_fxt_obj('SYS', 'X$KCFIO')
FROM dual;

DBMS_STATS_INTERNAL.GET_FXT_OBJ('SYS','X$KCFIO')
------------------------------------------------
                                      4294950957
 
GET_FXT_TYP
Returns the object type number of a fixed object dbms_stats_internal.get_fxt_typ(
owner   IN VARCHAR2,
tabname IN VARCHAR2)
RETURN NUMBER;
SELECT dbms_stats_internal.get_fxt_typ('SYS', 'X$KCFIO')
FROM dual;

DBMS_STATS_INTERNAL.GET_FXT_TYP('SYS','X$KCFIO')
------------------------------------------------
4
 
GET_HIST_DENSITY
Undocumented dbms_stats_internal.get_hist_density(
tobjn IN NUMBER,
icol  IN NUMBER)
RETURN NUMBER;
SELECT bo#, col#, intcol#
FROM icol$
WHERE rownum < 11;

SELECT dbms_stats_internal.get_hist_density(28, 1)
FROM dual;

DBMS_STATS_INTERNAL.GET_HIST_DENSITY(28,1)
------------------------------------------
                                .000068833
 
GET_IDENTIFIER
Undocumented dbms_stats_internal.get_identifier(
qualified_name IN     VARCHAR2,
pos            IN OUT NUMBER)
RETURN VARCHAR2;
TBD
 
GET_IDX_TABPART
Undocumented dbms_stats_internal.get_idx_tabpart(
idxpartobj IN NUMBER,
tobjn      IN NUMBER)
RETURN NUMBER;
TBD
 
GET_INDEX_BLOCK_COUNT
Returns the block count for an index

The DBA_SEGMENTS query returns 128, the DBA_INDEXES query 32. The difference between them is 96 which is the value returned by this function. Tests on other indexes show a different pattern so it is difficult to quickly determine what the value returned by this function actually indicates.
dbms_stats_internal.get_index_block_count(
ownname        IN VARCHAR2,
indname        IN VARCHAR2,
partname       IN VARCHAR2 DEFAULT NULL,
subpartname    IN VARCHAR2 DEFAULT NULL,
ignore_offline IN VARCHAR2)
RETURN NUMBER;
conn sys@pdbdev as sysdba

SELECT SUM(blocks)
FROM dba_segments
WHERE owner = 'SH'
AND segment_name = 'SALES_PROD_BIX';

SELECT leaf_blocks
FROM dba_indexes
WHERE owner = 'SH'
AND index_name = 'SALES_PROD_BIX';

SELECT dbms_stats_internal.get_index_block_count('SH', 'SALES_PROD_BIX', NULL, NULL, 'YES')
FROM dual;
 
GET_INDEX_LOCK_FLAG
Undocumented dbms_stats_internal.get_index_lock_flag(objnum IN NUMBER)
RETURN NUMBER;
conn sys@pdbdev as sysdba

SELECT object_id
FROM dba_objects
WHERE object_name = 'PK_AIRPLANES';

SELECT dbms_stats_internal.get_index_lock_flag(73004)
FROM dual;

DBMS_STATS_INTERNAL.GET_INDEX_LOCK_FLAG(73004)
----------------------------------------------
                                             0
 
GET_INDEX_PART_NAME
Undocumented dbms_stats_internal.get_index_part_name(
tabowner    IN VARCHAR2,
tabname     IN VARCHAR2,
tabpartname IN VARCHAR2,
indowner    IN VARCHAR2,
indname     IN VARCHAR2)
RETURN VARCHAR2;
SELECT dbms_stats_internal.get_index_part_name('SYS', 'WRH$_LATCH_CHILDREN', 'WRH$_LATCH_CHILD_MXDB_MXSN', 'SYS', 'WRH$_LATCH_CHILDREN_PK') AS GIPN
FROM dual;

GIPN
-----------------------------
"WRH$_LATCH_CHILD_MXDB_MXSN"
 
GET_INDSTATS_DICT
Undocumented dbms_stats_internal.get_indstats_dict(
owner            IN VARCHAR2,
indname          IN VARCHAR2,
partname         IN VARCHAR2,
stattype         IN VARCHAR2,
vc_cascade_parts IN VARCHAR2,
gttses           IN BOOLEAN,
indstats_cur     IN REF CURSOR);
TBD
 
GET_IND_COLS
Undocumented dbms_stats_internal.get_ind_cols(ind_objn IN NUMBER)
RETURN CLOB;
SELECT obj#
FROM ind$
WHERE rownum < 11;

SELECT dbms_stats_internal.get_ind_cols(34)
FROM dual;

DBMS_STATS_INTERNAL.GET_IND_COLS(34)
------------------------------------
3.US#
 
GET_IND_COL_INFO
Returns the name(s) of column(s) in the index and the column count so that the column delimited list can be parsed dbms_stats_internal.get_ind_col_info(
ownname  IN  VARCHAR2,
indname  IN  VARCHAR2,
col_list OUT CLOB,
col_cnt  OUT NUMBER);
CREATE TABLE test (
test_id number,
test_str varchar2(30),
test_date date);

Table created.

CREATE INDEX ix_test ON test(test_id, test_date);

Index created.

DECLARE
 listOut CLOB;
 cntOut NUMBER;
BEGIN
  dbms_stats_internal.get_ind_col_info('SYS', 'IX_TEST', listOut, cntOut);
  dbms_output.put_line(listOut);
  dbms_output.put_line(cntOut);
END;
/
"TEST_ID","TEST_DATE"
2

PL/SQL procedure successfully completed.
 
GET_IND_SHARE_TYPE
Undocumented dbms_stats_internal.get_ind_share_type(obj_num IN NUMBER)
RETURN NUMBER;
SELECT dbms_stats_internal.get_ind_share_type(4)
FROM dual;

DBMS_STATS_INTERNAL.GET_IND_SHARE_TYPE(4)
-----------------------------------------
                                        0
 
GET_INTCOL
Appears to returns the position of the identified column in a table dbms_stats_internal.get_intcol(
owner   IN VARCHAR2
tabname IN VARCHAR2,
colname IN VARCHAR2)
RETURN BINARY_INTEGER;
conn sys@pdbdev as sysdba

SELECT dbms_stats_internal.get_intcol('UWCLASS', 'AIRPLANES', 'CUSTOMER_ID')
FROM dual;

DBMS_STATS_INTERNAL.GET_INTCOL('UWCLASS','AIRPLANES','CUSTOMER_ID')
-------------------------------------------------------------------
                                                                  3
 
GET_IOT_MAPPING_TABLE
Returns the name of a mapping table based on the name of an index organized table dbms_stats_internal.get_iot_mapping_table(
owner      IN VARCHAR2,
table_name IN VARCHAR2)
RETURN VARCHAR2;
conn sys@pdbdev as sysdba

CREATE TABLE uwclass.iot_tab (
x INT,
y INT,
CONSTRAINT pk_t_iot PRIMARY KEY(x))
ORGANIZATION INDEX
MAPPING TABLE;

SELECT dbms_stats_internal.get_iot_mapping_table('UWCLASS', 'IOT_TAB')
FROM dual;
 
GET_MBRC
Return the mutli-block read count otherwise visible in v$parameter dbms_stats_internal.get_mbrc RETURN BINARY_INTEGER;
SELECT dbms_stats_internal.get_mbrc
FROM dual;

  GET_MBRC
----------
       128
 
GET_MV_ENQUE
Undocumented dbms_stats_internal.get_mv_enque(ownname IN VACHAR2, objname IN VARCHAR2)
RETURN BINARY_INTEGER;
conn sys@pdbdev as sysdba

CREATE MATERIALIZED VIEW uwclass.mv_test
USING INDEX
REFRESH COMPLETE
AS SELECT s.srvr_id, COUNT(*)
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id
GROUP BY s.srvr_id;

SELECT dbms_stats_internal.get_mv_enque('UWCLASS', 'MV_TEST')
FROM dual;
 
GET_NUM_ROWS
Returns the number of rows in a table dbms_stats_internal.get_num_rows(objn IN NUMBER) RETURN NUMBER;
SELECT COUNT(*)
FROM tab$;

  COUNT(*)
----------
      2192


SELECT dbms_stats_internal.get_num_rows(4)
FROM dual;

DBMS_STATS_INTERNAL.GET_NUM_ROWS(4)
-----------------------------------
                               2192
 
GET_NUM_ROWS_AND_BLK_CNT
Undocumented dbms_stats_internal.get_num_rows_and_blk_cnt(
objn          IN  NUMBER,
session_stats IN  VARCHAR2,
numrows       OUT NUMBER,
numblks       OUT NUMBER);
TBD
 
GET_OBJLIST_TEXT
Undocumented dbms_stats_internal.get_objlist_text(objlist_tabq IN VARCHAR2) RETURN VARCHAR2;
TDB
 
GET_OBJNUM
Returns the object identifier for an object

Overload 1

The two queries shown return the same value
dbms_stats_internal.get_objnum(
ownname  IN VARCHAR2,
objname  IN VARCHAR2,
partname IN VARCHAR2 DEFAULT NULL,
subpname IN VARCHAR2 DEFAULT NULL,
objtype  IN VARCHAR2)
RETURN NUMBER;
conn sys@pdbdev as sysdba

SELECT object_id
FROM dba_objects
WHERE owner = 'UWCLASS'
AND object_name = 'AIRPLANES'
AND object_type = 'TABLE';

SELECT dbms_stats_internal.get_objnum('UWCLASS', 'AIRPLANES', NULL, NULL, 'TABLE')
FROM dual;
Overload 2 dbms_stats_internal.get_objnum(
ownname  IN  VARCHAR2,
objname  IN  VARCHAR2,
partname IN  VARCHAR2 DEFAULT NULL,
subpname IN  VARCHAR2 DEFAULT NULL,
objtype  IN  VARCHAR2,
stype    OUT NUMBER);
RETURN NUMBER;
TBD
 
GET_OBJ_NAME_USER_NAME
Returns the object owner and object name corresponding to an object number dbms_stats_internal.get_obj_name_user_name(
obj_num  IN  NUMBER,
owner    OUT VARCHAR2,
obj_name OUT VARCHAR2);
DECLARE
 ownr VARCHAR2(30);
 onam VARCHAR2(30);
BEGIN
  dbms_stats_internal.get_obj_name_user_name(4, ownr, onam);
  dbms_output.put_line(ownr);
  dbms_output.put_line(onam);
END;
/
SYS
TAB$
 
GET_OLDEST_HISTORY_TIME
Undocumented dbms_stats_internal.get_oldest_history_time RETURN TIMESTAMP WITH TIME ZONE;
SELECT dbms_stats_internal.get_oldest_history_time
FROM dual;

GET_OLDEST_HISTORY_TIME
--------------------------------------
01-APR-21 10.06.08.974367000 PM +00:00


/* value returned does not correspond with that from either of the following two queries */
SELECT MIN(begin_interval_time)
FROM dba_hist_snapshot;

MIN(BEGIN_INTERVAL_TIME)
-------------------------
24-APR-21 11.58.43.137 PM


SELECT MIN(last_analyzed)
FROM dba_all_tables;

MIN(LAST_ANALYZED)
--------------------
03-NOV-2020 09:35:20
 
GET_OSTZHM (new 21c)
Undocumented dbms_stats_internal.get_ostzhm RETURN VARCHAR2;
SELECT dbms_stats_internal.get_ostzhm
FROM dual;

GET_OSTZHM
-----------
+00:00
 
GET_OWNER_ID (new 21c)
Undocumented dbms_stats_internal.get_owner_id(ownername IN VARCHAR2) RETURN NUMBER;
SELECT dbms_stats_internal.get_owner_id('C##UWCLASS')
FROM dual;

DBMS_STATS_INTERNAL.GET_OWNER_ID('C##UWCLASS')
----------------------------------------------
                                           111
 
GET_PARAM
Returns the value of a previously set DBMS_STATS global preference dbms_stats_internal.get_param(
pname IN  VARCHAR2,
pval  OUT VARCHAR2);
DECLARE
 retVal VARCHAR2(30);
BEGIN
  dbms_stats.set_global_prefs('ESTIMATE_PERCENT','5');

  dbms_stats_internal.get_param('ESTIMATE_PERCENT', retVal);
  dbms_output.put_line('Estimate Pct: ' || retVal);
END;
/
Estimate Pct: 5

PL/SQL procedure successfully completed.
 
GET_PARAMETER_VAL
Undocumented dbms_stats_internal.get_parameter_val(pname IN VARCHAR2) RETURN VARCHAR2;
SELECT dbms_stats_internal.get_parameter_val('METHOD_OPT')
FROM dual;

-- have tried numerous strings and can not successfully return anything other than NULL
 
GET_PARAM_DEFAULT
  dbms_stats_internal.get_param_default(
pname     IN  VARCHAR2,
pvalnum   OUT NUMBER,
pval      OUT VARCHAR2,
isdefault OUT NUMBER);
DECLARE
 p1 NUMBER;
 p2 VARCHAR2(30);
 p3 NUMBER;
BEGIN
  dbms_stats_internal.get_param_default('DEGREE', p1, p2, p3);
  dbms_output.put_line('pValNum: ' || TO_CHAR(p1));
  dbms_output.put_line('pVal: ' || p2);
  dbms_output.put_line('isDefault: ' || TO_CHAR(p3));
END;
/
pValNum:
pVal: NULL
isDefault: 1

PL/SQL procedure successfully completed.
 
GET_PARAM_NUMVAL
Undocumented dbms_stats_internal.get_param_numval(
pname   IN  VARCHAR2,
pval    OUT VARCHAR2,
pvalnum OUT NUMBER);
TBD
 
GET_PARAM_PROP
Undocumented dbms_stats_internal.get_param_prop(
pname     IN  VARCHAR2,
exist     OUT BOOLEAN,
isdefault OUT NUMBER);
DECLARE
 l_exist BOOLEAN;
 l_isdef NUMBER;
BEGIN
  dbms_stats_internal.get_param_prop('METHOD_OPT', l_exist, l_isdef);
  IF l_exist THEN
    dbms_output.put_line('Exists');
  ELSE
    dbms_output.put_line('Does Not Exists');
  END IF;
  dbms_output.put_line(l_isdef);
END;
/
Exists
1
 
GET_PARTN_LOCK_FLAG
Undocumented dbms_stats_internal.get_partn_lock_flag(
objn      IN NUMBER,
objtyp    IN NUMBER,
idxobjnum IN NUMBER)
RETURN NUMBER;
TBD
 
GET_PART_COLS
Undocumented dbms_stats_internal.get_part_cols(
ownname IN VARCHAR2,
tabname IN VARCHAR2,
objtype IN VARCHAR2);
RETURN dbms_stats_internal.identab;
-- not sure why this always returns 0

conn sh/sh@pdbdev

DECLARE
 retVal dbms_stats_internal.identab;
BEGIN
  retVal := dbms_stats_internal.get_part_cols('SH', 'SALES', 'TABLE');

  dbms_output.put_line(retVal.COUNT);
END;
/
 
GET_PREFS
Undocumented dbms_stats_internal.get_prefs(
pname_p IN  VARCHAR2,
pvalue  OUT VARCHAR2,
ownname IN  VARCHAR2,
tabname IN  VARCHAR2,
tobjn   IN  NUMBER);
TBD
 
GET_PREV_PART
Returns the name of the previous partition of a range partitioned table dbms_stats_internal.get_prev_part(
ownname   IN VARCHAR2,
tabname   IN VARCHAR2,
partname  IN VARCHAR2,
spartname IN VARCHAR2)
RETURN VARCHAR2;
conn sys@pdbdev as sysdba

SELECT partition_name
FROM dba_tab_partitions
WHERE table_name = 'SALES'
ORDER BY partition_name;

SELECT dbms_stats_internal.get_prev_part('SH', 'SALES', 'SALES_Q2_2002', NULL)
FROM dual;
 
GET_PROCRATE_ID
Undocumented dbms_stats_internal.get_procrate_id(statname IN VARCHAR2) RETURN NUMBER;
TBD
 
GET_QA_CONTROL (new 21c)
Undocumented dbms_stats_internal.get_qa_control RETURN NUMBER;
SELECT dbms_stats_internal.get_qa_control
FROM dual;

GET_QA_CONTROL
--------------
             0
 
GET_SC_1201
  dbms_stats_internal.get_sc_1201 RETURN NUMBER;
SELECT dbms_stats_internal.get_sc_1201
FROM dual;

GET_SC_1201
-----------
          1
 
GET_SC_1202
Undocumented dbms_stats_internal.get_sc_1202 RETURN NUMBER;
SELECT dbms_stats_internal.get_sc_1202
FROM dual;

GET_SC_1202
-----------
          2
 
GET_SC_MAX
Undocumented dbms_stats_internal.get_sc_max RETURN NUMBER;
SELECT dbms_stats_internal.get_sc_max
FROM dual;

GET_SC_MAX
----------
    100000
 
GET_STALE_REASON
Undocumented dbms_stats_internal.get_stale_reason(reason IN NUMBER)
RETURN VARCHAR2;
SELECT dbms_stats_internal.get_stale_reason(1)
FROM dual;

DBMS_STATS_INTERNAL.GET_STALE_REASON(1)
----------------------------------------
<staleness reason><reason>ALL</reason>
</staleness reason>



SELECT dbms_stats_internal.get_stale_reason(2)
FROM dual;

DBMS_STATS_INTERNAL.GET_STALE_REASON(2)
----------------------------------------
<staleness reason><reason>EMPTY</reason>
</staleness reason>



SELECT dbms_stats_internal.get_stale_reason(0)
FROM dual;

DBMS_STATS_INTERNAL.GET_STALE_REASON(0)
----------------------------------------



SELECT dbms_stats_internal.get_stale_reason(4)
FROM dual;

DBMS_STATS_INTERNAL.GET_STALE_REASON(4)
----------------------------------------
<staleness reason><reason>STALE</reason>
</staleness reason>


SELECT dbms_stats_internal.get_stale_reason(99)
FROM dual;

DBMS_STATS_INTERNAL.GET_STALE_REASON(99)
----------------------------------------
<staleness reason>
  <reason>ALL</reason>
  <reason>EMPTY</reason>
  <reason>HIST</reason>
  <reason>EXTN</reason>
</staleness reason>
 
GET_STAT_EXTENSION
Undocumented dbms_stats_internal.get_stat_extension(
owner   IN VARCHAR2,
tab     IN VARCHAR2,
xtnname IN VARCHAR2)
RETURN CLOB;
TBD
 
GET_SYNOPSIS_DEGREE
Undocumented dbms_stats_internal.get_synopsis_degree(
owner    IN VARCHAR2,
tabname  IN VARCHAR2,
tobjn    IN NUMBER,
groupnum IN NUMBER,
degree   IN NUMBER)
RETURN NUMBER;
TBD
 
GET_SYNOPSIS_GROUP_NUM
Undocumented dbms_stats_internal.get_synopsis_group_num(
tobjn IN NUMBER,
fobjn IN NUMBER)
RETURN NUMBER;
TBD
 
GET_TABLE_BLOCK_COUNT
Returns the number of blocks associated with a segment dbms_stats_internal.get_table_block_count(
ownname        IN VARCHAR2,
tabname        IN VARCHAR2,
partname       IN VARCHAR2 DEFAULT NULL,
subpartname    IN VARCHAR2 DEFAULT NULL,
ignore_offline IN VARCHAR2)
RETURN NUMBER;
conn sys@pdbdev as sysdba

SELECT dbms_stats_internal.get_table_block_count('SH', 'SALES', NULL, NULL, NULL)
FROM dual;
 
GET_TABLE_DEGREE
Returns the degree of parallelism associated with a table dbms_stats_internal.get_table_degree(
owner   IN VARCHAR2,
tabname IN VARCHAR2)
RETURN NUMBER;
conn sys@pdbdev as sysdba

SELECT dbms_stats_internal.get_table_degree('UWCLASS', 'AIRPLANES')
FROM dual;

DBMS_STATS_INTERNAL.GET_TABLE_DEGREE('UWCLASS','AIRPLANES')
-----------------------------------------------------------
                                                          1


ALTER TABLE uwclass.airplanes PARALLEL 4;

SELECT dbms_stats_internal.get_table_degree('UWCLASS', 'AIRPLANES')
FROM dual;

DBMS_STATS_INTERNAL.GET_TABLE_DEGREE('UWCLASS','AIRPLANES')
-----------------------------------------------------------
                                                          4
 
GET_TABLE_PREFS
Undocumented dbms_stats_internal.get_table_prefs(
pname_p  IN  VARCHAR2,
pvalue   OUT VARCHAR2,
ownname  IN  VARCHAR2,
tabname  IN  VARCHAR2,
tobjn    IN  NUMBER);
TBD
 
GET_TABLE_PROPERTY
Returns the value of the PROPERTY column in TAB$ for a table dbms_stats_internal.get_table_property(objn IN NUMBER)
RETURN NUMBER;
conn sys@pdbdev as sysdba

SELECT object_id, data_object_id
FROM dba_objects
WHERE object_name = 'AIRPLANES';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     73003          73003


SELECT dbms_stats_internal.get_table_property(73003)
FROM dual;

DBMS_STATS_INTERNAL.GET_TABLE_PROPERTY(73003)
---------------------------------------------
                                    536870912
 
GET_TABLE_STATS_SIMPLE
Returns the most basic table stats for the specified table dbms_stats_internal.get_table_stats_simple(
objnum          IN  NUMBER,
objtype         IN  NUMBER,
gttses          IN  BOOLEAN,
nrows           OUT NUMBER,
nblks           OUT NUMBER,
last_analyzed_d OUT DATE);
conn sys@pdbdev as sysdba

SELECT object_id
FROM dba_objects
WHERE object_name = 'AIRPLANES';

 OBJECT_ID
----------
     73003


DECLARE
 lNRows NUMBER;
 lNBlks NUMBER;
 lAnlDt DATE;
BEGIN
  dbms_stats_internal.get_table_stats_simple(73003, 2, FALSE, lNRows, lNBlks, lAnlDt);

  dbms_output.put_line('Rows: ' || TO_CHAR(lNRows));
  dbms_output.put_line('Blocks: ' || TO_CHAR(lNBlks));
  dbms_output.put_line('Last Analyzed: ' || TO_CHAR(lAnlDt));
END;
/
Rows: 250000
Blocks: 1064
Last Analyzed: 10-MAY-2020 12:45:26

PL/SQL procedure successfully completed.
 
GET_TAB_NUM_SEGMENTS
Undocumented dbms_stats_internal.get_tab_num_segments(tobjn IN NUMBER)
RETURN NUMBER;
SELECT dbms_stats_internal.get_tab_num_segments(73003)
FROM dual;
 
GET_TAB_PROPERTY
Undocumented

Overload 1
dbms_stats_internal.get_tab_property(owner IN VARCHAR2, tabname IN VARCHAR2)
RETURN NUMBER;
SELECT dbms_stats_internal.get_tab_property('UWCLASS', 'SERVERS')
FROM dual;

DBMS_STATS_INTERNAL.GET_TAB_PROPERTY('UWCLASS','SERVERS')
---------------------------------------------------------
                                                536870912
Overload 2 dbms_stats_internal.get_tab_property(tobjn IN NUMBER) RETURN NUMBER;
SELECT object_id
FROM dba_objects
WHERE object_name = 'AIRPLANES';

 OBJECT_ID
----------
     73003


SELECT dbms_stats_internal.get_tab_property(73003)
FROM dual;

DBMS_STATS_INTERNAL.GET_TAB_PROPERTY('UWCLASS','SERVERS')
---------------------------------------------------------
                                                536870912
 
GET_TAB_PROPERTY_AND_FLAGS
Undocumented dbms_stats_internal.get_tab_property_and_flags(
tobjn    IN  NUMBER,
property OUT NUMBER,
flags    OUT NUMBER);
TBD
 
GET_TAB_SHARE_TYPE
Undocumented dbms_stats_internal.get_tab_share_type(
obj_num IN NUMBER,
ownname IN VARCHAR2,
tabname IN VARCHAR2)
RETURN NUMBER;
TBD
 
GET_TAB_SHARE_TYPE_VIEW
Undocumented dbms_stats_internal.get_tab_share_type_view(
oflags IN NUMBER,
tprop  IN NUMBER)
RETURN NUMBER;
TBD
 
GET_USER_NUM
Returns the numeric identifier for a named user dbms_stats_internal.get_user_num(username IN VARCHAR2) RETURN NUMBER;
SELECT dbms_stats_internal.get_user_num('XDB')
FROM dual;

DBMS_STATS_INTERNAL.GET_USER_NUM('XDB')
---------------------------------------
                                     65
 
GRANT_CONC_PRIV_TO_USER
Undocumented dbms_stats_internal.grant_conc_priv_to_user(uname IN VARCHAR2);
exec dbms_stats_internal.grant_conc_priv_to_user('XDB');

PL/SQL procedure successfully completed.

exec dbms_stats_internal.revoke_conc_priv_from_user('XDB');

PL/SQL procedure successfully completed.
 
HYBRID_HIST_ENABLED
Undocumented dbms_stats_internal.hybrid_hist_enabled(
auto_sample IN BOOLEAN,
incremental IN BOOLEAN);
RETURN BOOLEAN;
BEGIN
  IF dbms_stats_internal.hybrid_hist_enabled(TRUE, TRUE) THEN
    dbms_output.put_line('F');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
F

PL/SQL procedure successfully completed.
 
IMPORT_COL_USAGE_FOR_DIR
Undocumented dbms_stats_internal.import_col_usage_for_dir(stattabq IN VARCHAR2);
TBD
 
IMPORT_MON_INFO
Undocumented dbms_stats_internal.import_mon_info(
stattabq     IN VARCHAR2,
objlist_tabq IN VARCHAR2);
TBD
 
IMPORT_STATS_HISTORY
Undocumented dbms_stats_internal.import_stats_history(
stattabq     IN VARCHAR2,
objlist_tabq IN VARCHAR2);
TBD
 
IMPORT_SYNOPSES_STATS
Undocumented dbms_stats_internal.import_synopses_stats(
owner    IN VARCHAR2,
tabname  IN VARCHAR2,
partname IN VARCHAR2,
fromtab  IN VARCHAR2,
fromid   IN VARCHAR2,
statown  IN VARCHAR2)
RETURN dbms_stats_internal.numtab;
TBD
 
INCREMENTAL_INTERNAL_ENABLED (new 21c)
Undocumented dbms_stats_internal.incremental_internal_enabled RETURN BOOLEAN;
BEGIN
  IF dbms_stats_internal.incremental_internal_enabled THEN
    dbms_output.put_line('Enabled');
  ELSE
    dbms_output.put_line('Not Enabled');
  END IF;
END;
/
Enabled

PL/SQL procedure successfully completed.
 
INDEX_MAX_KEYSIZE_OK
Undocumented dbms_stats_internal.index_max_keysize_ok(
owner   IN VARCHAR2,
indname IN VARCHAR2)
RETURN BOOLEAN;
conn sys@pdbdev as sysdba

BEGIN
  IF dbms_stats_internal.index_max_keysize_ok('UWCLASS', 'PK_AIRPLANE') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
True

PL/SQL procedure successfully completed.
 
INDHASGLOBALSTATS
Returns TRUE if an index has global stats collected dbms_stats_internal.indHasGlobalStats(bobjnum IN NUMBER) RETURN BOOLEAN;
conn sys@pdbdev as sysdba

SELECT object_id
FROM dba_objects
WHERE object_name = 'PK_SERVERS';

 OBJECT_ID
----------
     73006


BEGIN
  IF dbms_stats_internal.indhasglobalstats(73006) THEN
    dbms_output.put_line('Index Has Global Stats');
  ELSE
    dbms_output.put_line('Index Does Not Have Global Stats');
  END IF;
END;
/
Index Has Global Stats

PL/SQL procedure successfully completed.
 
INDPARTSHAVESTATS
Undocumented dbms_stats_internal.indPartsHaveStats(
owner   IN VARCHAR2,
indname IN VARCHAR2,
pname   IN VARCHAR2,
bobjnum IN NUMBER);
TBD
 
INIT_ID_MAP_TAB
Undocumented dbms_stats_internal.init_id_map_tab;
exec dbms_stats_internal.init_id_map_tab;

PL/SQL procedure successfully completed.
 
INIT_OBJ_LIST_TAB
Undocumented dbms_stats_internal.init_obj_list_tab;
exec dbms_stats_internal.init_obj_list_tab;

PL/SQL procedure successfully completed
 
INIT_STATS_ARG_TAB
Undocumented: But does not create an object dbms_stats_internal.init_stats_arg_tab;
exec dbms_stats_internal.init_stats_arg_tab;

PL/SQL procedure successfully completed
 
INSERT_ADVISOR_FILTER_OBJ
Undocumented dbms_stats_internal.insert_advisor_filter_obj(
task_id     IN NUMBER,
ownname     IN VARCHAR2,
objname     IN VARCHAR2,
def_include IN BOOLEAN);
exec dbms_stats_internal.insert_advisor_filter_obj(350, 'SYS', 'TAB$', TRUE);
 
INSERT_INTO_USTATS
Undocumented dbms_stats_internal.insert_into_ustats(
owner   IN VARCHAR2,
indname IN VARCHAR2,
pname   IN VARCHAR2,
bobjnum IN NUMBER);
TBD
 
INSERT_ONLY
Undocumented dbms_stats_internal.insert_only(objn IN NUMBER) RETURN BOOLEAN;
TBD
 
INS_SESSION_HG_STATS
Undocumented dbms_stats_internal.ins_session_hg_stats(cstats IN dbms_stats_internal.coltab);
TBD
 
INT_ASSERT
Undocumented dbms_stats_internal.int_assert(
assert_cond IN BOOLEAN,
proc_name   IN VARCHAR2,
err_str     IN VARCHAR2,
soft_assert IN BOOLEAN,
flags       IN BINARY_INTEGER);
TBD
 
ISSHARDEDCATALOG
Returns TRUE if the catalog is sharded dbms_stats_internal.isShardedCatalog RETURN BOOLEAN;
BEGIN
  IF dbms_stats_internal.isShardedCatalog THEN
    dbms_output.put_line('The Catalog Is Sharded');
  ELSE
    dbms_output.put_line('The Catalog Is NOT Sharded');
  END IF;
END;
/
The Catalog Is NOT Sharded
 
IS_DUPLICATED_OPR
Undocumented dbms_stats_internal.is_duplicated_opr(objnum IN NUMBER) RETURN BOOLEAN;
TBD
 
IS_HADOOP_TABLE
Returns TRUE if the table is a hadoop table, otherwise FALSE dbms_stats_internal.is_hadoop_table(tobjn IN NUMBER) RETURN BOOLEAN;
BEGIN
  IF dbms_stats_internal.is_hadoop_table(1) THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
F

PL/SQL procedure successfully completed.
 
IS_INDEX_UNIQUE
Returns 1 if an index is unique, otherwise 0 dbms_stats_internal.is_index_unique(
bobjn IN NUMBER,
iobjn IN NUMBER,
icols IN NUMBER)
RETURN NUMBER;
SELECT object_id
FROM dba_objects
WHERE object_name = 'SERVERS';

 OBJECT_ID
----------
     73005


SELECT object_id
FROM dba_objects
WHERE object_name = 'PK_SERVERS';

 OBJECT_ID
----------
     73006


SELECT column_position
FROM dba_ind_columns
WHERE index_owner = 'UWCLASS'
AND index_name = 'PK_SERVERS';

COLUMN_POSITION
---------------
              1


SELECT dbms_stats_internal.is_index_unique(73005, 73006, 1)
FROM dual;

DBMS_STATS_INTERNAL.IS_INDEX_UNIQUE(73005,73006,1)
--------------------------------------------------
                                              7522
 
IS_MV_TABLE_BUSY
Appears to return TRUE if a materialized view is busy, for example in the process of being refreshed: Otherwise returns FALSE dbms_stats_internal.is_mv_table_busy(
own_name   IN VARCHAR2,
obj_name   IN VARCHAR2,
start_time IN DATE)
RETURN BOOLEAN;
conn sys@pdbdev as sysdba

CREATE MATERIALIZED VIEW uwclass.mv_test
USING INDEX
REFRESH COMPLETE
AS SELECT s.srvr_id, COUNT(*)
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id
GROUP BY s.srvr_id;

Materialized view created.

BEGIN
  IF dbms_stats_internal.is_mv_table_busy('UWCLASS', 'MV_TEST', SYSDATE-1/1440) THEN
    dbms_output.put_line('Busy');
  ELSE
    dbms_output.put_line('Not Busy');
  END IF;
END;
/
Busy

PL/SQL procedure successfully completed.
 
IS_ORACLE_OWNED
Returns 1 if the tablespace is "owned by Oracle" which appears to mean "is SYSTEM" dbms_stats_internal.is_oracle_owned(ownname IN VARCHAR2) RETURN NUMBER;
SELECT dbms_stats_internal.is_oracle_owned('SYSTEM')
FROM dual;

SELECT dbms_stats_internal.is_oracle_owned('SYSTEM')
FROM dual;

DBMS_STATS_INTERNAL.IS_ORACLE_OWNED('SYSTEM')
---------------------------------------------
                                            1


SELECT dbms_stats_internal.is_oracle_owned('AUDIT_TBSP')
FROM dual;

DBMS_STATS_INTERNAL.IS_ORACLE_OWNED('AUDIT_TBSP')
-------------------------------------------------
                                                0
 
IS_PARAM_VALUE_DEFAULT
Undocumented dbms_stats_internal.is_param_value_default(
pnameu IN VARCHAR2,
pvalu  IN VARCHAR2)
RETURN VARCHAR2;
SELECT dbms_stats_internal.is_param_value_default('DEFAULT_STALE_PERCENT', 1)
FROM dual;

--unable to find values that produce a return string but it does execute
 
IS_PARTITIONED_TAB
Returns TRUE if the table is partitioned

Overload 1
dbms_stats_internal.is_partitioned_tab(
owner   IN VARCHAR2,
tabname IN VARCHAR2)
RETURN BOOLEAN;
conn sys@pdbdev as sysdba

BEGIN
  IF dbms_stats_internal.is_partitioned_tab('UWCLASS', 'AIRPLANES') THEN
    dbms_output.put_line('Airplanes Is Partitioned');
  ELSE
    dbms_output.put_line('Airplanes Is Not Partitioned');
  END IF;
  IF dbms_stats_internal.is_partitioned_tab('SH', 'SALES') THEN
    dbms_output.put_line('Sales Is Partitioned');
  ELSE
  dbms_output.put_line('Sales Is Not Partitioned');
  END IF;
END;
/
Airplanes Is Not Partitioned
Sales Is Partitioned

PL/SQL procedure successfully completed.
Overload 2 dbms_stats_internal.is_partitioned_tab(tobjn IN NUMBER) RETURN BOOLEAN;
TBD
 
IS_PART_DEFAULT
Undocumented dbms_stats_internal.is_part_default(
owner     IN  VARCHAR2,
objname   IN  VARCHAR2,
objtype   IN  VARCHAR2,
partname  IN  VARCHAR2,
isdefault OUT BOOLEAN;
conn sys@pdbdev as sysdba

DECLARE
 retVal BOOLEAN;
BEGIN
  dbms_stats_internal.is_part_default('SH', 'SALES', 'TABLE', 'SALES_Q2_2002', retVal);

  IF retVal THEN
    dbms_output.put_line('Default');
  ELSE
    dbms_output.put_line('Not Default');
  END IF;
END;
/
 
IS_PART_TYP_SAME
Undocumented dbms_stats_internal.is_part_typ_same(
owner   IN VARCHAR2,
tabname IN VARCHAR2,
part1   IN VARCHAR2,
part2   IN VARCHAR2)
RETURN BOOLEAN;
conn sys@pdbdev as sysdba

BEGIN
  IF dbms_stats_internal.is_part_typ_same('SH', 'SALES', 'SALES_Q2_2001', 'SALES_Q2_2002') THEN
    dbms_output.put_line('Same');
  ELSE
    dbms_output.put_line('Not The Same');
  END IF;
END;
/
 
IS_QA_MODE (new 21c)
Undocumented dbms_stats_internal.is_qa_mode RETURN BOOLEAN;
BEGIN
  IF dbms_stats_internal.is_qa_mode THEN
    dbms_output.put_line('In QA Mode');
  ELSE
    dbms_output.put_line('Not In QA Mode Which Is The Default');
  END IF;
END;
/
Not In QA Mode Which Is The Default

PL/SQL procedure successfully completed.
 
IS_RES_MAN_ON
Returns TRUE if resource management is enabled, else FALSE dbms_stats_internal.is_res_man_on RETURN BOOLEAN;
BEGIN
  IF dbms_stats_internal.is_res_man_on THEN
    dbms_output.put_line('Resource Management Enabled');
  ELSE
    dbms_output.put_line('Resource Management Not Enabled');
  END IF;
END;
/
Resource Management Not Enabled

PL/SQL procedure successfully completed.
 
IS_SENSITIVE_COL (new 21c)
Returns TRUE if the columns is identified as a "sensitive" column, else FALSE dbms_stats_internal.is_sensitive_col(
tobjn    IN NUMBER,
col_name IN VARCHAR2)
RETURN BOOLEAN;
SELECT object_id
FROM dba_objects
WHERE object_name = 'SERVERS';

 OBJECT_ID
----------
     84134


BEGIN
  IF dbms_stats_internal.is_sensitive_col(84134, 'NETWORK_ID') THEN
    dbms_output.put_line('This column contains sensitive data');
  ELSE
    dbms_output.put_line('This column does not contain sensitive data');
  END IF;
END;
/
This column does not contain sensitive data

PL/SQL procedure successfully completed.
 
IS_STALE
Undocumented dbms_stats_internal.is_stale(
tobj         IN NUMBER,
rows_changed IN NUMBER,
rowcnt       IN NUMBER)
RETURN VARCHAR2;
TBD
 
IS_STALE_IN_CONTAINER
Undocumented dbms_stats_internal.is_stale_in_container(
ownname    IN VARCHAR2,
tabname    IN VARCHAR2,
clist_hist IN sys.dbms_stats_internal.chtab)
RETURN NUMBER;
TBD
 
IS_STALE_STRICT (new 21c)
Undocumented dbms_stats_internal.is_stale_strict(
tobjn        IN NUMBER,
ownname      IN VARCHAR2,
tabname      IN VARCHAR2,
rows_changed IN NUMBER,
rowcnt       IN NUMBER,
dmlflags     IN NUMBER,
fobjn        IN NUMBER,
check_cu     IN VARCHAR2)
RETURN NUMBER;
TBD
 
IS_TABLE_EMPTY
Returns TRUE if the table is empty, else FALSE dbms_stats_internal.is_table_empty(
ownu IN VARCHAR2,
tabu IN VARCHAR2)
RETURN BOOLEAN;
BEGIN
  IF dbms_stats_internal.is_table_empty('SYS', 'OBJ$') THEN
    dbms_output.put_line('Is Empty');
  ELSE
    dbms_output.put_line('Not Empty');
  END IF;
END;
/
Not Empty

PL/SQL procedure successfully completed.
 
IS_TEMP_TAB
Returns TRUE if the object is a global temporary table dbms_stats_internal.is_temp_tab(
owner   IN VARCHAR2,
tabname IN VARCHAR2)
RETURN BOOLEAN;
conn sys@pdbdev as sysdba

CREATE GLOBAL TEMPORARY TABLE uwclass.gtt (
zip_code   VARCHAR2(5),
by_user    VARCHAR2(30),
entry_date DATE);

BEGIN
  IF dbms_stats_internal.is_temp_tab('UWCLASS', 'SERVERS') THEN
    dbms_output.put_line('Servers is a temporary table');
  ELSE
    dbms_output.put_line('Servers is not a temporary table');
  END IF;

  IF dbms_stats_internal.is_temp_tab('UWCLASS', 'GTT') THEN
    dbms_output.put_line('gtt is a temporary table');
  ELSE
    dbms_output.put_line('gtt is not a temporary table');
  END IF;
END;
/
 
IS_URGENT_ERROR
Undocumented
Overload 1
dbms_stats_internal.is_urgent_error(shutdown OUT BOOLEAN) RETURN BOOLEAN;
DECLARE
 outVal BOOLEAN;
BEGIN
  IF dbms_stats_internal.is_urgent_error(outVal) THEN
    dbms_output.put_line('Is');
  ELSE
    dbms_output.put_line('Is Not');
  END IF;

  IF outVal THEN
    dbms_output.put_line('Outval Is True');
  ELSE
    dbms_output.put_line('Outval Is False');
  END IF;
END;
/
Is Not
Outval Is False

PL/SQL procedure successfully completed.
Overload 2 dbms_stats_internal.is_urgent_error RETURN BOOLEAN;
BEGIN
  IF dbms_stats_internal.is_urgent_error THEN
    dbms_output.put_line('Is');
  ELSE
    dbms_output.put_line('Is Not');
  END IF;
END;
/
Is Not

PL/SQL procedure successfully completed.
 
JOB_CLASS_EXISTS
Returns TRUE if the specified job class exists dbms_stats_internal.job_class_exists(job_class IN VARCHAR2) RETURN BOOLEAN;
SELECT DISTINCT job_class
FROM dba_scheduler_jobs
ORDER BY 1;

JOB_CLASS
----------------------------
DEFAULT_JOB_CLASS
ORA$AUTOTASK_JOB_CLASS
SCHED$_LOG_ON_ERRORS_CLASS
XMLDB_NFS_JOBCLASS



DECLARE
 jc VARCHAR2(30) := 'DEFAULT_JOB_CLASS';
BEGIN
  IF dbms_stats_internal.job_class_exists(jc) THEN
    dbms_output.put_line('Job class ' || jc || ' exists' );
  ELSE
    dbms_output.put_line('Job class ' || jc || ' does not exist' );
  END IF;
END;
/
Job class DEFAULT_JOB_CLASS exists

PL/SQL procedure successfully completed.
 
MANAGE_COL_TRACKING_LEVEL_JOB
Undocumented dbms_stats_internal.manage_col_tracking_level_job(
level      IN NUMBER,
time_limit IN BINARY_INTEGER,
create_job IN BOOLEAN);
TBD
 
MERGE_HLL_FROM_DISKS
Undocumented dbms_stats_internal.merge_hll_from_disks(
g_objn  IN     NUMBER,
t_objn  IN     NUMBER,
clist   IN OUT sys.ctab,
curtime IN     DATE);
TBD
 
MIN_NUM
Appears to be a wrapper around the LEAST function dbms_stats_internal.min_num(
val1 IN NUMBER,
val2 IN NUMBER)
RETURN NUMBER;
SELECT dbms_stats_internal.min_num(6,4)
FROM dual;
 
MONITORING_MAN_SIGNAL_SAFEMODE
Undocumented dbms_stats_internal.monitoring_man_signal_safemode(
aname     IN VARCHAR2,
sender    IN VARCHAR2,
committee IN BOOLEAN);
TBD
 
NOTES_STRING_TO_XML (new 21c)
Undocumented dbms_stats_internal.notes_string_to_xml(notes_string IN VARCHAR2) RETURN CLOB;
SELECT dbms_stats_internal.notes_string_to_xml('Welcome To Morgan''s Library')
FROM dual;

DBMS_STATS_INTERNAL.NOTES_STRING_TO_XML('WELCOMETOMORGAN''SLIBRARY')
--------------------------------------------------------------------------------
<notes>Welcome To Morgan's Library</notes>
 
OBJECT_EXISTS
Returns TRUE if the object exists dbms_stats_internal.object_exists(
owner   IN VARCHAR2,
objname IN VARCHAR2,
objtype IN BINARY_INTEGER)
RETURN BOOLEAN;
conn sys@pdbdev as sysdba

BEGIN
  IF dbms_stats_internal.object_exists('UWCLASS', 'SERVERS', 2) THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/
 
OPEN_ADVISOR_ACTION_OBJ_CUR
Undocumented dbms_stats_internal.open_advisor_action_obj_cur(
task_id        IN     NUMBER,
exec_name      IN     VARCHAR2,
action_all     IN     VARCHAR2,
valid_rules    IN     dbmsstatnumtab,
valid_findings IN     dbmsstatnumtab,
adv_cur        IN OUT REF CURSOR);
TBD
 
OPEN_ADVISOR_FINDING_OBJ_CUR
Undocumented dbms_stats_internal.open_advisor_finding_obj_cur(
task_id    IN     NUMBER,
exec_name  IN     VARCHAR2,
rule_id    IN     NUMBER,
finding_id IN     NUMBER,
adv_cur    IN OUT REF CURSOR);
TBD
 
OPEN_ALL_EMPTY_OBJS_CUR
Open All Empty Objects Cursor dbms_stats_internal.open_all_empty_objs_cur(
ownname      IN  VARCHAR2,
gather_sys   IN  VARCHAR2,
gather_temp  IN  VARCHAR2,
gather_fixed IN  VARCHAR2,
ign_lckd_obj IN  NUMBER,
granularity  IN  VARCHAR2,
get_index    IN  VARCHAR2,
cur          OUT REF CURSOR);
TBD
 
OPEN_ALL_OBJECTS_CUR
Open All Objects Cursor dbms_stats_internal.open_all_objects_cur(
ownname      IN  VARCHAR2,
gather_sys   IN  VARCHAR2,
gather_temp  IN  VARCHAR2,
gather_fixed IN  VARCHAR2,
gather_ext   IN  VARCHAR2,
ign_lckd_obj IN  NUMBER,
get_index    IN  VARCHAR2,
gather       IN  VARCHAR2,
gather_cot   IN  VARCHAR2,
cur          OUT REF CURSOR);
TBD
 
OPEN_ALL_STALE_OBJS_CUR
Open Stale Objects Cursor dbms_stats_internal.open_all_stale_objs_cur(
ownname      IN  VARCHAR2,
gather_sys   IN  VARCHAR2,
gather_fixed IN  VARCHAR2,
ign_lckd_obj IN  NUMBER,
granularity  IN  VARCHAR2,
cur          OUT REF CURSOR);
DECLARE
 refcur SYS_REFCURSOR;
BEGIN
  dbms_stats_internal.open_all_stale_objs_cur(USER, 'TRUE', 'TRUE', 0, 'AUTO', refcur);
END;
/

PL/SQL procedure successfully completed.
 
OPEN_COLSTATS_DICT_CUR
Open Column Stats Dictionary Cursor dbms_stats_internal.open_clstats_dict_cur(
owner         IN  VARCHAR2,
tabname       IN  VARCHAR2,
partname      IN  VARCHAR2,
colname       IN  VARCHAR2,
cascade_parts IN  BOOLEAN,
fixed_table   IN  BOOLEAN,
colstats_cur  OUT REF CURSOR);
TBD
 
OPEN_COLSTATS_HIST_CUR
Open Column Stats Histogram Cursor dbms_stats_internal.open_colstats_hist_cur(
owner         IN  VARCHAR2,
tabname       IN  VARCHAR2,
partname      IN  VARCHAR2,
as_of_time    IN  TIMESTAMP WITH TIME ZONE,
cascade_parts IN  VARCHAR2,
colstats_cur  OUT REF CURSOR);
TBD
 
OPEN_CUR_ALL_LOCAL_INDEX_PARTS
Undocumented dbms_stats_internal.open_cur_all_local_index_parts(
whose_tab   IN     VARCHAR2,
which_tab   IN     VARCHAR2,
which_part  IN     VARCHAR2,
index_owner IN     VARCHAR2,
index_name  IN     VARCHAR2,
inccur      IN OUT REF CURSOR);
TBD
 
OPEN_EXTN_HIST_CUR
Open Extended Stats Histogram Cursor dbms_stats_internal.open_extn_hist_cur(
owner        IN  VARCHAR2,
tabname      IN  VARCHAR2,
as_of_time   IN  TIMESTAMP WITH TIME ZONE,
colstats_cur OUT REF CURSOR);
TBD
 
OPEN_FXT_COLSTATS_HIST_CUR
Open Fixed Object Column Stats Histogram Cursor dbms_stats_internal.open_fxt_colstats_hist_cur(
owner        IN  VARCHAR2,
tabname      IN  VARCHAR2,
as_of_time   IN  TIMESTAMP WITH TIME ZONE,
colstats_cur OUT REF CURSOR);
TBD
 
OPEN_FXT_STATS_DICT_CUR
Open Fixed Object Stats Dictionary Cursor dbms_stats_internal.open_fxt_stats_dict_cur(
owner        IN  VARCHAR2,
tabname      IN  VARCHAR2,
partname     IN  VARCHAR2,
tabstats_cur OUT REF CURSOR);
TBD
 
OPEN_FXT_STATS_HIST_CUR
Open Fixed Object Stats Histogram Cursor dbms_stats_internal.open_fxt_stats_hist_cur(
owner        IN  VARCHAR2,
tabname      IN  VARCHAR2,
as_of_time   IN  TIMESTAMP WITH TIME ZONE,
tabstats_cur OUT REF CURSOR);
TBD
 
OPEN_GET_IND_PARTS_CUR
Open Index Partitions Cursor dbms_stats_internal.open_get_ind_parts_cur(
whose_ind   IN  VARCHAR2,
which_ind   IN  VARCHAR2,
pname       IN  VARCHAR2,
iobjn       IN  NUMBER,
tobjn       IN  NUMBER,
incremental IN  BOOLEAN,
cur         OUT REF CURSOR);
TBD
 
OPEN_GET_IND_SUBPARTS_CUR
Open Index Subpartitions Cursor dbms_stats_internal.open_get_ind_subparts_cur(
whose_ind   IN  VARCHAR2,
which_ind   IN  VARCHAR2,
pname       IN  VARCHAR2,
iobjn       IN  NUMBER,
tobjn       IN  NUMBER,
incremental IN  BOOLEAN,
cur         OUT REF CURSOR);
TBD
 
OPEN_GET_OPR_PARAMS
Undocumented dbms_stats_internal.open_get_opr_params(
params   IN VARCHAR2,
opr_name IN VARCHAR2)
RETURN REF CURSOR
TBD
 
OPEN_GET_TARGET_PARTITIONS_CUR
Undocumented dbms_stats_internal.open_get_target_partitions_cur(
tobjn      IN  NUMBER,
pname      IN  VARCHAR2,
clist_hist IN  dbms_stats_internal.chtab,
cur        OUT REF CURSOR);
TBD
 
OPEN_GET_TARGET_SUBPARTS_CUR
Open Target Subpartitions Cursor dbms_stats_internal.open_get_target_subparts_cur(
tobjn      IN  NUMBER,
pname      IN  VARCHAR2,
clist_hist IN  dbms_stats_internal.chtab,
cur        OUT REF CURSOR);
TBD
 
OPEN_INDEX_CUR
Undocumented dbms_stats_internal.open_index_cur(
tabowner            IN VARCHAR2,
tabname             IN VARCHAR2,
ind_pat             IN VARCHAR2,
p_status            IN VARCHAR,
no_stats_index_only IN NUMBER,
p_uniqueness        IN VARCHAR2,
inducr              IN OUT REF CURSOR);
TBD
 
OPEN_OPERATIONS_CUR
Undocumented dbms_stats_internal.open_operations_cur(
since         IN     TIMESTAMP WITH TIME ZONE,
until         IN     TIMESTAMP WITH TIME ZONE,
auto_only     IN     NUMBER,
detail_level  IN     VARCHAR2,
latestn       IN     NUMBER,
container_ids IN     dbms_utility.number_array,
opcur         IN OUT REF CURSOR);
TBD
 
OPEN_TAB_STATS_DICT_CUR
Open Table Stats Dictionary Cursor dbms_stats_internal.open_tab_stats_dict_cur(
owner         IN  VARCHAR2,
tabname       IN  VARCHAR2,
partname      IN  VARCHAR2,
stattype      IN  VARCHAR2,
cascade_parts IN  BOOLEAN,
tabstats_cur  OUT REF CURSOR);
TBD
 
OPEN_TAB_STATS_HIST_CUR
Open a Table Stats Histogram Cursor dbms_stats_internal.open_tab_stats_hist_cur(
owner        IN  VARCHAR2,
tabname      IN  VARCHAR2,
as_of_time   IN  TIMESTAMP WITH TIME ZONE,
tabstats_cur OUT REF CURSOR);
TBD
 
PAD_CHR (new 21c)
Undocumented dbms_stats_internal.pad_chr(
chr      IN VARCHAR2,
max_size IN BINARY_INTEGER,
pad_in   IN VARCHAR2)
RETURN VARCHAR2;
SELECT dbms_stats_internal.pad_chr('Library', '20', '$')
FROM dual;

DBMS_STATS_INTERNAL.PAD_CHR('MORGAN','20','$')
----------------------------------------------
Library$$$$$$$$$$$$$
 
PARSE_HASHVAL
Undocumented dbms_stats_internal.parse_hashval(
hashval IN     CLOB,
hashtab IN OUT dbms_stats_internal.numtab,
RETURN NUMBER);
TBD
 
PARSE_INCREMENTAL_STALENESS
Undocumented dbms_stats_internal.parse_incremental_staleness(
ncre_staleness     IN  VARCHAR2,
use_stale_percent  OUT BOOLEAN,
use_locked_stats   OUT BOOLEAN,
allow_mixed_format OUT BOOLEAN);
TBD
 
PART_TYPES
Returns the partition and subpartition types associated with a table dbms_stats_internal.part_types(
owner       IN  VARCHAR2,
objname     IN  VARCHAR2,
namespace_p IN  BINARY_INTEGER,
ptype       OUT BINARY_INTEGER,
sptype      OUT BINARY_INTEGER);
conn sys@pdbdev as sysdba

SELECT namespace
FROM dba_objects
WHERE owner = 'UWCLASS'
AND object_name = 'SERVERS';

DECLARE
 l_ptype  PLS_INTEGER;
 l_sptype PLS_INTEGER;
BEGIN
  dbms_stats_internal.part_types('UWCLASS', 'SERVERS', 1, l_ptype, l_sptype);
  dbms_output.put_line(l_ptype);
  dbms_output.put_line(l_sptype);
END;
/

SELECT DISTINCT namespace
FROM dba_objects
WHERE owner = 'SH'
AND object_name = 'SALES';

DECLARE
 l_ptype  PLS_INTEGER;
 l_sptype PLS_INTEGER;
BEGIN
  dbms_stats_internal.part_types('SH', 'SALES', 1, l_ptype, l_sptype);
  dbms_output.put_line(l_ptype);
  dbms_output.put_line(l_sptype);
END;
/

CREATE TABLE uwclass.list_hash (
cust_id NUMBER(10),
cust_name VARCHAR2(25),
cust_state VARCHAR2(2),
time_id DATE)
PARTITION BY LIST(cust_state)
SUBPARTITION BY HASH (cust_id)
SUBPARTITION TEMPLATE(
SUBPARTITION sp1,
SUBPARTITION sp2,
SUBPARTITION sp3,
SUBPARTITION sp4) (
PARTITION west VALUES ('OR', 'WA'),
PARTITION east VALUES ('NY', 'CT'),
PARTITION cent VALUES ('IL', 'MN'));

DECLARE
 l_ptype  PLS_INTEGER;
 l_sptype PLS_INTEGER;
BEGIN
  dbms_stats_internal.part_types('UWCLASS', 'LIST_HASH', 1, l_ptype, l_sptype);
  dbms_output.put_line(l_ptype);
  dbms_output.put_line(l_sptype);
END;
/
 
PENDING_STATS_ENABLED
Returns TRUE if pending stats are enabled, else FALSE dbms_stats_internal.pending_stats_enabled RETURN BOOLEAN;
BEGIN
  IF dbms_stats_internal.pending_stats_enabled THEN
    dbms_output.put_line('Enabled');
  ELSE
    dbms_output.put_line('Disabled');
  END IF;
END;
/
Enabled

PL/SQL procedure successfully completed.
 
POPULATE_OBJ_LIST_TAB
Undocumented dbms_stats_internal.populate_obj_list_tab(
ownu IN VARCHAR2,
tabu IN VARCHAR2);
TBD
 
POPULATE_TEMP_INSERT
Undocumented dbms_stats_internal.populate_temp_insert(
seltxt   IN VARCHAR2,
fromtxt  IN VARCHAR2,
wheretxt IN VARCHAR2,
fobjn    IN NUMBER,
ttabname IN VARCHAR2);
TBD
 
POST_PROCESS_HIGHF_AUTOTASK
Undocumented dbms_stats_internal.post_process_highf_autotask;
exec dbms_stats_internal.post_process_highf_autotask;

PL/SQL procedure successfully completed.
 
PQFLAGS
Returns the current Parallel Query Flags Value dbms_stats_internal.pqflags RETURN BINARY_INTEGER;
SELECT dbms_stats_internal.pqflags
FROM dual;

 PQFLAGS
--------
       0
 
PREPARE_METHOD_OPT_TEXT
Undocumented dbms_stats_internal.prepare_method_opt_text(
ownname    IN VARCHAR2,
clist_hist IN sys.dbms_stats_internal.chtab,
tabname    IN VARCHAR2)
RETURN VARCHAR2;
TBD
 
PREP_INSERT_SYNOPSIS_ONLINE
Undocumented dbms_stats_internal.prep_insret_synopsis_online(
tobjn     IN NUMBER,
fobjn     IN NUMBER,
clist_syn IN sys.dbmsstatnumtab);
TBD
 
PREP_SAVE_CS
Undocumented dbms_stats_internal.prep_save_cs(sav_time IN TIMESTAMP WITH TIME ZONE) RETURN BOOLEAN;
BEGIN
  IF dbms_stats_internal.prep_save_cs(TO_TIMESTAMP_TZ('2021-05-02 11:00:00 -7:00', 'YYYY-MM-DD HH:MI:SS TZH:TZM')) THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/
FALSE

PL/SQL procedure successfully completed.
 
PRORATE_GLOBAL_NDV
Undocumented dbms_stats_internal.prorate_global_ndv(
intcol          IN NUMBER,
hll_ndv         IN NUMBER,
hll_cardinality IN NUMBER,
hll_partcnt     IN NUMBER,
hll_min         IN NUMBER,
hll_max         IN NUMBER,
hll_nullcnt     IN NUMBER,
as_ndv          IN NUMBER,
as_cardinality  IN NUMBER,
as_partcnt      IN NUMBER,
as_min          IN NUMBER,
as_max          IN NUMBER,
as_nullcnt      IN NUMBER)
RETURN NUMBER;
TBD
 
PURGE_PENDING_STATS
Purge Pending Stats on the identified object dbms_stats_internal.purge_pending_stats(
object_num IN NUMBER,
column_num IN NUMBER,
options    IN NUMBER);
conn sys@pdbdev as sysdba

SELECT object_id
FROM dba_objects
WHERE owner = 'UWCLASS'
AND object_name = 'SERVERS';

SELECT column_id, column_name
FROM dba_tab_cols
WHERE owner = 'UWCLASS'
AND table_name = 'SERVERS';
ORDER BY 1;

exec dbms_stats_internal.purge_pending_stats(92053, 1, 1);
 
PURGE_STATS_AUX
Undocumented dbms_stats_internal.purge_stats_aux(
start_ts   IN TIMESTAMP WITH TIME ZONE,
stop_ts    IN TIMESTAMP WITH TIME ZONE,
object_num IN NUMBER,
column_num IN NUMBER,
options    IN NUMBER);
TBD
 
PURGE_STAT_TABLE
Purge a stats table dbms_stats_internal.purge_stat_table(
statown  IN VARCHAR2,
stattab  IN VARCHAR2,
p_statid IN VARCHAR2);
conn sys@pdbdev as sysdba

exec dbms_stats.create_stat_table(USER, 'STAT_TAB', 'UWDATA');

desc stat_tab

INSERT INTO stat_tab
(statid, type, version, flags)
VALUES
('TEST', 'A', 12, 0);
COMMIT;

SELECT COUNT(*) FROM stat_tab;

exec dbms_stats_internal.purge_stat_table(USER, 'STAT_TAB', 'TEST');

-- appears to work but the row is still there ... obviously I didn't get something right
SELECT COUNT(*) FROM stat_tab;
 
PURGE_SYNOPSIS
Undocumented dbms_stats_internal.purge_synopsis(partitionBatched IN BOOLEAN);
exec dbms_stats_internal.purge_synopsis(TRUE);
 
PURGE_SYNOPSIS_BY_MMON
Undocumented dbms_stats_internal.purge_synopsis_by_mmon;
exec dbms_stats_internal.purge_synopsis_by_mmon;
 
QOSPSETPGAFLG
Undocumented dbms_stats_internal.qOspSetPGAFlf(
andbits IN BINARY_INTEGER,
orbits  IN BINARY_INTEGER,
xorbits IN BINARY_INTEGER)
RETURN BINARY_INTEGER;
TBD
 
RECLAIM_SYNOPSIS_SPACE
Undocumented dbms_stats_internal.reclaim_synopsis_space;
exec dbms_stats_internal.reclaim_synopsis_space;
 
RECORD_OP_FAILURE (new 21c)
Undocumented

Overload 1
dbms_stats_internal.record_op_failure(
op_id      IN NUMBER,
notes      IN VARCHAR2,
commitfree IN BOOLEAN);
TBD
Overload 2 dbms_stats_internal.record_op_failure(
op_id      IN NUMBER,
notes      IN VARCHAR2,
commitfree IN BOOLEAN,
err_msg    IN VARCHAR2);
TBD
 
REC_EXTN_DDL
Undocumented dbms_stats_internal.rec_extn_ddl(
owner     IN VARCHAR2,
tabname   IN VARCHAR2,
col_group IN VARCHAR2,
ddl_flags IN NUMBER);
TBD
 
REMAP_INDEX_NAMES
Undocumented dbms_stats_internal.remap_index_names(
stattabq IN VARCHAR2,
src_ownu IN VARCHAR2,
tgt_ownu IN VARCHAR2,
src_tabu IN VARCHAR2,
tgt_tabu IN VARCHAR2)
RETURN NUMBER;
TBD
 
REMOVE_SUBSCRIBER
Undocumented dbms_stats_internal.remove_subscriber(subscriber IN sys.aq$_agent);
TBD
 
REPORTING_MAN_LOG_OP (new 21c)
Undocumented dbms_stats_internal.reporting_man_log_op(
id         IN NUMBER,
operation  IN VARCHAR2,
target     IN VARCHAR2,
start_time IN TIMESTAMP WITH TIME ZONE,
end_time   IN TIMESTAMP WITH TIME ZONE,
status     IN NUMBER,
job_name   IN VARCHAR2,
session_id IN NUMBER,
notes      IN VARCHAR2);
TBD
 
REPORTING_MAN_LOG_TASK (new 21c)
Undocumented dbms_stats_internal.reporting_man_log_task(
ctx                IN OUT sys.dbms_stats_internal.reportingContext,
target             IN     VARCHAR2,
statusS            IN     NUMBER,
auto_stats_session IN     BOOLEAN);
TBD
 
REPORTING_MAN_LOG_TASK_CALLOUT (new 21c)
Undocumented dbms_stats_internal.reporting_man_log_task_callout(
op_id       IN NUMBER,
job_name    IN VARCHAR2,
status      IN NUMBER,
start_time  IN TIMESTAMP WITH TIME ZONE,
end_time    IN TIMESTAMP WITH TIME ZONE,
target      IN VARCHAR2,
target_objn IN NUMBER,
target_type IN NUMBER,
target_size IN NUMBER,
cost        IN NUMBER,
batch_coeff IN NUMBER,
actions     IN NUMBER,
priority    IN NUMBER,
notes       IN VARCHAR2,
flags       IN NUMBER);
TBD
 
REPORTING_MAN_UPDATE_TASK (new 21c)
Undocumented dbms_stats_internal.reporting_man_update_task(
ctx                IN OUT sys.dbms_stats_internal.reportingContext,
statusS            IN     NUMBER,
auto_stats_session IN     BOOLEAN,
commitfree         IN     BOOLEAN);
TBD
 
RESTORE_STATS_OK
Undocumented dbms_stats_internal.restore_stats_ok(as_of_time IN TIMESTAMP WITH TIME ZONE) RETURN BOOLEAN;
BEGIN
  IF dbms_stats_internal.restore_stats_ok(SYSDATE-1/24) THEN
    dbms_output.put_line('True');
  ELSE
    dbms_output.put_line('False');
  END IF;
END;
/
True

PL/SQL procedure successfully completed.
 
REVOKE_CONC_PRIV_FROM_USER
Undocumented dbms_stats_internal.revoke_conc_priv_from_user(uname IN VARCHAR2);
exec dbms_stats_internal.revoke_conc_priv_from_user('XDB');
 
SAME_COL
Undocumented dbms_stats_internal.same_col(
csr1 IN dbms_stats_internal.colrec,
csr2 IN dbms_stats_internal.colrec)
RETURN BOOLEAN;
TBD
 
SAME_PART
Undocumented dbms_stats_internal.same_part(
csr1 IN dbms_stats_internal.colrec,
csr2 IN dbms_stats_internal.colrec)
RETURN BOOLEAN;
TBD
 
SAME_TAB_COLS
Returns true if two tables have the same column names. A table with the same column names but in a different order will still return TRUE. dbms_stats_internal.same_tab_cols(
owner1    IN VARCHAR2,
tabnameu1 IN VARCHAR2,
owner2    IN VARCHAR2,
tabnameu2 IN VARCHAR2)
RETURN BOOLEAN;
CREATE TABLE torig AS
SELECT obj#, stime, spare8 FROM obj$;

CREATE TABLE tsame AS
SELECT obj#, stime, spare8 FROM obj$;

CREATE TABLE tdiff AS
SELECT obj#, stime, spare9 FROM obj$;

BEGIN
  IF dbms_stats_internal.same_tab_cols('SYS', 'TORIG', 'SYS', 'TSAME') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;

  IF dbms_stats_internal.same_tab_cols('SYS', 'TORIG', 'SYS', 'TDIFF') THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;
END;
/
 
SAVE_AS_PENDING_COL_STATS
Undocumented dbms_stats_internal.save_as_pending_col_stats(cstats IN dbms_stats_internal.coltab)
RETURN BOOLEAN;
TBD
 
SAVE_AS_PENDING_INDEX_STATS
Undocumented dbms_stats_internal.save_as_pending_index_stats(istats IN dbms_stats_internal.indtab)
RETURN BOOLEAN;
TBD
 
SAVE_AS_PENDING_TABLE_STATS
Undocumented dbms_stats_internal.save_as_pending_table_stats(tstats IN dbms_stats_internal.tabtab)
RETURN BOOLEAN;
TBD
 
SCHEMA_EXISTS
Returns TRUE if the named schema exists dbms_stats_internal.schema_exists(uname IN VARCHAR2) RETURN BOOLEAN;
conn sys@pdbdev as sysdba

BEGIN
  IF dbms_stats_internal.schema_exists('ZZYZX') THEN
    dbms_output.put_line('ZZYZX schema exists');
  ELSE
    dbms_output.put_line('ZZYZX schema does not exists');
  END IF;

  IF dbms_stats_internal.schema_exists('UWCLASS') THEN
    dbms_output.put_line('UWCLASSZ schema exists');
  ELSE
    dbms_output.put_line('UWCLASSZ schema does not exists');
  END IF;
END;
/
 
SEGMENT_NUMBER_BLOCKS
Undocumented dbms_stats_internal.segment_number_blocks(
header_tablespace_id IN BINARY_INTEGER,
header_relative_file IN BINARY_INTEGER,
header_block         IN BINARY_INTEGER,
segment_type         IN BINARY_INTEGER,
buffer_pool_id       IN BINARY_INTEGER,
dictionary_flags     IN BINARY_INTEGER,
data_object_id       IN NUMBER,
dictionary_blocks    IN NUMBER,
ignore_offline       IN VARCHAR2)
RETURN NUMBER;
SELECT ts#, name
FROM ts$;

SELECT file_name, file_id
FROM dba_data_files
WHERE tablespace_name = 'UWDATA';

SELECT header_block
FROM dba_segments
WHERE segment_name = 'SERVERS';

-- do not know how to get buffer_pool_id
-- do not know how to get dictionary flags


SELECT t.dataobj#
FROM tab$ t, dba_objects do
WHERE t.obj# = do.object_id
AND do.object_name = 'SERVERS';

-- do not know how to get dictionary blocks
-- do not know what to enter for ignore_offline but tried a number of reasonable? possibilities


SELECT dbms_stats_internal.segment_number_blocks(7, 6, 1827, 2, NULL, NULL, 76842, NULL, 'TRUE')
FROM dual;
 *
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktsapsblk-1], [], [], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 289
 
SET_ADVISOR_EXEC_PROPERTIES
Undocumented dbms_stats_internal.set_advisor_exec_properties(
task_id   IN NUMBER,
privilege IN NUMBER,
username  IN VARCHAR2);
exec dbms_stats_internal.set_advisor_exec_properties(350, -47, 'SYS');
 
SET_ANDV_ALGO_INTERNAL_OBSERVE (new 21c)
Undocumented dbms_stats_internal.set_andv_algo_ingternal_observe(pval IN VARCHAR2);
TBD
 
SET_APPR_NDV (new 21c)
Undocumented dbms_stats_internal.set_appr_ndv(pval IN VARCHAR2);
TBD
 
SET_DEBUG_FLAGS (new 21c)
Undocumented

This procedure is protected by an ACCESSIBLE BY clause
dbms_stats_internal.set_debug_flags(pval IN NUMBER);
exec dbms_stats_internal.set_debug_flags(1);
BEGIN dbms_stats_internal.set_debug_flags(1); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00904: insufficient privilege to access object SET_DEBUG_FLAGS
 
SET_INCREMENTAL_INTERNAL_CONTROL (new 21c)
Undocumented dbms_stats_internal.set_incremental_internal_control(pval IN VARCHAR2);
TBD
 
SET_MON_COL_USAGE_FLAG
Sets or unsets the column monitor usage flag dbms_stats_internal.set_mon_col_usage_flag(session IN BOOLEAN);
exec dbms_stats_internal.set_mon_col_usage_flag(TRUE);

PL/SQL procedure successfully completed.
 
SET_PARAM
Undocumented dbms_stats_internal.set_param(
pname     IN VARCHAR2,
pvalnum   IN NUMBER,
pvalvchar IN VARCHAR2,
updtime   IN TIMESTAMP WITH TIME ZONE,
isdefault IN NUMBER);
TBD
 
SET_PROCESSING_RATE_AUX
Undocumented dbms_stats_internal.set_processing_rate_aux(
opid     IN NUMBER,
procrate IN NUMBER,
originid IN NUMBER);
TBD
 
SET_PROCESSING_RATE_CALLOUT
Undocumented dbms_stats_internal.set_processing_rate_callout(
opid    IN NUMBER,
opvalue IN NUMBER,
origin  IN NUMBER)
RETURN BINARY_INTEGER;
TBD
 
SET_QA_CONTROL (new 21c)
Undocumented

This procedure is protected by an ACCESSIBLE BY clause
dbms_stats_internal.set_qa_control(pval IN NUMBER);
exec dbms_stats_internal.set_qa_control(1);
BEGIN dbms_stats_internal.set_qa_control(1); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00904: insufficient privilege to access object SET_QA_CONTROL
 
SET_STATS_TARGET_STATUS (new 21c)
Undocumented dbms_stats_internal.set_stats_target_status(
objnum        IN NUMBER,
new_status    IN BINARY_INTEGER,
reset_timeout IN BOOLEAN);
TBD
 
SET_SYS_FLAGS (new 21c)
Undocumented dbms_stats_internal.set_sys_flags(flag IN NUMBER);
TBD
 
SET_TEMP_DOP
Set temporary degree of parallelism dbms_stats_internal.set_temp_dop(
ttabname    IN VARCHAR2,
degree      IN NUMBER,
alter_table IN BOOLEAN);
conn sys@pdbdev as sysdba

SELECT degree
FROM dba_all_tables
WHERE table_name = 'AIRPLANES;

exec dbms_stats_internal.set_temp_dop('UWCLASS.AIRPLANES', 2, TRUE);

SELECT degree
FROM dba_all_tables
WHERE table_name = 'AIRPLANES';
-- that was too easy but unfortunately no change observed ... more work to do.
 
SET_TRACE_LEVEL (new 21c)
Undocumented dbms_stats_internal.set_trace_level(pval IN NUMBER);
TBD
 
SHARDEDCATALOG_OR_ROOT
Undocumented dbms_stats_internal.shardedCatalog_or_root RETURN VARCHAR2;
SELECT dbms_stats_internal.shardedCatalog_or_root
FROM dual;

SHARDEDCATALOG_OR_ROOT
----------------------
                     0
 
SIMILAR_OBJECT_EXISTS
Returns TRUE if a "similar" object exists dbms_stats_internal.similar_object_exists(
owner   IN VARCHAR2,
objname IN VARCHAR2,
objtype IN BINARY_INTEGER)
RETURN BOOLEAN;
SQL> SELECT owner, object_type, namespace
  2  FROM dba_objects
  3* WHERE object_name = 'TAB$';

OWNER  OBJECT_TYPE               NAMESPACE
------ ------------------------- ----------
SYS    TABLE                             1

BEGIN
  IF dbms_stats_internal.similar_object_exists( USER, 'TAB$', 1) THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/
FALSE

PL/SQL procedure successfully completed.
 
SIMILAR_SCHEMA_EXISTS
Returns TRUE if a "similar" schema exists dbms_stats_internal.similar_schema_exists(uname IN VARCHAR2) RETURN BOOLEAN;
BEGIN
  IF dbms_stats_internal.similar_schema_exists('SYSBACKUP') THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/
TRUE

PL/SQL procedure successfully completed.
 
STATS_SENSITIVE_TAB (new 21c)
Undocumented dbms_stats_internal.stats_sensitive_tab(
objn    IN NUMBER,
owner   IN VARCHAR2,
tabname IN VARCHAR2);
TBD
 
STORE_SYSTEM_STATS
Sets (saves) three System Stats dbms_stats_internal.store_system_stats(
ioseektim  IN NUMBER,
iotrfspeed IN NUMBER,
cpuspeednw IN NUMBER)
RETURN NUMBER;
SELECT pname, pval1
FROM aux_stats$
WHERE sname = 'SYSSTATS_MAIN'
ORDER BY 1;

PNAME                               PVAL1
------------------------------ ----------
CPUSPEED
CPUSPEEDNW                     1606.82185
IOSEEKTIM                              10
IOTFRSPEED                           4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM


DECLARE
 retVal NUMBER;
BEGIN
  retVal := dbms_stats_internal.store_system_stats(20, 8192, 3000);
  dbms_output.put_line(TO_CHAR(retVal));
END;
/

SELECT pname, pval1
FROM aux_stats$
WHERE sname = 'SYSSTATS_MAIN';

-- either collect real system stats or use this technique to restore the original values as shown
DECLARE
 retVal NUMBER;
BEGIN
  retVal := dbms_stats_internal.store_system_stats(10, 4096, 2708.62471);
  dbms_output.put_line(TO_CHAR(retVal));
END;
/
 
SYSAUX_OFFLINE
Returns TRUE if the local container's SYSAUX tablespace is off-line, else FALSE dbms_stats_internal.sysaux_offline RETURN BOOLEAN;
SELECT status
FROM dba_tablespaces
WHERE tablespace_name = 'SYSAUX';

STATUS
---------
ONLINE


BEGIN
  IF dbms_stats_internal.sysaux_offline  THEN
    dbms_output.put_line('The SYSAUX Tablespace is Off-line');
  ELSE
    dbms_output.put_line('The SYSAUX Tablespace is On-line');
  END IF;
END;
/
The SYSAUX Tablespace is On-line

PL/SQL procedure successfully completed.
 
TABHASGLOBALSTATS
Returns TRUE if global stats have been collected on a partitioned table dbms_stats_internal.tabHasGlobalStats(
owner   IN VARCHAR2,
tabname IN VARCHAR2)
RETURN BOOLEAN;
conn sys@pdbdev as sysdba

BEGIN
  IF dbms_stats_internal.tabHasGlobalStats('SH', 'SALES') THEN
    dbms_output.put_line('Global Stats Collected');
  ELSE
    dbms_output.put_line('Global Stats Not Collected');
  END IF;
END;
/
 
TABLE_PREFS_EXISTS
Undocumented dbms_stats_internal.table_prefs_exists(
pname_p IN VARCHAR2,
ownname IN VARCHAR2,
tabname IN VARCHAR2)
RETURN BOOLEAN;
TBD
 
TABPARTSHAVESTATS
Returns TRUE if partitions stats have been collected on a partitioned table dbms_stats_internal.tabPartsHaveStats(
owner   IN VARCHAR2,
tabname IN VARCHAR2,
pname   IN VARCHAR2)
RETURN BOOLEAN;
conn sys@pdbdev as sysdba

SELECT partition_name
FROM dba_tab_partitions
WHERE table_owner = 'SH'
AND table_name = 'SALES'
ORDER BY 1;

BEGIN
  IF dbms_stats_internal.tabPartsHaveStats('SH', 'SALES', 'SALES_Q2_2000') THEN
    dbms_output.put_line('Partition Stats Collected');
  ELSE
    dbms_output.put_line('Partition Stats Not Collected');
  END IF;
END;
/
 
TAB_STATS_STALE
Undocumented dbms_stats_internal.tab_stats_stale(
baseobj       IN NUMBER,
obj           IN NUMBER,
clist_hist    IN dbms_stats_internal.chtab,
chkhist       IN BOOLEAN,
use_stale_pct IN BOOLEAN)
RETURN BINARY_INTEGER;
TBD
 
TEST_GATHER_SQL_STATS
Undocumented dbms_stats_internal.test_gather_sql_stats(
sql_text    IN CLOB,
user_name   IN VARCHAR2,
bind_list   IN sys.sql_binds,
options     IN VARCHAR2,
exec_userid IN NUMBER,
RETURN sys.xmltype;
TBD
 
TEST_MMON_AUTOJOB
Undocumented dbms_stats_internal.test_mmon_autojob;
SQL> exec dbms_stats_internal.test_mmon_autojob;
BEGIN dbms_stats_internal.test_mmon_autojob; END;
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 85
ORA-06512: at line 1
 
TOP_FREQ_HIST_ENABLED
Returns TRUE if top frequency histograms are enabled on the table dbms_stats_internal.top_freq_hist_enabled(
owner       IN VARCHAR2,
tab         IN VARCHAR2,
incremental IN BOOLEAN)
RETURN BOOLEAN;
BEGIN
  IF dbms_stats_internal.top_freq_hist_enabled('SYS', 'OBJ$', TRUE) THEN
    dbms_output.put_line('Enabled');
  ELSE
    dbms_output.put_line('Disabled');
  END IF;
END;
/
Enabled

PL/SQL procedure successfully completed.
 
TO_BOOL_TYPE
Undocumented dbms_stats_internal.to_bool_type(
pname IN VARCHAR2,
pval  IN VARCHAR2,
okstr IN VARCHAR2)
RETURN BOOLEAN;
TBD
 
TO_CASCADE_TYPE
Undocumented dbms_stats_internal.to_cascade_type(cascade IN VARCHAR2) RETURN BOOLEAN;
TBD
 
TO_DEGREE_TYPE
Converts the string representation of an integer or decimal to a number. Includes appropriate input sanitatization. dbms_stats_internal.to_degree_type(degree IN VARCHAR2) RETURN NUMBER;
SELECT dbms_stats_internal.to_degree_type('4')
FROM dual;

DBMS_STATS_INTERNAL.TO_DEGREE_TYPE('4')
---------------------------------------
                                      4


SELECT dbms_stats_internal.to_degree_type('42.4')
FROM dual;

DBMS_STATS_INTERNAL.TO_DEGREE_TYPE('42.4')
------------------------------------------
                                      42.4
 
TO_ESTIMATE_PERCENT_TYPE
Converts the string representation of an integer or decimal to a number. Includes appropriate input sanitatization. dbms_stats_internal.to_estimate_percent_type(estimate_percent IN VARCHAR2) RETURN NUMBER;
SELECT dbms_stats_internal.to_estimate_percent_type('88')
FROM dual;

DBMS_STATS_INTERNAL.TO_ESTIMATE_PERCENT_TYPE('88')
--------------------------------------------------
                                                88


SQL> SELECT dbms_stats_internal.to_estimate_percent_type('88.5')
  2  FROM dual;

DBMS_STATS_INTERNAL.TO_ESTIMATE_PERCENT_TYPE('88.5')
----------------------------------------------------
                                                88.5
 
TO_STALE_PERCENT_TYPE
Undocumented dbms_stats_internal.to_stale_percent_type(stale_c IN VARCHAR2) RETURN NUMBER;
SELECT dbms_stats_internal.to_stale_percent_type(12) FROM dual;

DBMS_STATS_INTERNAL.TO_STALE_PERCENT_TYPE(12)
---------------------------------------------
                                           12


SELECT dbms_stats_internal.to_stale_percent_type('12') FROM dual;

DBMS_STATS_INTERNAL.TO_STALE_PERCENT_TYPE('12')
-----------------------------------------------
                                             12
 
TO_TABLE_CACHED_BLOCKS_TYPE
Undocumented dbms_stats_internal.to_table_cached_blocks_type(cb_c IN VARCHAR2) RETURN NUMBER;
TBD
 
TRACE (new 21c)
Undocumented

Overload 1
dbms_stats_internal.trace(
b   IN NUMBER,
str IN VARCHAR2);
TBD
Overload 2 dbms_stats_internal.trace(
b    IN NUMBER,
strc IN CLOB);
TBD
Overload 3 dbms_stats_internal.trace(
b    IN NUMBER,
strx IN sys.xmltype);
TBD
 
TRACE_ENABLED (new 21c)
Undocumented dbms_stats_internal.trace_enabled(b IN NUMBER) RETURN BOOLEAN;
TBD
 
TRACE_ERROR
Undocumented dbms_stats_internal.trace_error(msg IN VARCHAR2);
exec dbms_stats_internal.trace_error('This is a test');
 
TRACE_TS (new 21c)
Undocumented dbms_stats_internal.trace_ts(
b   IN NUMBER,
str IN VARCHAR2);
TBD
 
TRANSLATE_EV_COLNAME
Undocumented dbms_stats_internal.translate_ev_colname(
evowner   IN VARCHAR2,
evname    IN VARCHAR2,
evcolname IN VARCHAR2)
RETURN VARCHAR2;
TBD
 
TRANSLATE_EV_TO_TBL
Undocumented dbms_stats_internal.translate_ev_to_tbl(
ownname IN VARCHAR2,
objname IN VARCHAR2)
RETURN VARCHAR2;
TBD
 
TRUNCATE_TABLE
Truncates a table and appears to serve the purpose of encapsulating a dynamic SQL call dbms_stats_internal.truncate_table(tabnameq IN VARCHAR2);
CREATE TABLE trunctest as
SELECT * FROM dba_objects;

exec dbms_stats_internal.truncate_table('TRUNCTEST');

SELECT COUNT(*)
FROM trunctest;
 
TRUNCATE_TEMP
Undocumented dbms_stats_internal.truncate_temp(ttabname IN VARCHAR2);
TBD
 
UPCASE
Upper cases a string in the form a of a stored procedure rather than a function but also contains input sanitization (will not accept punctuation or spaces) dbms_stats_internal.upcase(
inname  IN  VARCHAR2,
outname OUT VARCHAR2);
DECLARE
 inVal  VARCHAR2(30) := 'uwclass';
 outVal VARCHAR2(30);
BEGIN
  dbms_output.put_line(inVal);
  dbms_stats_internal.upcase(inVal, OutVal);
  dbms_output.put_line(OutVal);
END;
/
uwclass
UWCLASS

PL/SQL procedure successfully completed.
 
UPDATE_ADVISOR_DEFAULT_INCLUDE
Undocumented dbms_stats_internal.update_advisor_default_include(
task_id IN NUMBER,
include IN BOOLEAN);
col task_name format a22
col description format a73

SELECT task_id, task_name, description
FROM dba_advisor_tasks;

exec dbms_stats_internal.update_advisor_default_include(350, TRUE);
 
UPDATE_OP_TERMINATION_STATUS
Undocumented

Overload 1
dbms_stats_internal.update_op_termination_status(
op_id IN     NUMBER,
st    IN     NUMBER,
nts   IN OUT VARCHAR2);
TBD
Overload 2 dbms_stats_internal.update_op_termination_status(
op_id IN     NUMBER,
st    IN     NUMBER);
TBD
Overload 3 dbms_stats_internal.update_op_termination_status(
op_id      IN NUMBER,
st         IN NUMBER,
commitfree IN BOOLEAN);
TBD
Overload 4 dbms_stats_internal.update_op_termination_status(
op_id      IN     NUMBER,
st         IN     NUMBER,
nts        IN OUT VARCHAR2,
commitfree IN     BOOLEAN);
TBD
 
UPDATE_ROOT_OR_COORD_DML
Undocumented dbms_stats_internal.update_root_or_coord_dml(share_type IN NUMBER);
TBD
 
UPDATE_SYNOPSIS_HEAD
Undocumented dbms_stats_internal.update_synopsis_head(
tobjn     IN NUMBER,
clist_syn IN dbms_stats_internal.chtab);
TBD
 
UPDATE_SYNOPSIS_PDB_SHARD
Undocumented dbms_stats_internal.update_synopsis_pdb_shard(
tobjn     IN NUMBER,
clist_syn IN sys.dbmsstatnumtab);
TBD
 
UPDATE_TARGET_LIST
Undocumented dbms_stats_internal.update_target_list(
sesid            IN BINARY_INTEGER,
sesser           IN BINARY_INTEGER,
granularity      IN VARCHAR2,
get_index        IN VARCHAR2,
global_stale_pct IN NUMBER);
TBD
 
UPDATE_TASK_TERMINATION_STATUS
Undocumented dbms_stats_internal.update_task_termination_status(
opid  IN NUMBER,
objn  IN NUMBER,
st    IN NUMBER,
tsize IN NUMBER,
actns IN NUMBER,
nts   IN VARCHAR2,
flgs  IN NUMBER);
TBD
 
VALIDATE_REALTIME_STATS_PREF (new 21c)
Undocumented dbms_stats_internal.validate_realtime_stats_pref(pval IN VARCHAR2);
TBD
 
VIEW_COLUMN_EXISTS
Undocumented dbms_stats_internal.view_column_exists(
owner       IN VARCHAR2,
viewname    IN VARCHAR2,
viewcolname IN VARCHAR2)
RETURN BOOLEAN;
BEGIN
  IF dbms_stats_internal.view_column_exists(USER, 'USER_ALL_TABLES', 'OWNER') THEN
    dbms_output.put_line('Owner Column Exists');
  ELSE
    dbms_output.put_line('Owner Column Does Not Exists');
  END IF;
END;
/
Owner Column Does Not Exists

PL/SQL procedure successfully completed.
 
WRITE_CLOB_TO_FILE
Creates a physical file and dumps the contents of a CLOB to the file dbms_stats_internal.write_clob_to_file(
dir_name  IN VARCHAR2,
file_name IN VARCHAR2,
content   IN CLOB);
SELECT owner, directory_name, directory_path
FROM dba_directories
ORDER BY 1,2;

DECLARE
 cCLOB CLOB := 'Test dumping test to a file';
BEGIN
  dbms_stats_internal.write_clob_to_file('ORACLE_BASE', 'dumpfile.txt', cCLOB);
END;
/

-- go to the CTEMP directory and open dumpfile.txt

Related Topics
Built-in Functions
Built-in Packages
DBMS_STATS
DBMS_STATS_ADVISORS
X$KCFIO
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx