Oracle DBMS_STATS_INTERNAL
Version 18.3

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

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 ...
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 194 objects

Additionally

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_PARAM_VAL IS_MV_TABLE_BUSY
ADD_PARAM GET_ADVISOR_RAT_ID IS_PARAM_VALUE_DEFAULT
ADD_SUBSCRIBER GET_ADVISOR_REC_CBK IS_PARTITIONED_TAB
ADVISOR_CHECK_OBJ_FILTER_VALID GET_ADVISOR_REC_ID IS_PART_DEFAULT
ADVISOR_CHECK_RESUME GET_ADVISOR_REC_NAME IS_PART_TYP_SAME
ADVISOR_CLEANUP_OBJ_FILTER GET_ADVISOR_REPORT_CBK IS_RES_MAN_ON
ADVISOR_CLEAR_DIRECTIVES GET_ADVISOR_RTL_NAME IS_STALE
ADVISOR_CLEAR_FILTERS GET_ADVISOR_RULE_DESC IS_STALE_IN_CONTAINER
ADVISOR_EXPAND_DIRECTIVES GET_ADVISOR_RULE_ID IS_TABLE_EMPTY
ADVISOR_GET_MAX_OBJ_ID GET_ADVISOR_RULE_NAME IS_TEMP_TAB
ADVISOR_GET_OBJECT_NUMBER GET_ADVISOR_RULE_OBJ_ID IS_URGENT_ERROR
ADVISOR_GET_OBJ_DIR GET_ADVISOR_TASK_ID JOB_CLASS_EXISTS
ADVISOR_GET_OPR_DIR GET_ADVISOR_TASK_NAME MANAGE_COL_TRACKING_LEVEL_JOB
ADVISOR_GET_RULE_DIR GET_ADVISOR_TASK_OWNER MIN_NUM
ADVISOR_GET_UNIQUE_DIR_NAME GET_ADVISOR_TASK_PRIVILEGE MONITORING_MAN_SIGNAL_SAFEMODE
ADVISOR_IS_SYSTEM_RULE GET_AGG_CHT_WITH_DISK OBJECT_EXISTS
ADVISOR_REPORT_HEADER GET_AGG_CLIST_WITH_DISK OPEN_ADVISOR_ACTION_OBJ_CUR
ADVISOR_REPORT_SUMMARY GET_AGG_COLSTATS OPEN_ADVISOR_FINDING_OBJ_CUR
ADVISOR_RULE_HAS_FINDING GET_AGG_NDV OPEN_ALL_EMPTY_OBJS_CUR
ADVISOR_SETUP_OBJ_FILTER GET_ALL_ADVISOR_RULE_IDS OPEN_ALL_OBJECTS_CUR
ADVISOR_SKIP_CHECKED_RULES GET_APPROX_NDV_ALGORITHM OPEN_ALL_STALE_OBJS_CUR
ADVISOR_TASK_EXISTS GET_BLKCNT OPEN_COLSTATS_DICT_CUR
AGGREGATE_INDSTATS GET_CALIB_OPID_CALLOUT OPEN_COLSTATS_HIST_CUR
AGG_PARTITION_ROWCNT GET_COLNAME OPEN_CUR_ALL_LOCAL_INDEX_PARTS
AGG_PDB_SHARD_TSTATS GET_COLNUM OPEN_EXTN_HIST_CUR
ALTER_PARAMETER_VAL GET_COLTYPE OPEN_FXT_COLSTATS_HIST_CUR
BITCLR GET_COL_EXPR OPEN_FXT_STATS_DICT_CUR
BITOR GET_COUNT_OF_COLS OPEN_FXT_STATS_HIST_CUR
CAN_DERIVE_COL_HISTOGRAM GET_CURRENT_JOB_NAME OPEN_GET_IND_PARTS_CUR
CCT GET_CURRENT_SESSION_ID OPEN_GET_IND_SUBPARTS_CUR
CHECK_ADVISOR_INPUT_VALIDITY GET_DB_BLOCK_SIZE OPEN_GET_OPR_PARAMS
CHECK_ADVISOR_OBJ_FILTER GET_DEFAULT$ OPEN_GET_TARGET_PARTITIONS_CUR
CHECK_ADVISOR_OPR_FILTER GET_DEFAULT_ADVISOR_EXECUTION OPEN_GET_TARGET_SUBPARTS_CUR
CHECK_ADVISOR_RULE_FILTER GET_DEFAULT_DOP OPEN_OPERATIONS_CUR
CHECK_ANALYZE_DONE GET_EXPORT_SQL_CLAUSES OPEN_TAB_STATS_DICT_CUR
CHECK_DOMIDX_PARTITIONS GET_EXP_LIST OPEN_TAB_STATS_HIST_CUR
CHECK_DOMIDX_PARTS GET_FIRST_PART_COL PARSE_HASHVAL
CHECK_DOMIDX_SUBPARTITIONS GET_FXT_OBJ PARSE_INCREMENTAL_STALENESS
CHECK_GLOBAL_TAB_STALENESS GET_FXT_TYP PART_TYPES
CHECK_GRANULARITY GET_HIST_DENSITY PENDING_STATS_ENABLED
CHECK_HIST_MISSING GET_IDENTIFIER POPULATE_OBJ_LIST_TAB
CHECK_INCREMENTAL_STALENESS GET_IDX_TABPART POPULATE_TEMP_INSERT
CHECK_VOLATILE GET_INDEX_BLOCK_COUNT PQFLAGS
CHECK_WINDOW_CAPTURE_MODE GET_INDEX_LOCK_FLAG PREPARE_METHOD_OPT_TEXT
CLEAR_MON_COL_USAGE_FLAG GET_INDEX_PART_NAME PREP_INSERT_SYNOPSIS_ONLINE
COLHASBASESTATS GET_INDSTATS_DICT PREP_SAVE_CS
COLHASHISTOGRAM GET_IND_COLS PRORATE_GLOBAL_NDV
COLUMN_EXISTS GET_IND_SHARE_TYPE PURGE_PENDING_STATS
COL_STATS_FROM_LOAD GET_INTCOL PURGE_STATS_AUX
COMPOSE_HASHVAL_CLOB_REC GET_IOT_MAPPING_TABLE PURGE_STAT_TABLE
COMPUTE_DOP GET_MBRC PURGE_SYNOPSIS
CONTROL_PARALLEL GET_MV_ENQUE PURGE_SYNOPSIS_BY_MMON
COUNT_PDB_OR_SHARD GET_NUM_ROWS QOSPSETPGAFLG
CREATE_ADVISOR_OBJECT GET_OBJLIST_TEXT RECLAIM_SYNOPSIS_SPACE
CREATE_TEMP GET_OBJNUM REC_EXTN_CREATED
DBMS_ASSERT_SIMPLE_SQL_NAME GET_OBJ_NAME_USER_NAME REMAP_INDEX_NAMES
DECODE_GRANULARITY GET_OLDEST_HISTORY_TIME REMOVE_SUBSCRIBER
DELETE_CACHE_HISTORY GET_PARAM RESTORE_STATS_OK
DELETE_COL_USAGE GET_PARAMETER_VAL REVOKE_CONC_PRIV_FROM_USER
DELETE_EXPR_STATS GET_PARAM_DEFAULT SAME_COL
DELETE_FROM_USTATS GET_PARAM_NUMVAL SAME_PART
DELETE_PARTITION_SYNOPSIS GET_PARAM_PROP SAME_TAB_COLS
DELETE_PROC_RATE_CALLOUT GET_PARTN_LOCK_FLAG SAVE_AS_PENDING_COL_STATS
DELETE_SINGLE_COL_GROUP_USAGE GET_PART_COLS SAVE_AS_PENDING_INDEX_STATS
DELETE_STATS_OPERATION GET_PREFS SAVE_AS_PENDING_TABLE_STATS
DEL_SESSION_COL_STATS GET_PREV_PART SCHEMA_EXISTS
DERIVE_GLOBAL_HISTOGRAM GET_PROCRATE_ID SEGMENT_NUMBER_BLOCKS
DML_STATTAB_PREFS GET_SC_1201 SET_ADVISOR_EXEC_PROPERTIES
DML_TABLE_PREFS GET_SC_1202 SET_MON_COL_USAGE_FLAG
DQ GET_SC_MAX SET_PARAM
DROP_OLD_TEMP GET_STALE_REASON SET_PROCESSING_RATE_AUX
DROP_TEMP GET_STAT_EXTENSION SET_PROCESSING_RATE_CALLOUT
DUMP_ALERT_TSES GET_SYNOPSIS_DEGREE SET_TEMP_DOP
DUMP_PQ_SESSTAT GET_SYNOPSIS_GROUP_NUM SHARDEDCATALOG_OR_ROOT
DUMP_QUERY GET_TABLE_BLOCK_COUNT SIMILAR_OBJECT_EXISTS
DUMP_TRACE GET_TABLE_DEGREE SIMILAR_SCHEMA_EXISTS
DUMP_TRACE_TS GET_TABLE_PREFS STORE_SYSTEM_STATS
EST_USING_NONEXACT_HISTORY GET_TABLE_PROPERTY SYSAUX_OFFLINE
EXECUTE_ADVISOR_AUTO_TASK GET_TABLE_STATS_SIMPLE TABHASGLOBALSTATS
EXPORT_COLSTATS_DIRECT GET_TAB_NUM_SEGMENTS TABLE_PREFS_EXISTS
EXPORT_COL_USAGE GET_TAB_PROPERTY TABPARTSHAVESTATS
EXPORT_FXT_COLSTATS_DIRECT GET_TAB_PROPERTY_AND_FLAGS TAB_STATS_STALE
EXTRACT_STR GET_TAB_SHARE_TYPE TEST_GATHER_SQL_STATS
FILL_IN_PARAMS GET_TAB_SHARE_TYPE_VIEW TEST_MMON_AUTOJOB
FILL_IN_PARAMS_WITH_NO_PREFS GET_USER_NUM TOP_FREQ_HIST_ENABLED
FILL_SYNOPSIS GRANT_CONC_PRIV_TO_USER TO_BOOL_TYPE
FIXQ HYBRID_HIST_ENABLED TO_CASCADE_TYPE
FIX_IND_NAMES IMPORT_COL_USAGE_FOR_DIR TO_DEGREE_TYPE
FIX_TAB_NAMES IMPORT_MON_INFO TO_ESTIMATE_PERCENT_TYPE
FLUSH_CACHE_STATS IMPORT_STATS_HISTORY TO_STALE_PERCENT_TYPE
FORMAT_CACHE_ROWS IMPORT_SYNOPSES_STATS TO_TABLE_CACHED_BLOCKS_TYPE
GATHER_FXT_STATS_OK INDEX_MAX_KEYSIZE_OK TRACE_ERROR
GATHER_INDEX INDHASGLOBALSTATS TRANSLATE_EV_COLNAME
GATHER_PROCESSING_RATE_AUX INDPARTSHAVESTATS TRANSLATE_EV_TO_TBL
GATHER_PROCESSING_RATE_JOB INIT_ID_MAP_TAB TRUNCATE_TABLE
GATHER_SCAN_RATE INIT_OBJ_LIST_TAB TRUNCATE_TEMP
GATHER_SCAN_RATE_BY_MMON INIT_STATS_ARG_TAB UPCASE
GATHER_SQL_STATS INSERT_ADVISOR_FILTER_OBJ UPDATE_ADVISOR_DEFAULT_INCLUDE
GENERATE_OP_ID INSERT_INTO_USTATS UPDATE_OP_TERMINATION_STATUS
GET_ADVISOR_DEFAULT_INCLUDE INSERT_ONLY UPDATE_ROOT_OR_COORD_DML
GET_ADVISOR_FINDING_DEF_ID INS_SESSION_HG_STATS UPDATE_SYNOPSIS_HEAD
GET_ADVISOR_FINDING_ID INT_ASSERT UPDATE_SYNOPSIS_PDB_SHARD
GET_ADVISOR_FINDING_NAME ISSHARDEDCATALOG UPDATE_TARGET_LIST
GET_ADVISOR_NUM_FINDINGS IS_DUPLICATED_OPR UPDATE_TASK_TERMINATION_STATUS
GET_ADVISOR_OBJECT_TYPE_ID IS_HADOOP_TABLE VIEW_COLUMN_EXISTS
GET_ADVISOR_OPR_FILTER IS_INDEX_UNIQUE 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 (new 19c)
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);
SQL> exec dbms_stats_internal.advisor_check_obj_filter_valid(USER, 'TAB$');

