Oracle DBMS_STATS_INTERNAL
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Do you remember when mainframes were replaced by client-server? When client-server was replaced by n-tier architecture? The "Cloud" is a distraction ... DevOps is not. Prepare for the future.
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 174 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_RAT_ID IS_PARTITIONED_TAB
ADD_PARAM GET_ADVISOR_REC_CBK IS_PART_DEFAULT
ADVISOR_CHECK_OBJ_FILTER_VALID GET_ADVISOR_REC_ID IS_PART_TYP_SAME
ADVISOR_CHECK_RESUME GET_ADVISOR_REC_NAME IS_RES_MAN_ON
ADVISOR_CLEANUP_OBJ_FILTER GET_ADVISOR_REPORT_CBK IS_STALE
ADVISOR_CLEAR_DIRECTIVES GET_ADVISOR_RTL_NAME IS_TABLE_EMPTY
ADVISOR_CLEAR_FILTERS GET_ADVISOR_RULE_DESC IS_TEMP_TAB
ADVISOR_EXPAND_DIRECTIVES GET_ADVISOR_RULE_ID IS_URGENT_ERROR
ADVISOR_GET_MAX_OBJ_ID GET_ADVISOR_RULE_NAME JOB_CLASS_EXISTS
ADVISOR_GET_OBJECT_NUMBER GET_ADVISOR_RULE_OBJ_ID MANAGE_COL_TRACKING_LEVEL_JOB
ADVISOR_GET_OBJ_DIR GET_ADVISOR_TASK_ID MIN_NUM
ADVISOR_GET_OPR_DIR GET_ADVISOR_TASK_NAME MONITORING_MAN_SIGNAL_SAFEMODE
ADVISOR_GET_RULE_DIR GET_ADVISOR_TASK_OWNER OBJECT_EXISTS
ADVISOR_GET_UNIQUE_DIR_NAME GET_ADVISOR_TASK_PRIVILEGE OPEN_ADVISOR_ACTION_OBJ_CUR
ADVISOR_IS_SYSTEM_RULE GET_AGG_COLSTATS OPEN_ADVISOR_FINDING_OBJ_CUR
ADVISOR_REPORT_HEADER GET_AGG_NDV OPEN_ALL_EMPTY_OBJS_CUR
ADVISOR_REPORT_SUMMARY GET_ALL_ADVISOR_RULE_IDS OPEN_ALL_OBJECTS_CUR
ADVISOR_RULE_HAS_FINDING GET_APPROX_NDV_ALGORITHM OPEN_ALL_STALE_OBJS_CUR
ADVISOR_SETUP_OBJ_FILTER GET_BLKCNT OPEN_COLSTATS_DICT_CUR
ADVISOR_SKIP_CHECKED_RULES GET_CALIB_OPID_CALLOUT OPEN_COLSTATS_HIST_CUR
ADVISOR_TASK_EXISTS GET_COLNAME OPEN_CUR_ALL_LOCAL_INDEX_PARTS
AGGREGATE_INDSTATS GET_COLNUM OPEN_EXTN_HIST_CUR
AGG_PARTITION_ROWCNT GET_COLTYPE OPEN_FXT_COLSTATS_HIST_CUR
ALTER_PARAMETER_VAL GET_COL_EXPR OPEN_FXT_STATS_DICT_CUR
BITCLR GET_COUNT_OF_COLS OPEN_FXT_STATS_HIST_CUR
BITOR GET_CURRENT_JOB_NAME OPEN_GET_IND_PARTS_CUR
CAN_DERIVE_COL_HISTOGRAM GET_CURRENT_SESSION_ID OPEN_GET_IND_SUBPARTS_CUR
CCT GET_DB_BLOCK_SIZE OPEN_GET_OPR_PARAMS
CHECK_ADVISOR_INPUT_VALIDITY GET_DEFAULT$ OPEN_GET_TARGET_PARTITIONS_CUR
CHECK_ADVISOR_OBJ_FILTER GET_DEFAULT_ADVISOR_EXECUTION OPEN_GET_TARGET_SUBPARTS_CUR
CHECK_ADVISOR_OPR_FILTER GET_DEFAULT_DOP OPEN_OPERATIONS_CUR
CHECK_ADVISOR_RULE_FILTER GET_EXPORT_SQL_CLAUSES OPEN_TAB_STATS_DICT_CUR
CHECK_ANALYZE_DONE GET_EXP_LIST OPEN_TAB_STATS_HIST_CUR
CHECK_DOMIDX_PARTS GET_FIRST_PART_COL PARSE_HASHVAL
CHECK_GRANULARITY GET_FXT_OBJ PARSE_INCREMENTAL_STALENESS
CHECK_INCREMENTAL_STALENESS GET_FXT_TYP PART_TYPES
CHECK_VOLATILE GET_HIST_DENSITY PENDING_STATS_ENABLED
CLEAR_MON_COL_USAGE_FLAG GET_IDX_TABPART POPULATE_OBJ_LIST_TAB
COLHASBASESTATS GET_INDEX_BLOCK_COUNT POPULATE_TEMP_INSERT
COLHASHISTOGRAM GET_INDEX_LOCK_FLAG PQFLAGS
COLUMN_EXISTS GET_INDEX_PART_NAME PREP_INSERT_SYNOPSIS_ONLINE
COL_STATS_FROM_LOAD GET_INDSTATS_DICT PREP_SAVE_CS
COMPOSE_HASHVAL_CLOB_REC GET_IND_COLS PRORATE_GLOBAL_NDV
COMPUTE_DOP GET_INTCOL PURGE_PENDING_STATS
CONTROL_PARALLEL GET_IOT_MAPPING_TABLE PURGE_STATS_AUX
CREATE_ADVISOR_OBJECT GET_MBRC PURGE_STAT_TABLE
CREATE_TEMP GET_MV_ENQUE PURGE_SYNOPSIS
DBMS_ASSERT_SIMPLE_SQL_NAME GET_OBJLIST_TEXT PURGE_SYNOPSIS_BY_MMON (noparms)
DECODE_GRANULARITY GET_OBJNUM QOSPSETPGAFLG
DELETE_CACHE_HISTORY GET_OBJ_ID_FOR_QUALIFIED_NAME RECLAIM_SYNOPSIS_SPACE (noparms)
DELETE_COL_USAGE GET_OLDEST_HISTORY_TIME REC_EXTN_CREATED
DELETE_FROM_USTATS GET_PARAM REMAP_INDEX_NAMES
DELETE_PARTITION_SYNOPSIS GET_PARAMETER_VAL RESTORE_STATS_OK
DELETE_PROC_RATE_CALLOUT GET_PARAM_DEFAULT REVOKE_CONC_PRIV_FROM_USER
DELETE_SINGLE_COL_GROUP_USAGE GET_PARAM_NUMVAL SAME_COL
DELETE_STATS_OPERATION GET_PARAM_PROP SAME_PART
DEL_SESSION_COL_STATS GET_PARTN_LOCK_FLAG SAME_TAB_COLS
DERIVE_GLOBAL_HISTOGRAM GET_PART_COLS SAVE_AS_PENDING_COL_STATS
DML_STATTAB_PREFS GET_PREFS SAVE_AS_PENDING_INDEX_STATS
DML_TABLE_PREFS GET_PREV_PART SAVE_AS_PENDING_TABLE_STATS
DQ GET_PROCRATE_ID SCHEMA_EXISTS
DROP_OLD_TEMP GET_SC_1201 SEGMENT_NUMBER_BLOCKS
DROP_TEMP GET_SC_1202 SET_ADVISOR_EXEC_PROPERTIES
DUMP_ALERT_TSES GET_SC_MAX SET_MON_COL_USAGE_FLAG
DUMP_PQ_SESSTAT GET_STALE_REASON SET_PARAM
DUMP_QUERY GET_STAT_EXTENSION SET_PROCESSING_RATE_AUX
DUMP_TRACE GET_SYNOPSIS_DEGREE SET_PROCESSING_RATE_CALLOUT
DUMP_TRACE_TS GET_SYNOPSIS_GROUP_NUM SET_TEMP_DOP
EXECUTE_ADVISOR_AUTO_TASK GET_TABLE_BLOCK_COUNT SIMILAR_OBJECT_EXISTS
EXPORT_COLSTATS_DIRECT GET_TABLE_DEGREE SIMILAR_SCHEMA_EXISTS
EXPORT_COL_USAGE GET_TABLE_PREFS STORE_SYSTEM_STATS
EXPORT_FXT_COLSTATS_DIRECT GET_TABLE_PROPERTY SYSAUX_OFFLINE
EXTRACT_STR GET_TABLE_STATS_SIMPLE TABHASGLOBALSTATS
FILL_IN_PARAMS GET_TAB_NUM_SEGMENTS TABLE_PREFS_EXISTS
FILL_IN_PARAMS_WITH_NO_PREFS GET_TAB_PROPERTY TABPARTSHAVESTATS
FILL_SYNOPSIS GET_USER_NUM TAB_STATS_STALE
FIXQ GRANT_CONC_PRIV_TO_USER TEST_MMON_AUTOJOB
FIX_IND_NAMES HYBRID_HIST_ENABLED TOP_FREQ_HIST_ENABLED
FIX_TAB_NAMES IMPORT_COL_USAGE_FOR_DIR TO_BOOL_TYPE
FLUSH_CACHE_STATS IMPORT_MON_INFO TO_CASCADE_TYPE
FORMAT_CACHE_ROWS IMPORT_STATS_HISTORY TO_DEGREE_TYPE
GATHER_FXT_STATS_OK IMPORT_SYNOPSES_STATS TO_ESTIMATE_PERCENT_TYPE
GATHER_INDEX INDEX_MAX_KEYSIZE_OK TO_STALE_PERCENT_TYPE
GATHER_PROCESSING_RATE_AUX INDHASGLOBALSTATS TO_TABLE_CACHED_BLOCKS_TYPE
GATHER_PROCESSING_RATE_JOB INDPARTSHAVESTATS TRACE_ERROR
GATHER_SCAN_RATE INIT_ID_MAP_TAB TRANSLATE_EV_COLNAME
GATHER_SCAN_RATE_BY_MMON INIT_OBJ_LIST_TAB TRANSLATE_EV_TO_TBL
GATHER_SQL_STATS INIT_STATS_ARG_TAB TRUNCATE_TABLE
GENERATE_OP_ID INSERT_ADVISOR_FILTER_OBJ TRUNCATE_TEMP
GET_ADVISOR_DEFAULT_INCLUDE INSERT_INTO_USTATS UPCASE
GET_ADVISOR_FINDING_DEF_ID INSERT_ONLY UPDATE_ADVISOR_DEFAULT_INCLUDE
GET_ADVISOR_FINDING_ID INS_SESSION_HG_STATS UPDATE_OP_TERMINATION_STATUS
GET_ADVISOR_FINDING_NAME INT_ASSERT UPDATE_SYNOPSIS_HEAD
GET_ADVISOR_NUM_FINDINGS IS_HADOOP_TABLE UPDATE_TARGET_LIST
GET_ADVISOR_OBJECT_TYPE_ID IS_INDEX_UNIQUE UPDATE_TASK_TERMINATION_STATUS
GET_ADVISOR_OPR_FILTER IS_MV_TABLE_BUSY VIEW_COLUMN_EXISTS
GET_ADVISOR_PARAM_VAL IS_PARAM_VALUE_DEFAULT WRITE_CLOB_TO_FILE
 
