Oracle DBMS_STATS_INTERNAL
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. 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.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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;
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 140 objects
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
ADD_PARAM GET_DEFAULT_DOP OPEN_ALL_EMPTY_OBJS_CUR
AGGREGATE_INDSTATS GET_FIRST_PART_COL OPEN_ALL_OBJECTS_CUR
AGG_PARTITION_ROWCNT GET_FXT_OBJ OPEN_ALL_STALE_OBJS_CUR
ALTER_COL_TRACKING_LEVEL GET_FXT_TYP OPEN_COLSTATS_DICT_CUR
CAN_DERIVE_COL_HISTOGRAM GET_HIST_DENSITY OPEN_COLSTATS_HIST_CUR
CCT GET_IDX_TABPART OPEN_CUR_ALL_LOCAL_INDEX_PARTS
CHECK_ANALYZE_DONE GET_INDEX_BLOCK_COUNT OPEN_EXTN_HIST_CUR
CHECK_DOMIDX_PARTS GET_INDEX_LOCK_FLAG OPEN_FXT_COLSTATS_HIST_CUR
CHECK_GRANULARITY GET_INDEX_PART_NAME OPEN_FXT_STATS_DICT_CUR
COLHASBASESTATS GET_INDSTATS_DICT OPEN_FXT_STATS_HIST_CUR
COLHASHISTOGRAM GET_IND_COLS OPEN_GET_IND_PARTS_CUR
COLUMN_EXISTS GET_INTCOL OPEN_GET_IND_SUBPARTS_CUR
COL_STATS_FROM_LOAD GET_IOT_MAPPING_TABLE OPEN_GET_TARGET_PARTITIONS_CUR
COMPOSE_HASHVAL_CLOB_REC GET_MBRC OPEN_GET_TARGET_SUBPARTS_CUR
COMPUTE_DOP GET_MV_ENQUE OPEN_OPERATIONS_CUR
CONTROL_PARALLEL GET_OBJLIST_TEXT OPEN_TAB_STATS_DICT_CUR
CREATE_TEMP GET_OBJNUM OPEN_TAB_STATS_HIST_CUR
DECODE_GRANULARITY GET_OLDEST_HISTORY_TIME PARSE_HASHVAL
DELETE_CACHE_HISTORY GET_PARAM PART_TYPES
DELETE_COL_USAGE GET_PARAMETER_VAL PENDING_STATS_ENABLED
DELETE_FROM_USTATS GET_PARAM_NUMVAL POPULATE_OBJ_LIST_TAB
DELETE_PARTITION_SYNOPSIS GET_PARAM_PROP POPULATE_SYNOPSIS_PARTGRP
DELETE_PROC_RATE_CALLOUT GET_PARTN_LOCK_FLAG POPULATE_TEMP_INSERT
DELETE_SINGLE_COL_GROUP_USAGE GET_PART_COLS PQFLAGS
DELETE_STATS_OPERATION GET_PREFS PREP_INSERT_SYNOPSIS_ONLINE
DEL_SESSION_COL_STATS GET_PREV_PART PURGE_PENDING_STATS
DERIVE_GLOBAL_HISTOGRAM GET_PROCRATE_ID PURGE_STATS_AUX
DML_STATTAB_PREFS GET_STAT_EXTENSION PURGE_STAT_TABLE
DML_TABLE_PREFS GET_SYNOPSIS_DEGREE PURGE_SYNOPSIS
DQ GET_SYNOPSIS_GROUP_NUM RECLAIM_SYNOPSIS_SPACE
DROP_OLD_TEMP GET_TABLE_BLOCK_COUNT REC_EXTN_CREATED
DROP_TEMP GET_TABLE_DEGREE REMAP_INDEX_NAMES
DUMP_ALERT_TSES GET_TABLE_PREFS RESTORE_STATS_OK
DUMP_PQ_SESSTAT GET_TABLE_PROPERTY REVOKE_CONC_PRIV_FROM_USER
DUMP_QUERY GET_TABLE_STATS_SIMPLE SAME_COL
DUMP_TRACE GET_TAB_PROPERTY SAME_PART
DUMP_TRACE_TS GET_USER_NUM SAME_TAB_COLS
EST_SYNOPSIS_SIZE GRANT_CONC_PRIV_TO_USER SAVE_AS_PENDING_COL_STATS
EXPORT_COLSTATS_DIRECT HYBRID_HIST_ENABLED SAVE_AS_PENDING_INDEX_STATS
EXPORT_FXT_COLSTATS_DIRECT IMPORT_MON_INFO SAVE_AS_PENDING_TABLE_STATS
EXTRACT_STR IMPORT_STATS_HISTORY SCHEMA_EXISTS
FILL_SYNOPSIS IMPORT_SYNOPSES_STATS SEGMENT_NUMBER_BLOCKS
FIXQ INDEX_MAX_KEYSIZE_OK SET_PARAM
FIX_IND_NAMES INDHASGLOBALSTATS SET_PROCESSING_RATE_AUX
FIX_TAB_NAMES INDPARTSHAVESTATS SET_PROCESSING_RATE_CALLOUT
FLUSH_CACHE_STATS INIT_ID_MAP_TAB SET_TEMP_DOP
FORMAT_CACHE_ROWS INIT_OBJ_LIST_TAB STORE_SYSTEM_STATS
GATHER_FXT_STATS_OK INSERT_INTO_USTATS SYSAUX_OFFLINE
GATHER_INDEX INSERT_ONLY TABHASGLOBALSTATS
GATHER_PROCESSING_RATE_AUX INS_SESSION_HG_STATS TABLE_PREFS_EXISTS
GATHER_PROCESSING_RATE_JOB INT_ASSERT TABPARTSHAVESTATS
GATHER_SQL_STATS IS_INDEX_UNIQUE TAB_STATS_STALE
GENERATE_GROUP_LEVEL_SYNOPSIS IS_MV_TABLE_BUSY TOP_FREQ_HIST_ENABLED
GENERATE_OP_ID IS_PARTGRP_ONE_TO_ONE TO_BOOL_TYPE
GET_AGG_COLSTATS IS_PARTITIONED_TAB TO_CASCADE_TYPE
GET_AGG_NDV IS_PART_DEFAULT TO_STALE_PERCENT_TYPE
GET_BLKCNT IS_PART_TYP_SAME TRACE_ERROR
GET_CALIB_OPID_CALLOUT IS_RES_MAN_ON TRANSLATE_EV_COLNAME
GET_COLNAME IS_STALE TRANSLATE_EV_TO_TBL
GET_COLNUM IS_TABLE_EMPTY TRUNCATE_TABLE
GET_COLTYPE IS_TEMP_TAB TRUNCATE_TEMP
GET_COL_EXPR IS_URGENT_ERROR UPDATE_OP_TERMINATION_STATUS
GET_COUNT_OF_COLS JOB_CLASS_EXISTS UPDATE_SYNOPSIS_HEAD
GET_CURRENT_JOB_NAME MANAGE_COL_TRACKING_LEVEL_JOB UPDATE_TARGET_LIST
GET_CURRENT_SESSION_ID MIN_NUM UPDATE_TASK_TERMINATION_STATUS
GET_DB_BLOCK_SIZE MONITORING_MAN_SIGNAL_SAFEMODE VIEW_COLUMN_EXISTS
GET_DEFAULT$ OBJECT_EXISTS  
 
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
 
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_COL_TRACKING_LEVEL
Undocumented dbms_stats_internal.alter_col_tracking_level(
level     IN  NUMBER,
session   IN  BOOLEAN,
cur_level OUT NUMBER);
TBD
 
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_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 to relate 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
 
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 (new 12.1)
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 (new 12.1 parameters)
Undocumented dbms_stats_internal.compose_hashval_clob_rec(
tobjn     IN NUMBER,
fobjn     IN NUMBER,
group_num IN NUMBER);
RETURN dbms_stats_internal.synhashvaltab;
TBD
 
