Oracle DBMS_STATS_INTERNAL
Version 11.2.0.3
 
General Information
Speculations: TOBJN is Table Object Number, ICOLN is Index Column Number
Source {ORACLE_HOME}/rdbms/admin/prvtstas.plb
{ORACLE_HOME}/rdbms/admin/prvtstai.plb
Defined 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
Dependencies (102 objects) SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_STATS_INTERNAL'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_STATS_INTERNAL';
Security Model Owned by sys with no privileges granted
Subprograms
ADD_PARAM GET_COLTYPE OPEN_ALL_EMPTY_OBJS_CUR
AGGREGATE_INDSTATS GET_COUNT_OF_COLS OPEN_ALL_OBJECTS_CUR
AGG_PARTITION_ROWCNT GET_CURRENT_SESSION_ID OPEN_ALL_STALE_OBJS_CUR
ALTER_COL_TRACKING_LEVEL GET_DB_BLOCK_SIZE OPEN_COLSTATS_DICT_CUR
CAN_DERIVE_COL_HISTOGRAM GET_DEFAULT_DOP OPEN_COLSTATS_HIST_CUR
CCT GET_FIRST_PART_COL OPEN_EXTN_HIST_CUR
CHECK_ANALYZE_DONE GET_FXT_OBJ OPEN_FXT_COLSTATS_HIST_CUR
CHECK_DOMIDX_PARTS GET_FXT_TYP OPEN_FXT_STATS_DICT_CUR
CHECK_GRANULARITY GET_HIST_DENSITY OPEN_FXT_STATS_HIST_CUR
COLHASBASESTATS GET_INDEX_BLOCK_COUNT OPEN_GET_IND_PARTS_CUR
COLHASHISTOGRAM GET_INDEX_LOCK_FLAG OPEN_GET_IND_SUBPARTS_CUR
COLUMN_EXISTS GET_INTCOL OPEN_GET_TARGET_PARTITIONS_CUR
COMPOSE_HASHVAL_CLOB GET_IOT_MAPPING_TABLE OPEN_GET_TARGET_SUBPARTS_CUR
CONTROL_PARALLEL GET_MBRC OPEN_TAB_STATS_DICT_CUR
CREATE_TEMP GET_MV_ENQUE OPEN_TAB_STATS_HIST_CUR
DECODE_GRANULARITY GET_OBJNUM PARSE_HASHVAL
DELETE_CACHE_HISTORY GET_OLDEST_HISTORY_TIME PART_TYPES
DELETE_COL_USAGE GET_PARAM PENDING_STATS_ENABLED
DELETE_FROM_USTATS GET_PARAMETER_VAL POPULATE_SYNOPSIS_PARTGRP
DELETE_PARTITION_SYNOPSIS GET_PARAM_PROP POPULATE_TEMP_INSERT
DELETE_SINGLE_COL_GROUP_USAGE GET_PARTN_LOCK_FLAG PQFLAGS
DELETE_TABLE_SYNOPSIS GET_PART_COLS PURGE_PENDING_STATS
DERIVE_GLOBAL_HISTOGRAM GET_PREFS PURGE_STATS_AUX
DML_STATTAB_PREFS GET_PREV_PART PURGE_STAT_TABLE
DML_TABLE_PREFS GET_SYNOPSIS_BLKCNT RESTORE_STATS_OK
DQ GET_SYNOPSIS_GROUP_NUM SAME_COL
DROP_OLD_TEMP GET_TABLE_BLOCK_COUNT SAME_PART
DROP_TEMP GET_TABLE_DEGREE SAVE_AS_PENDING_COL_STATS
DUMP_ALERT_TSES GET_TABLE_PROPERTY SAVE_AS_PENDING_INDEX_STATS
DUMP_PQ_SESSTAT GET_TABLE_STATS_SIMPLE SAVE_AS_PENDING_TABLE_STATS
DUMP_QUERY GET_TAB_PROPERTY SCHEMA_EXISTS
DUMP_TRACE GET_USER_NUM SEGMENT_NUMBER_BLOCKS
DUMP_TRACE_TS IMPORT_SYNOPSES_STATS SET_PARAM
EXPORT_COLSTATS_DIRECT INDEX_MAX_KEYSIZE_OK SET_TEMP_DOP
EXPORT_FXT_COLSTATS_DIRECT INDHASGLOBALSTATS STORE_SYSTEM_STATS
EXPORT_SYNOPSIS_HEAD INDPARTSHAVESTATS SYSAUX_OFFLINE
FILL_SYNOPSIS INSERT_INTO_USTATS TABHASGLOBALSTATS
FLUSH_CACHE_STATS IS_INDEX_UNIQUE TABPARTSHAVESTATS
FORMAT_CACHE_ROWS IS_MV_TABLE_BUSY TO_BOOL_TYPE
GATHER_FXT_STATS_OK IS_PARTGRP_ONE_TO_ONE TO_CASCADE_TYPE
GATHER_INDEX IS_PARTITIONED_TAB TO_STALE_PERCENT_TYPE
GATHER_SQL_STATS IS_PART_DEFAULT TRACE_ERROR
GENERATE_GROUP_LEVEL_SYNOPSIS IS_PART_TYP_SAME TRANSLATE_EV_COLNAME
GET_AGG_COLSTATS IS_STALE TRANSLATE_EV_TO_TBL
GET_AGG_NDV IS_TEMP_TAB TRUNCATE_TEMP
GET_BLKCNT IS_URGENT_ERROR UPDATE_SYNOPSIS_HEAD
GET_CHTAB MANAGE_COL_TRACKING_LEVEL_JOB UPDATE_TARGET_LIST
GET_COLNAME OBJECT_EXISTS VIEW_COLUMN_EXISTS
GET_COLNUM    
 
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;
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 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;
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;
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;
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;
/
 