PL/SQL procedure successfully completed.

SQL> 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);
SQL> 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;
 
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;
SQL> SELECT dbms_stats_internal.advisor_get_max_obj_id(1)
  2  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;
SQL> DECLARE
  2   retVal VARCHAR2(30);
  3  BEGIN
  4    retVal := dbms_stats_internal.advisor_get_unique_dir_name(350);
  5    dbms_output.put_line(retVal);
  6  END;
  7  /
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;
/
 
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);
 
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(350) THEN
    dbms_output.put_line('Exists');
  ELSE
    dbms_output.put_line('Does Not Exist');
  END IF;
END;
/
 
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 (new 19c)
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
 
BITCLR
Undocumented dbms_stats_internal.bitclr(
v1 IN BINARY_INTEGER,
v2 IN BINARY_INTEGER)
RETURN BINARY_INTEGER;
SQL> ed
Wrote file afiedt.buf

1 SELECT dbms_stats_internal.bitclr(3, 2)
2* FROM dual
SQL> /

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

SQL> ed
Wrote file afiedt.buf

1 SELECT dbms_stats_internal.bitclr(3, 99)
2* FROM dual
SQL> /

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;
SQL> SELECT dbms_stats_internal.bitor(1, 0)
  2* FROM dual;

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

SQL> SELECT dbms_stats_internal.bitor(0, 0)
  2* FROM dual;

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