COMPUTE_DOP (new 12.1)
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_TEMP
Undocumented dbms_stats_internal.create_temp(
seltxt   IN  VARCHAR2,
fromtxt  IN  VARCHAR2,
wheretxt IN  VARCHAR2,
ttabname OUT VARCHAR2,
uname    IN  VARCHAR2);
TBD
 
DECODE_GRANULARITY
Undocumented dbms_stats_internal.decode_granularity(
tobjn       IN NUMBER,
granularity IN VARCHAR2)
RETURN BINARY_INTEGER;
DECLARE
 retVal PLS_INTEGER;
BEGIN
  retVal := dbms_stats_internal.decode_granularity(76842, 'Z');
  dbms_output.put_line(retVal);
END;
/
 
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 (new 12.1 parameters)
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 (new 12.1)
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 (new 12.1)
Undocumented dbms_stats_internal.delete_stats_operation(opid IN NUMBER);
exec dbms_stats_internal.delete_stats_operation(1);
 
DEL_SESSION_COL_STATS (new 12.1)
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
 
EST_SYNOPSIS_SIZE (new 12.1)
Undocumented dbms_stats_internal.est_synopsis_size(synopsis_subpart_name IN VARCHAR2) RETURN NUMBER;
TBD
 
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_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 (new 12.1)
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_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 (new 12.1)
Undocumented dbms_stats_internal.fixq(str IN VARCHAR2) RETURN VARCHAR2;
TBD
 
FIX_IND_NAMES (new 12.1)
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 (new 12.1)
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 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 (new 12.1)
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 (new 12.1)
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_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_GROUP_LEVEL_SYNOPSIS
Undocumented dbms_stats_internal.generate_group_level_synopsis(tab_num IN NUMBER);
exec dbms_stats_internal.generate_group_level_synopsis(18);
 