ADDOBJTONUMTAB (new 12.2)
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
 
ADVISOR_CHECK_OBJ_FILTER_VALID (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
Removes all Advisor filters (rules) dbms_stats.internal.advisor_clear_filters;
exec dbms_stats.internal.advisor_clear_filters;
 
ADVISOR_EXPAND_DIRECTIVES (new 12.2)
Undocumented dbms_stats_internal.advisor_expand_directives(
task_id        IN NUMBER,
operation_type IN VARCHAR2,
privilege      IN NUMBER);
TBD
 
ADVISOR_GET_MAX_OBJ_ID (new 12.2)
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 (new 12.2)
Undocumented dbms_stats_internal.advisor_get_object_number(
ownname IN VARCHAR2,
objname IN VARCHAR2)
RETURN dbms_stats_internal.numtab;
TBD
 
ADVISOR_GET_OBJ_DIR (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
Undocumented dbms_stats_internal.advisor_rule_findings(
task_id   IN NUMBER,
exec_name IN VARCHAR2,
rule_id   IN NUMBER);
TBD
 
ADVISOR_SETUP_OBJ_FILTER (new 12.2)
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 (new 12.2)
Undocumented dbms_stats_internal.advisor_skip_checked_rules(task_id IN NUMBER);
exec dbms_stats_internal.advisor_skip_checked_rules(350);
 
ADVISOR_TASK_EXISTS (new 12.2)
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
 
ALTER_PARAMETER_VAL (new 12.2)
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 (new 12.2)
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 (new 12.2)
BITOR function dbms_stats_internal.bitor(
v1 IN BINARY_INTEGER,
v2 IN BINARY_INTEGER)
RETURN BINARY_INTEGER;
SQL> ed
Wrote file afiedt.buf

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

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

SQL> ed
Wrote file afiedt.buf

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

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

SQL> ed
Wrote file afiedt.buf

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

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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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_PARTS
Undocumented but appears related to domain indexes and partitioned tables dbms_stats_internal.check_domidx_parts(idx_objno IN NUMBER, part_objno) RETURN NUMBER;
TBD
 
CHECK_GRANULARITY
Undocumented dbms_stats_internal.check_granularity(
granularity IN VARCHAR2,
granchk     IN VARCHAR2)
RETURN BOOLEAN;
TBD
 
CHECK_INCREMENTAL_STALENESS (new 12.2)
Undocumented dbms_stats_internal.check_incremental_staleness(
stalenessu   IN VARCHAR2,
stalenesschk IN VARCHAR2)
RETURN BOOLEAN;
TBD
 
CHECK_VOLATILE (new 12.2)
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
 
CLEAR_MON_COL_USAGE_FLAG (new 12.2)
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
 
CREATE_ADVISOR_OBJECT (new 12.2)
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 (new 12.2)
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_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);
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);
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
 