SQL> SELECT dbms_stats_internal.bitor(1, 1)
  2* 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');
 
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_DOMIDX_PARTITIONS (new 19c)
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 (new 19c)
Undocumented dbms_check_domidx_subpartitions(
idx_objno  IN NUMBER,
part_objno IN NUMBER,
is_subpart IN BOOLEAN);
RETURN NUMBER;
TBD
 
CHECK_GLOBAL_TAB_STALENESS (new 19c)
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 (new 19c)
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_VOLATILE
Undocumented dbms_stats_internal.check_volatile(tobjn IN NUMBER)
RETURN VARCHAR2;
SQL> SELECT dbms_stats_internal.check_volatile(350)
  2  FROM dual;

DBMS_STATS_INTERNAL.CHECK_VOLATILE(350)
----------------------------------------
F
 
CHECK_WINDOW_CAPTURE_MODE (new 19c)
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, 4)
FROM dual;
 
CONTROL_PARALLEL
Undocumented dbms_stats_internal.control_parallel(
enable  IN BOOLEAN,
pqflags IN BINARY_INTEGER)
RETURN BOOLEAN;
TBD
 
COUNT_PDB_OR_SHARD (new 19c)
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('OBJ$')
FROM dual;
 
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;
/
 
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 (new 19c)
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$;