GENERATE_OP_ID (new 12.1)
Undocumented dbms_stats_internal.generate_op_id RETURN NUMBER;
SELECT dbms_stats_internal.generate_op_id
FROM 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_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 (new 12.1)
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 (new 12.1)
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 (new 12.1)
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$ (new 12.1)
Undocumented dbms_stats_internal.get_default$(colrowid IN ROWID) RETURN CLOB;
TBD
 
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_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@pdborcl 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 (new 12.1)
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@pdborcl 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 (new 12.1)
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 (new 12.1)
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 (new 12.1)
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 (new 12.1)
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_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_NUMVAL (new 12.1)
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@pdborcl

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@pdborcl 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 (new 12.1)
Undocumented dbms_stats_internal.get_procrate_id(statname IN VARCHAR2) RETURN NUMBER;
TBD
 
GET_STAT_EXTENSION (new 12.1)
Undocumented dbms_stats_internal.get_stat_extension(
owner   IN VARCHAR2,
tab     IN VARCHAR2,
xtnname IN VARCHAR2)
RETURN CLOB;
TBD
 
GET_SYNOPSIS_DEGREE (new 12.1)
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@pdborcl 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 (new 12.1)
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_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 (new 12.1)
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 (new 12.1)
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_MON_INFO (new 12.1)
Undocumented dbms_stats_internal.import_mon_info(
stattabq     IN VARCHAR2,
objlist_tabq IN VARCHAR2);
TBD
 
IMPORT_STATS_HISTORY (new 12.1)
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 (new 12.1)
Undocumented dbms_stats_internal.init_id_map_tab;
exec dbms_stats_internal.init_id_map_tab;
 
INIT_OBJ_LIST_TAB (new 12.1)
Undocumented dbms_stats_internal.init_obj_list_tab;
exec dbms_stats_internal.init_obj_list_tab;
 
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 (new 12.1)
Undocumented dbms_stats_internal.insert_only(objn IN NUMBER) RETURN BOOLEAN;
TBD
 
INS_SESSION_HG_STATS (new 12.1)
Undocumented dbms_stats_internal.ins_session_hg_stats(cstats IN dbms_stats_internal.coltab);
TBD
 
INT_ASSERT (new 12.1)
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_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_PARTGRP_ONE_TO_ONE
Undocumented dbms_stats_internal.is_partGrp_one_to_one(tab_num IN NUMBER) RETURN BOOLEAN;
BEGIN
  IF dbms_stats_internal.is_partgrp_one_to_one(76840) THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/
 
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@pdborcl 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@pdborcl 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 (new 12.1)
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 (new 12.1)
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 (new 12.1)
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 (new 12.1)
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 (new 12.1)
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_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 (new 12.1)
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_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 (new 12.1)
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
 
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@pdborcl 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 (new 12.1)
Undocumented dbms_stats_internal.populate_obj_list_tab(
ownu IN VARCHAR2,
tabu IN VARCHAR2);
TBD
 
POPULATE_SYNOPSIS_PARTGRP
Populates sys.wri$_optstat_synopsis_partgrp, the implications of which, are far from clear dbms_stats_internal.populate_synopsis_partgrp(ownname IN VARCHAR2, tabname IN VARCHAR2);
conn sys@pdborcl as sysdba

SELECT COUNT(*)
FROM sys.wri$_optstat_synopsis_partgrp;

exec dbms_stats_internal.populate_synopsis_partgrp('SH', 'SALES');

SELECT COUNT(*)
FROM sys.wri$_optstat_synopsis_partgrp;
 
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 (new 12.1)
Undocumented dbms_stats_internal.prep_insret_synopsis_online(
tobjn     IN NUMBER,
fobjn     IN NUMBER,
clist_syn IN sys.dbmsstatnumtab);
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 (new 12.1)
Undocumented dbms_stats_internal.purge_synopsis(partitionBatched IN BOOLEAN);
exec dbms_stats_internal.purge_synopsis(TRUE);
 
RECLAIM_SYNOPSIS_SPACE (new 12.1)
Undocumented dbms_stats_internal.reclaim_synopsis_space;
exec dbms_stats_internal.reclaim_synopsis_space;
 
REC_EXTN_CREATED (new 12.1)
Undocumented dbms_stats_internal.rec_extn_created(
objn      IN NUMBER,
col_group IN VARCHAR2);
TBD
 
REMAP_INDEX_NAMES (new 12.1)
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
 
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 (new 12.1)
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 (new 12.1)
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_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 (new 12.1)
Undocumented dbms_stats_internal.set_processing_rate_aux(
opid     IN NUMBER,
procrate IN NUMBER,
originid IN NUMBER);
TBD
 
SET_PROCESSING_RATE_CALLOUT (new 12.1)
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.
 
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@pdborcl 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 (new 12.1)
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@pdborcl 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 (new 12.1)
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
 
TOP_FREQ_HIST_ENABLED (new 12.1)
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_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?
 
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 (new 12.1)
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
 
UPDATE_OP_TERMINATION_STATUS (new 12.1)
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 (new 12.1)
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;
/

Related Topics
DBMS_STATS
Packages
X$KCFIO

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