EXECUTE_ADVISOR_AUTO_TASK (new 12.2)
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 (new 12.2)
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 (new 12.2)
Undocumented dbms_stats_internal.fill_in_params RETURN dbms_stats_internal.pararray;
TBD
 
FILL_IN_PARAMS_WITH_NO_PREFS (new 12.2)
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);
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(10);
BEGIN dbms_stats_internal.gather_processing_rate_aux(10); END;

*
ERROR at line 1:
ORA-27476: "SYS"."ST$PRATE_GATHER" does not exist
ORA-06512: at "SYS.DBMS_ISCHED", line 4719
ORA-06512: at "SYS.DBMS_SCHEDULER", line 3305
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 15103
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 243
ORA-06512: at "SYS.DBMS_SCHEDULER", line 778
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 14984
ORA-06512: at line 1
 
GATHER_SCAN_RATE (new 12.2)
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 (new 12.2)
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 XMLTYPE,
err_code     OUT NUMBER,
err_mesg     OUT VARCHAR2);
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);
END;
/
DECLARE
*
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19213: error occurred in XML processing at lines 2
LPX-00231: invalid character 57 ('9') found in a Name or Nmtoken
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 13733
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 18929
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 8835
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
Undocumented dbms_stats_internal.get_advisor_num_findings(
task_id        IN NUMBER,
execution_name IN VARCHAR2)
RETURN NUMBER;
TBD
 