exec dbms_stats_internal.delete_from_ustats(58562, 0);

SELECT * FROM sys.ustats$;
 
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(1)
FROM dual;

-- successful however the value 8 produces an ORA-00600

SQL> SELECT dbms_stats_internal.delete_proc_rate_callout(8)
  2  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);
 
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
 
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);
 
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
 
EST_USING_NONEXACT_HISTORY (new 19c)
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;
/
 
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_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;

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;
/
 
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);
 
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;
 
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 11135
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 11189
ORA-06512: at line 6


-- clearly more work needs to be done on this demo
 
GENERATE_OP_ID
Undocumented dbms_stats_internal.generate_op_id RETURN NUMBER;
SELECT dbms_stats_internal.generate_op_id
FROM dual;
 
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;
SQL> SELECT DISTINCT finding_id, finding_name
  2  FROM v$stats_advisor_findings
  3* 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

SQL> SELECT dbms_stats_internal.get_advisor_finding_id('F_MaintenanceWindowNotEnabled')
2 FROM dual;

DBMS_STATS_INTERNAL.GET_ADVISOR_FINDING_ID('F_MAINTENANCEWINDOWNOTENABLED')
---------------------------------------------------------------------------
                                                                         2
 
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;
SQL> SELECT DISTINCT finding_id, finding_name
  2  FROM v$stats_advisor_findings
  3* 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