COMPOSE_HASHVAL_CLOB
Undocumented dbms_stats_internal.compose_hashval_clob(
tobjn     IN NUMBER,
group_num IN NUMBER,
intcols   IN dbms_stats_internal.numtab,
dop       IN NUMBER);
RETURN dbms_stats_internal.clobtab;
TBD
 
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);
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,
fobjn     IN NUMBER,
clist_syn IN dbms_stats_internal.chtab);
TBD
 
DELETE_SINGLE_COL_GROUP_USAGE
Undocumented dbms_stats_internal.delete_single_col_group_usage(
objn      IN NUMBER,
col_group IN VARCHAR2);
TBD
 
DELETE_TABLE_SYNOPSIS
Undocumented dbms_stats_internal.delete_table_synopsis(
tobjn  IN NUMBER,
fobjn  IN NUMBER,
intcol IN NUMBER);
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
 
EXPORT_COLSTATS_DIRECT
Undocumented dbms_stats_internal.export_colstats_direct(
owner       IN VARCHAR2,
tabname     IN VARCHAR2,
colname
partname
stattab
statid
statown
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
stattab
statid
statown
version IN NUMBER);
TBD
 
EXPORT_SYNOPSIS_HEAD
Undocumented dbms_stats_internal.export_synopsis_head(
owner    IN VARCHAR2,
tabname  IN VARCHAR2,
partname IN VARCHAR2,
stattab  IN VARCHAR2,
statid   IN VARCHAR2,
statown  IN VARCHAR2,
version  IN NUMBER);
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
 
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
demo here
 
GATHER_INDEX
Undocumented dbms_stats_internal.gather_index(
tobjn     IN NUMBER,
get_index IN VARCHAR2)
RETURN VARCHAR2;
TBD
 