GET_ADVISOR_OBJECT_TYPE_ID (new 12.2)
Undocumented dbms_stats_internal.get_advisor_object_type_id(obj_type IN VARCHAR2)
RETURN NUMBER;
TBD
 
GET_ADVISOR_OPR_FILTER (new 12.2)
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 (new 12.2)
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 (new 12.2)
Undocumented dbms_stats_internal.get_advisor_rat_id(rtl_name IN VARCHAR2)
RETURN NUMBER;
TBD
 
GET_ADVISOR_REC_CBK (new 12.2)
Undocumented dbms_stats_internal.get_advisor_rec_cbk(rec_def_id IN NUMBER)
RETURN VARCHAR2;
TBD
 
GET_ADVISOR_REC_ID (new 12.2)
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 (new 12.2)
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 (new 12.2)
Undocumented dbms_stats_internal.get_advisor_report_cbk(
rule_id        IN NUMBER,
finding_def_id IN NUMBER)
RETURN VARCHAR2;
TBD
 
GET_ADVISOR_RTL_NAME (new 12.2)
Undocumented dbms_stats_internal.get_advisor_rtl_name(rat_id IN VARCHAR2)
RETURN NUMBER;
TBD
 
GET_ADVISOR_RULE_DESC (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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_COLSTATS
Undocumented dbms_stats_internal.get_agg_colstats(
tab_num    IN NUMBER,
total_rows IN NUMBER,
ndv_needed IN BOOLEAN,
dop        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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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_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_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_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_ID_FOR_QUALIFIED_NAME (new 12.2)
Returns the object number for an object identified by its fully qualified name: At least in theory as I cannot find a fully qualified name that returns anything but an exception. dbms_stats_internal.get_obj_id_for_qualified_name(qualified_name IN VARCHAR2)
RETURN NUMBER;
SQL> SELECT dbms_stats_internal.get_obj_id_for_qualified_name('SYS.TAB$')
  2  FROM dual;
SELECT dbms_stats_internal.get_obj_id_for_qualified_name('SYS.TAB$')
*
ERROR at line 1:
ORA-20001: object does not exist for the given qualified name
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 19362
 
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_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 (new 12.2)
  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;
/
 
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 (new 12.2)
  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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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_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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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
 
IS_HADOOP_TABLE (new 12.2)
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 (new 12.2)
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_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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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;
 
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
Undocumented dbms_stats_internal.purge_synopsis_by_mmon;
exec dbms_stats_internal.purge_synopsis_by_mmon;
 
QOSPSETPGAFLG (new 12.2)
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
g
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 (new 12.2)
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 (new 12.2)
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_tables
WHERE table_name = 'AIRPLANES;

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

SELECT degree
FROM dba_tables
WHERE table_name = 'AIRPLANES';

-- that was too easy but unfortunately no change observed ... more work to do.
 
SIMILAR_OBJECT_EXISTS (new 12.2)
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 (new 12.2)
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_MMON_AUTOJOB (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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 (new 12.2)
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_SYNOPSIS_HEAD
Undocumented dbms_stats_internal.update_synopsis_head(tobjn IN NUMBER, clist_syn IN dbms_stats_internal.chtab);
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_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 (new 12.2)
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
DBMS_STATS
DBMS_STATS_ADVISORS
Packages
X$KCFIO
What's New In 12cR1
What's New In 12cR2

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-2017 Daniel A. Morgan All Rights Reserved