SQL> SELECT dbms_stats_internal.get_advisor_finding_name(2)
  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;
SQL> SELECT parameter_name
  2  FROM DBA_ADVISOR_DEF_PARAMETERS
  3  WHERE advisor_name = 'ADDM';

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

SQL> SELECT dbms_stats_internal.get_advisor_param_val(350, 'ANALYSIS_TYPE')
  2  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 dbms_stats_internal.get_advisor_rec_id('Rec_OutOfOrderHistorgram')
FROM dual;
 
GET_ADVISOR_REC_NAME
Undocumented dbms_stats_internal.get_advisor_rec_name(rec_id IN NUMBER)
RETURN VARCHAR2;
SQL> SELECT MAX(rec_id)
  2  FROM v_$stats_advisor_recs;

REC_ID
------
    34

SQL> SELECT dbms_stats_internal.get_advisor_rec_name(34)
  2  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;
SQL> SELECT dbms_stats_internal.get_advisor_rule_desc(1)
  2  FROM dual;

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

SQL> SELECT dbms_stats_internal.get_advisor_rule_desc(23)
  2  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;
SQL> SELECT dbms_stats_internal.get_advisor_rule_id('UseAutoDegree')
  2  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;
SQL> SELECT dbms_stats_internal.get_advisor_rule_name(1) FROM dual;

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

SQL> SELECT dbms_stats_internal.get_advisor_rule_name(2) FROM dual;

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

SQL> SELECT dbms_stats_internal.get_advisor_rule_name(3) FROM dual;

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

SQL> SELECT dbms_stats_internal.get_advisor_rule_name(4) FROM dual;

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

SQL> SELECT dbms_stats_internal.get_advisor_rule_name(5) FROM dual;

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

SQL> SELECT dbms_stats_internal.get_advisor_rule_name(6) FROM dual;

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

SQL> 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)
FORM dual;
 
GET_AGG_CHT_WITH_DISK (new 19c)
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 (new 19c)
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$');

SELECT dbms_stats_internal.get_blkcnt(18)
FROM dual;


-- while this does not return an error it also does not return a value so I'm not happy with this one 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;

SELECT dbms_stats_internal.get_colname(4, 2, NULL)
FROM dual;
 
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;
/
 
GET_CURRENT_SESSION_ID
Undocumented dbms_stats_internal.get_current_session_id(
sesid  OUT BINARY_INTEGER,
sesser OUT BINARY_INTEGER);
SQL> SELECT osuser, sid, serial#, program
2 FROM v$session
3 WHERE service_name NOT LIKE '%BACK%';