GATHER_SQL_STATS
Undocumented dbms_stats_internal.gather_sql_stats(
TBD
 
GENERATE_GROUP_LEVEL_SYNOPSIS
Undocumented dbms_stats_internal.generate_group_level_synopsis(
TBD
 
GET_AGG_COLSTATS
Undocumented dbms_stats_internal.get_agg_colstats(
TBD
 
GET_AGG_NDV
Undocumented dbms_stats_internal.get_agg_ndv(
TBD
 
GET_BLKCNT
Undocumented dbms_stats_internal.get_blkcnt(tobjn IN NUMBER) RETURN NUMBER;
TBD
 
GET_CHTAB (dropped in 12cR1)
Undocumented dbms_stats_internal.get_chtab RETURN dbms_stats_internal.chtab;
DECLARE
 l_chtab dbms_stats_internal.chtab;
BEGIN
  l_chtab := dbms_stats_internal.get_chtab;
END;
/
 *
ERROR at line 5:
ORA-06550: line 1103, column 12:
PLS-00653: aggregate/table functions are not allowed in PL/SQL scope
 
GET_COLNAME
Undocumented dbms_stats_internal.get_colname(
obj_num     IN NUMBER,
intcol_num  IN NUMBER,
virtual_col IN VARCHAR2)
RETURN VARCHAR2;
TBD
 
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;
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;
SELECT dbms_stats_internal.get_coltype('UWCLASS', 'SERVERS', 'SRVR_ID') FROM dual;

SELECT dbms_stats_internal.get_coltype('UWCLASS', 'SERVERS', 'NETADDRESS') FROM dual;
 
GET_COUNT_OF_COLS
In theory it would seem that it counts virtual and non-virtual columns but the reality is that it appears to have a bug. But given that this is a totally undocumented internal package I think I'll hold off on 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;
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_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_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
Returns the first partition key column from a partitioned table dbms_stats_internal.get_first_part_col(
ownname IN VARCHAR2,
tabname IN VARCHAR2,
objtype IN VARCHAR2)
RETURN VARCHAR2;
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;
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;
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;
SELECT object_id
FROM dba_objects
WHERE object_name = 'PK_AIRPLANES';

SELECT dbms_stats_internal.get_index_lock_flag(76841)
FROM dual;
 
GET_INTCOL
Appears to returns the position in a table of the identified column dbms_stats_internal.get_intcol(
owner   IN VARCHAR2
tabname IN VARCHAR2,
colname IN VARCHAR2)
RETURN BINARY_INTEGER;
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;
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;
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_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;
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_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

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,
dvalue  IN  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;
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_SYNOPSIS_BLKCNT
Undocumented dbms_stats_internal.get_synopsis_blkcnt(tobjn IN NUMBER, fobjn 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;
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;
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_PROPERTY
Undocumented dbms_stats_internal.get_table_property(objn IN NUMBER) RETURN NUMBER;
SELECT object_id
FROM dba_objects
WHERE object_name = 'AIRPLANES';

SELECT dbms_stats_internal.get_table_property(76840)
FROM dual;
 
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);
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('UWCLASS')
FROM dual;
 
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;
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
Undocumented dbms_stats_internal.indhasglobalstats(bobjnum IN NUMBER) RETURN BOOLEAN;
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
 
INSERT_INTO_USTATS
Undocumented dbms_stats_internal.insert_into_ustats(
owner   IN VARCHAR2,
indname IN VARCHAR2,
pname   IN VARCHAR2,
bobjnum IN NUMBER);
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;
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;
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;
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;
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_STALE
Undocumented dbms_stats_internal.is_stale(
tobj         IN NUMBER,
rows_changed IN NUMBER,
rowcnt       IN NUMBER)
RETURN VARCHAR2;
TBD
 
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;
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;
/
 
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
 
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;
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
Undocumented 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
Undocumented 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
Undocumented dbms_stats_internal.open_al_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);
TBD
 
OPEN_COLSTATS_DICT_CUR
Undocumented 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
Undocumented 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_EXTN_HIST_CUR
Undocumented 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
Undocumented 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
Undocumented 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
Undocumented 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
Undocumented 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
Undocumented 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
Undocumented 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_TAB_STATS_DICT_CUR
Undocumented 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
Undocumented 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);
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
Undocumented 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_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);
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
Undocumented dbms_stats_internal.pqflags RETURN BINARY_INTEGER
SELECT dbms_stats_internal.pqflags
FROM dual;
 
PURGE_PENDING_STATS
Undocumented dbms_stats_internal.purge_pending_stats(
object_num IN NUMBER,
column_num IN NUMBER,
options    IN NUMBER);
TBD
 
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
Undocumented dbms_stats_internal.purge_stats_table(
statown  IN VARCHAR2,
stattab  IN VARCHAR2,
p_statid IN VARCHAR2);
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;
/
 
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
 
SAVE_AS_PENDING_COL_STATS
Undocumented dbms_stats_internal.save_as_pending_col_stats(
cstats IN dbms_stats_internal.coltab)
RETURN BOOLEAN;
???
 
SAVE_AS_PENDING_INDEX_STATS
Undocumented dbms_stats_internal.save_as_pending_index_stats(
istats IN dbms_stats_internal.indtab)
RETURN BOOLEAN;
???
 
SAVE_AS_PENDING_TABLE_STATS
Undocumented dbms_stats_internal.save_as_pending_table_stats(
tstats IN dbms_stats_internal.tabtab)
RETURN BOOLEAN;
???
 
SCHEMA_EXISTS
Returns TRUE if the named schema exists dbms_stats_internal.schema_exists(uname IN VARCHAR2) RETURN BOOLEAN;
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 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_TEMP_DOP
Undocumented dbms_stats_internal.set_temp_dop(
ttabname    IN VARCHAR2,
degree      IN NUMBER,
alter_table IN BOOLEAN);
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 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
Undocumented dbms_stats_internal.sysaux_offline RETURN BOOLEAN;
???
 
TABHASGLOBALSTATS
Undocumented dbms_stats_internal.tabHasGlobalStats(
owner   IN VARCHAR2,
tabname IN VARCHAR2)
RETURN BOOLEAN;
???
 
TABPARTSHAVESTATS
Undocumented dbms_stats_internal.tabPartsHaveStats(
owner   IN VARCHAR2,
tabname IN VARCHAR2,
pname   IN VARCHAR2)
RETURN BOOLEAN;
???
 
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;
???
 
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;
What is an EV?

SELECT dbms_stats_internal.translate_ev_to_tbl('UWCLASS', '???')
FROM dual;
 
TRUNCATE_TEMP
Undocumented dbms_stats_internal.truncate_temp(ttabname IN VARCHAR2);
create a global temporary table and test this

exec dbms_stats_internal.truncate_temp('???');
 
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
 
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
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-2013 Daniel A. Morgan All Rights Reserved