OSUSER                                SID    SERIAL# PROGRAM
------------------------------ ---------- ---------- -------------
perrito4\oracle                       134       4239 sqlplusw.exe
perrito4\oracle                       195       1561 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;
/
 
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. dbms_stats_internal.get_db_block_size RETURN NUMBER;
SELECT dbms_stats_internal.get_db_block_size
FROM dual;
 
GET_DEFAULT$
Undocumented dbms_stats_internal.get_default$(colrowid IN ROWID)
RETURN CLOB;
TBD
 
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;
SQL> SELECT dbms_stats_internal.get_default_advisor_execution(350)
  2  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
UndocumentedReturns 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;
 
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;
 
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;
 
GET_IDENTIFIER (new 19c)
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(76841)
FROM dual;
 
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')
FROM dual;
 
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;
 
GET_IND_SHARE_TYPE (new 19c)
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;
 
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_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 (new 19c)
Returns the number of rows in a table dbms_stats_internal.get_num_rows(objn IN NUMBER)
RETURN NUMBER;
SQL> SELECT COUNT(*)
  2* FROM tab$;

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

SELECT dbms_stats_internal.get_num_rows(4)
FROM dual;

DBMS_STATS_INTERNAL.GET_NUM_ROWS(4)
-----------------------------------
                               2192
 
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

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;
 
GET_OBJ_NAME_USER_NAME (new 19c)
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;

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

SELECT MIN(last_analyzed)
FROM dba_all_tables;
 
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;
/
 
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);
SQL> DECLARE
  2   p1 NUMBER;
  3   p2 VARCHAR2(30);
  4   p3 NUMBER;
  5  BEGIN
  6    dbms_stats_internal.get_param_default('DEGREE', p1, p2, p3);
  7    dbms_output.put_line('pValNum: ' || TO_CHAR(p1));
  8    dbms_output.put_line('pVal: ' || p2);
  9    dbms_output.put_line('isDefault: ' || TO_CHAR(p3));
 10  END;
 11  /
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_SC_1201
  dbms_stats_internal.get_sc_1201 RETURN NUMBER;
SQL> SELECT dbms_stats_internal.get_sc_1201
  2  FROM dual;

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

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

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

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


SQL> SELECT dbms_stats_internal.get_stale_reason(2)
  2* FROM dual;

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


SQL> SELECT dbms_stats_internal.get_stale_reason(0)
  2* FROM dual;

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


SQL> SELECT dbms_stats_internal.get_stale_reason(4)
  2* FROM dual;

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

SQL> SELECT dbms_stats_internal.get_stale_reason(99)
  2* 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;

ALTER TABLE uwclass.airplanes PARALLEL 4;

SELECT dbms_stats_internal.get_table_degree('UWCLASS', 'AIRPLANES')
FROM dual;
 
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';

SELECT dbms_stats_internal.get_table_property(92059)
FROM dual;

DBMS_STATS_INTERNAL.GET_TABLE_PROPERTY(92059)
---------------------------------------------
                                    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,
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';

DECLARE
 lNRows NUMBER;
 lNBlks NUMBER;
 lAnlDt DATE;
BEGIN
  dbms_stats_internal.get_table_stats_simple(76840, 2, 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;
/
 
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(19005)
FROM dual;
 
GET_TAB_PROPERTY
Undocumented

Overload 1
dbms_stats_internal.get_tab_property(owner IN VARCHAR2, tabname IN VARCHAR2)
RETURN NUMBER;
SELECT object_id
FROM dba_objects
WHERE object_name = 'AIRPLANES';

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

SELECT dbms_stats_internal.get_tab_property(76840)
FROM dual;
 
GET_TAB_PROPERTY_AND_FLAGS (new 19c)
Undocumented dbms_stats_internal.get_tab_property_and_flags(
tobjn    IN  NUMBER,
property OUT NUMBER,
flags    OUT NUMBER);
TBD
 
GET_TAB_SHARE_TYPE (new 19c)
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 (new 19c)
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;
 
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');
exec dbms_stats_internal.revoke_conc_priv_from_user('XDB');
 
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;
/
 
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
 
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('True');
  ELSE
    dbms_output.put_line('False');
  END IF;
END;
/
 
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';

BEGIN
  IF dbms_stats_internal.indhasglobalstats(76843) THEN
    dbms_output.put_line('Index Has Global Stats');
  ELSE
    dbms_output.put_line('Index Does Not Have Global Stats');
  END IF;
END;
/
 
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;
 
INIT_OBJ_LIST_TAB
Undocumented dbms_stats_internal.init_obj_list_tab;
exec dbms_stats_internal.init_obj_list_tab;
 
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;
 
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 (new 19c)
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 (new 19c)
Undocumented dbms_stats_internal.is_duplicated_opr(objnum IN NUMBER)
RETURN BOOLEAN;
TBD
 
IS_HADOOP_TABLE
Undocumented 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;
/
 
IS_INDEX_UNIQUE
Undocumented 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';

SELECT object_id
FROM dba_objects
WHERE object_name = 'PK_SERVERS';

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

SELECT dbms_stats_internal.is_index_unique(76842, 76843, 1) from dual;
 
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;

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;
/
 
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 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;
/
 
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_RES_MAN_ON
Returns TRUE if resource management is enabled 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;
/
 
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 (new 19c)
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_TABLE_EMPTY
Returns TRUE if the table is empty 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;
/
 
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;
/
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;
/
 
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;

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;
/
 
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
 
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
 
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;
/
 
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_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
 
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 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;
/
 
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
 
PQFLAGS
Returns the current Parallel Query Flags Value dbms_stats_internal.pqflags RETURN BINARY_INTEGER;
SELECT dbms_stats_internal.pqflags
FROM dual;
 
PREPARE_METHOD_OPT_TEXT (new 19c)
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('2017-12-12 11:00:00 -8:00', 'YYYY-MM-DD HH:MI:SS TZH:TZM')) THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/
 
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;
 
REC_EXTN_CREATED
Undocumented dbms_stats_internal.rec_extn_created(
objn      IN NUMBER,
col_group IN VARCHAR2);
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 (new 19c)
Undocumented dbms_stats_internal.remove_subscriber(subscriber IN sys.aq$_agent);
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;
/
 
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_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);
 
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_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.
 
SHARDEDCATALOG_OR_ROOT (new 19c)
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
 
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
 
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;
SQL> SELECT pname, pval1
2 FROM aux_stats$
3 WHERE sname = 'SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEED
CPUSPEEDNW                     2708.62471
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 I've done here
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 dbms_stats_internal.sysaux_offline RETURN BOOLEAN;
SELECT status
FROM dba_tablespaces
WHERE tablespace_name = 'SYSAUX';

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;
/
 
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 (new 19c)
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;
/
 
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;
SQL> SELECT dbms_stats_internal.to_degree_type('4')
  2  FROM dual;

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

SQL> SELECT dbms_stats_internal.to_degree_type('42.4')
  2  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;
SQL> SELECT dbms_stats_internal.to_estimate_percent_type('88')
SQL> SELECT dbms_stats_internal.to_estimate_percent_type('88')
  2  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;

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

-- why it likes string representations of numeric values is beyond me ... why doesn't it just accept a NUMBER as the IN parameter?
 
TO_TABLE_CACHED_BLOCKS_TYPE
Undocumented dbms_stats_internal.to_table_cached_blocks_type(cb_c IN VARCHAR2)
RETURN NUMBER;
TBD
 
TRACE_ERROR
Undocumented dbms_stats_internal.trace_error(msg IN VARCHAR2);
exec dbms_stats_internal.trace_error('This is a test');
 
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;
/
 
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 (new 19c)
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 (new 19c)
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
 
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;
/
 
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 18c
What's New In 19c

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