Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows
Purpose
Internal support package for CBO optimizer stats collection in conjunction with DBMS_STATS.
Because this package is completely undocumented a few small liberties have been in the object description column based on either the object's name or the results of working with the object itself.
Some errors should be assumed and corrections are welcomed.
AUTHID
DEFINER
Data Types
AGGCOLREC
COLHISTTAB
NUMTAB
AGGCOLTAB
COLREC
TABREC
CHREC
COLTAB
TABTAB
CHTAB (table of CHREC)
IDENTAB
T_CACHESTATIN
CLOBTAB
INDREC
T_CACHESTATOUT
COLHISTREC
INDTAB
T_CACHESTATOUTSET
Speculations: TOBJN is Table Object Number, ICOLN is Internal Column Number
TYPE aggcolrec IS RECORD(
intcol# NUMBER
nnv NUMBER
nmin NUMBER
nmax NUMBER
minval RAW
maxval RAW
acl NUMBER
ndv NUMBER);
TYPE aggcoltab IS TABLE OF aggcolrec;
TYPE numtab IS ...
TYPE pararray IS ...
TYPE reportingcontext IS ...
TYPE varchartab IS ...
sys.ctab IS TABLE OF sys.crec;
Dependencies
SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_STATS_INTERNAL'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_STATS_INTERNAL';
Returns 212 objects
DBA_OPTSTAT_OPERATION_TASKS
DBA_AUTOTASK_OPERATION
V_$STATS_ADVISOR_RECS
DBA_AUTOTASK_JOB_HISTORY
V_$STATS_ADVISOR_FINDINGS
V_$STATS_ADVISOR_RULES
Documented
No
Exceptions
Error Code
Reason
ORA-02149
Specified partition does not exist
ORA-27475
unknown job <job_name_string>
First Available
8.1.5
Security Model
Owned by sys with no privileges granted.
Direct access to this package is prevented by an Accessible By clause.
dbms_stats_internal.adjust_hf_max_run_time(timeout_cnt OUT NUMBER) RETURN BOOLEAN;
DECLARE
outVal NUMBER;
BEGIN
IF dbms_stats_internal.adjust_hf_max_run_time(outVal) THEN
dbms_output.put_line(TO_CHAR(outVal));
ELSE
dbms_output.put_line('F');
END IF;
END;
/ F
Returns true if the identified advisor task has resumed
Returns ORA-01403 if the task is not found.
dbms_stats_internal.advisor_check_resume(task_id IN NUMBER) RETURN BOOLEAN;
BEGIN
IF NOT dbms_stats_internal.advisor_check_resume(350) THEN
dbms_output.put_line('Advisor Task ' || TO_CHAR(350) || ' Has Not Resumed');
END IF;
END;
/
dbms_stats_internal.advisor_get_obj_dir(
ownname IN VARCHAR2,
objname IN VARCHAR2,
rule_name IN VARCHAR2,
opr_type IN VARCHAR2,
task_id IN NUMBER,
exist OUT BOOLEAN,
include OUT BOOLEAN,
curr_dir_name OUT VARCHAR2);
dbms_stats_internal.advisor_get_opr_dir(
opr_name IN VARCHAR2,
opr_notes IN VARCHAR2,
rule_name IN VARCHAR2,
opr_type IN VARCHAR2,
task_id IN NUMBER,
exist OUT BOOLEAN,
include OUT BOOLEAN,
curr_dir_name OUT VARCHAR2);
dbms_stats_internal.advisor_get_rule_dir(
rule_name IN VARCHAR2,
operation_type IN VARCHAR2,
task_id IN NUMBER,
exist OUT BOOLEAN,
include OUT BOOLEAN,
curr_dir_name OUT VARCHAR2);
dbms_stats_internal.advisor_is_system_rule(rule_id IN NUMBER) RETURN BOOLEAN;
BEGIN
IF dbms_stats_internal.advisor_is_system_rule(350) THEN
dbms_output.put_line('Exists');
ELSE
dbms_output.put_line('Does Not Exist');
END IF;
END;
/ Does Not Exist
dbms_stats_internal.advisor_report_header(
report_xml IN OUT XMLTYPE,
task_id IN NUMBER,
execution_name IN VARCHAR2,
level_flags IN NUMBER,
privilege IN NUMBER);
dbms_stats_internal.advisor_report_summary(
report_xml IN OUT XMLTYPE,
task_id IN NUMBER,
execution_name IN VARCHAR2,
level_flags IN NUMBER,
privilege IN NUMBER,
num_findings IN NUMBER);
Determines whether the specified advisor task exists
dbms_stats_internal.advisor_task_exists(task_name IN VARCHAR2) RETURN BOOLEAN;
BEGIN
IF dbms_stats_internal.advisor_task_exists(3) THEN
dbms_output.put_line('Exists');
ELSE
dbms_output.put_line('Does Not Exist');
END IF;
END;
/ Does Not Exist
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);
dbms_stats_internal.agg_pdb_shard_tstats(
owner IN VARCHAR2,
tab_name IN VARCHAR2,
tab_type IN NUMBER,
nrows OUT NUMBER,
nblks OUT NUMBER,
im_imcu_count OUT NUMBER,
im_block_count OUT NUMBER,
pdb_or_shard_count IN NUMBER);
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);
dbms_stats_internal.check_advisor_opr_filter(
rule_id IN NUMBER
task_id IN NUMBER
opr_type IN VARCHAR2
opr_name IN VARCHAR2
target IN VARCHAR2
param_val IN VARCHAR2
username IN VARCHAR2
privilege IN NUMBER)
RETURN VARCHAR2;
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,
reporting_mode IN BOOLEAN,
op_id IN NUMBER,
not_found OUT BOOLEAN);
RETURN BOOLEAN;
SELECT object_id
FROM dba_objects
WHERE object_name = 'TAB$';
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;
/
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;
/
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;
/ The LATITUDE Column Does Not Exist
dbms_stats_internal.compose_hashval_clob_rec(
tobjn IN NUMBER,
fobjn IN NUMBER,
group_num IN NUMBER);
RETURN PIPELINED dbms_stats_internal.synhashvaltab;
Converts the difference between to timestamps with time zone into seconds
dbms_stats_internal.convert_interval_to_seconds(
start_time IN TIMESTAMP WITH TIME ZONE,
end_time IN TIMESTAMP WITH TIME ZONE)
RETURN NUMBER;
DECLARE
st TIMESTAMP WITH TIME ZONE := STANDARD.SYS_AT_TIME_ZONE(SYSTIMESTAMP, 'US/Pacific');
et TIMESTAMP WITH TIME ZONE := STANDARD.SYS_AT_TIME_ZONE(SYSTIMESTAMP, 'US/Pacific')+1;
rn NUMBER;
BEGIN
rn := dbms_stats_internal.convert_interval_to_seconds(st, et);
dbms_output.put_line(TO_CHAR(rn));
END;
/ 61199.790662
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);
dbms_stats_internal.derive_global_histogram(
tobjn IN NUMBER,
intcoln IN NUMBER,
cht IN OUT dbms_stats_internal.colhisttab,
mnb IN NUMBER,
cind IN NUMBER,
freq IN OUT BOOLEAN,
ndv IN NUMBER,
nmin IN NUMBER,
nnv IN NUMBER,
ssize IN OUT NUMBER,
ssizesq IN OUT NUMBER,
popcnt IN OUT NUMBER,
popcntsq IN OUT NUMBER,
auto_sample IN BOOLEAN);
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);
dbms_stats_internal.dml_table_prefs(
dml_type IN VARCHAR2,
ownnameu IN VARCHAR2,
tabnameu IN VARCHAR2,
pnameu IN VARCHAR2,
valcharu IN VARCHAR2,
isdefault IN NUMBER);
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 */)));
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);
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);
dbms_stats_internal.fill_syopsis(
tobjn IN NUMBER,
group_num IN NUMBER,
intcol_num IN NUMBER,
cursplit IN NUMBER,
analyze_time IN TIMESTAMP WITH TIME ZONE,
hashval IN CLOB,
zero_col_stats IN BOOLEAN);
DECLARE
b BOOLEAN;
BEGIN
IF dbms_stats_internal.gather_fxt_stats_ok(4294950912) THEN
dbms_output.put_line('T');
ELSE
dbms_output.put_line('F');
END IF;
END;
/ T
dbms_stats_internal.gather_sql_stats(
sql_text IN CLOB,
user_name IN VARCHAR2,
bind_list IN sys.sql_binds,
options IN VARCHAR2,
rept IN OUT sys.xmltype,
err_code OUT NUMBER,
err_mesg OUT VARCHAR2,
exec_userid IN NUMBER);
DECLARE
rept XMLTYPE;
ecode NUMBER;
emesg VARCHAR2(60);
BEGIN
dbms_stats_internal.gather_sql_stats('SELECT * FROM sys.tab$', USER, NULL, 'CASCADE', rept, ecode, emesg, 0);
END;
/
*
ORA-31011: XML parsing failed
ORA-19213: error occurred in XML processing at lines 1
LPX-00217: invalid character 25 (U+0019)
-- clearly more work needs to be done on this demo
dbms_stats_internal.gather_table_stats_model(
ownname IN VARCHAR2,
tabname IN VARCHAR2,
tobjn IN NUMBER,
op_id IN NUMBER,
model_control IN NUMBER,
from_database IN BOOLEAN);
dbms_stats_internal.generate_params_xml_int(
params IN OUT sys.dbms_stats_internal.varchartab,
params_xml IN OUT VARCHAR2,
owner IN VARCHAR2,
tabnameu IN VARCHAR2);
DBMS_STATS_INTERNAL.GET_ADVISOR_RULE_DESC(23)
------------------------------------------------------------
Avoid using analyze table commands for statistics collection
dbms_stats_internal.get_agg_cht_with_disk(
objn IN NUMBER,
clist IN OUT sys.ctab,
cind IN NUMBER,
cht IN sys.colhisttab,
nnv IN NUMBER,
mnb IN NUMBER,
chtmrg IN OUT sys.colhisttab);
dbms_stats_internal.get_agg_clist_with_disk(
objn IN NUMBER,
nrows IN NUMBER,
nrows_disk IN NUMBER,
clist IN sys.ctab)
RETURN sys.dbms_stats_internal.aggcoltab;
dbms_stats_internal.get_agg_colstats(
tab_num IN NUMBER,
total_rows IN NUMBER,
ndv_needed IN BOOLEAN,
dop IN NUMBER,
hybrid_global_ndv OUT BOOLEAN,
pdb_or_shard_count IN NUMBER)
RETURN dbms_stats_internal.aggcoltab;
dbms_stats_internal.get_autotask_max_runtime(max_rtime OUT NUMBER);
DECLARE
outVal NUMBER;
BEGIN
dbms_stats_internal.get_autotask_max_runtime(outVal);
dbms_output.put_line(TO_CHAR(outVal));
END;
/
*
ORA-01403: no data found
dbms_stats_internal.get_aux_vector_index_tables(
OWNER IN VARCHAR2,
INDNAME IN VARCHAR2,
AUXTAB1 OUT VARCHAR2,
AUXTAB2 OUT VARCHAR2,
AUXTAB3 OUT VARCHAR2,
IDX_TYPE OUT VARCHAR2);
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));
dbms_stats_internal.get_export_sql_clauses(
fin_sc IN NUMBER,
db_sc IN NUMBER,
sql_type IN VARCHAR2,
flags IN NUMBER,
ins_list IN OUT VARCHAR2,
sel_list IN OUT VARCHAR2,
sc_pred IN OUT VARCHAR2);
The DBA_SEGMENTS query returns 128, the DBA_INDEXES query 32. The difference between them is 96 which is the value returned by this function.
Tests on other indexes show a different pattern so it is difficult to quickly determine what the value returned by this function actually indicates.
dbms_stats_internal.get_index_block_count(
ownname IN VARCHAR2,
indname IN VARCHAR2,
partname IN VARCHAR2 DEFAULT NULL,
subpartname IN VARCHAR2 DEFAULT NULL,
ignore_offline IN VARCHAR2)
RETURN NUMBER;
conn sys@pdbdev as sysdba
SELECT SUM(blocks)
FROM dba_segments
WHERE owner = 'SH'
AND segment_name = 'SALES_PROD_BIX';
SELECT leaf_blocks
FROM dba_indexes
WHERE owner = 'SH'
AND index_name = 'SALES_PROD_BIX';
dbms_stats_internal.get_index_part_name(
tabowner IN VARCHAR2,
tabname IN VARCHAR2,
tabpartname IN VARCHAR2,
indowner IN VARCHAR2,
indname IN VARCHAR2)
RETURN VARCHAR2;
SELECT dbms_stats_internal.get_index_part_name('SYS', 'WRH$_LATCH_CHILDREN', 'WRH$_LATCH_CHILD_MXDB_MXSN', 'SYS', 'WRH$_LATCH_CHILDREN_PK') AS GIPN;
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);
dbms_stats_internal.get_mv_enque(
ownname IN VARCHAR2,
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;
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';
dbms_stats_internal.get_objnum(
ownname IN VARCHAR2,
objname IN VARCHAR2,
partname IN VARCHAR2 DEFAULT NULL,
subpname IN VARCHAR2 DEFAULT NULL,
objtype IN VARCHAR2,
stype OUT NUMBER);
RETURN NUMBER;
dbms_stats_internal.get_param_prop(
pname IN VARCHAR2,
exist OUT BOOLEAN,
isdefault OUT NUMBER);
DECLARE
l_exist BOOLEAN;
l_isdef NUMBER;
BEGIN
dbms_stats_internal.get_param_prop('METHOD_OPT', l_exist, l_isdef);
IF l_exist THEN
dbms_output.put_line('Exists');
ELSE
dbms_output.put_line('Does Not Exists');
END IF;
dbms_output.put_line(l_isdef);
END;
/ Exists
1
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;
Returns the most basic table stats for the specified table
dbms_stats_internal.get_table_stats_simple(
objnum IN NUMBER,
objtype IN NUMBER,
gttses IN BOOLEAN,
nrows OUT NUMBER,
nblks OUT NUMBER,
last_analyzed_d OUT DATE);
conn sys@pdbdev as sysdba
SELECT object_id
FROM dba_objects
WHERE object_name = 'AIRPLANES';
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;
BEGIN
IF dbms_stats_internal.incremental_internal_enabled THEN
dbms_output.put_line('Enabled');
ELSE
dbms_output.put_line('Not Enabled');
END IF;
END;
/ Enabled
dbms_stats_internal.index_max_keysize_ok(
owner IN VARCHAR2,
indname IN VARCHAR2)
RETURN BOOLEAN;
conn sys@pdbdev as sysdba
BEGIN
IF dbms_stats_internal.index_max_keysize_ok('UWCLASS', 'PK_AIRPLANE') THEN
dbms_output.put_line('T');
ELSE
dbms_output.put_line('F');
END IF;
END;
/ True
Returns TRUE if an index has global stats collected
dbms_stats_internal.indHasGlobalStats(bobjnum IN NUMBER) RETURN BOOLEAN;
conn sys@pdbdev as sysdba
SELECT object_id
FROM dba_objects
WHERE object_name = 'PK_SERVERS';
OBJECT_ID
----------
73006
BEGIN
IF dbms_stats_internal.indhasglobalstats(73006) THEN
dbms_output.put_line('Index Has Global Stats');
ELSE
dbms_output.put_line('Index Does Not Have Global Stats');
END IF;
END;
/ Index Has Global Stats
dbms_stats_internal.insert_impdp_stats(
objlist_tab_owner IN VARCHAR2,
objlist_tab_name IN VARCHAR2,
full IN BOOLEAN,
export_stats_since IN TIMESTAMP WITH TIME ZONE,
stat_tab_owner IN VARCHAR2,
stat_tab_name IN VARCHAR2,
fin_sc IN NUMBER,
db_sc IN NUMBER);
BEGIN
IF dbms_stats_internal.isShardedCatalog THEN
dbms_output.put_line('The Catalog Is Sharded');
ELSE
dbms_output.put_line('The Catalog Is NOT Sharded');
END IF;
END;
/ The Catalog Is NOT Sharded
Appears to return TRUE if a materialized view is busy, for example in the process of being refreshed: Otherwise returns FALSE
dbms_stats_internal.is_mv_table_busy(
own_name IN VARCHAR2,
obj_name IN VARCHAR2,
start_time IN DATE)
RETURN BOOLEAN;
conn sys@pdbdev as sysdba
CREATE MATERIALIZED VIEW uwclass.mv_test
USING INDEX
REFRESH COMPLETE
AS SELECT s.srvr_id, COUNT(*)
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id
GROUP BY s.srvr_id;
Materialized view created.
BEGIN
IF dbms_stats_internal.is_mv_table_busy('UWCLASS', 'MV_TEST', SYSDATE-1/1440) THEN
dbms_output.put_line('Busy');
ELSE
dbms_output.put_line('Not Busy');
END IF;
END;
/ Busy
dbms_stats_internal.is_partitioned_tab(
owner IN VARCHAR2,
tabname IN VARCHAR2)
RETURN BOOLEAN;
conn sys@pdbdev as sysdba
BEGIN
IF dbms_stats_internal.is_partitioned_tab('UWCLASS', 'AIRPLANES') THEN
dbms_output.put_line('Airplanes Is Partitioned');
ELSE
dbms_output.put_line('Airplanes Is Not Partitioned');
END IF;
IF dbms_stats_internal.is_partitioned_tab('SH', 'SALES') THEN
dbms_output.put_line('Sales Is Partitioned');
ELSE
dbms_output.put_line('Sales Is Not Partitioned');
END IF;
END;
/ Airplanes Is Not Partitioned
Sales Is Partitioned
PL/SQL procedure successfully completed.
Overload 2
dbms_stats_internal.is_partitioned_tab(tobjn IN NUMBER) RETURN BOOLEAN;
dbms_stats_internal.is_part_typ_same(
owner IN VARCHAR2,
tabname IN VARCHAR2,
part1 IN VARCHAR2,
part2 IN VARCHAR2)
RETURN BOOLEAN;
conn sys@pdbdev as sysdba
BEGIN
IF dbms_stats_internal.is_part_typ_same('SH', 'SALES', 'SALES_Q2_2001', 'SALES_Q2_2002') THEN
dbms_output.put_line('Same');
ELSE
dbms_output.put_line('Not The Same');
END IF;
END;
/
BEGIN
IF dbms_stats_internal.is_qa_mode THEN
dbms_output.put_line('In QA Mode');
ELSE
dbms_output.put_line('Not In QA Mode Which Is The Default');
END IF;
END;
/ Not In QA Mode Which Is The Default
Returns TRUE if resource management is enabled, else FALSE
dbms_stats_internal.is_res_man_on RETURN BOOLEAN;
BEGIN
IF dbms_stats_internal.is_res_man_on THEN
dbms_output.put_line('Resource Management Enabled');
ELSE
dbms_output.put_line('Resource Management Not Enabled');
END IF;
END;
/ Resource Management Not Enabled
Returns TRUE if the columns is identified as a "sensitive" column, else FALSE
dbms_stats_internal.is_sensitive_col(
tobjn IN NUMBER,
col_name IN VARCHAR2)
RETURN BOOLEAN;
SELECT object_id
FROM dba_objects
WHERE object_name = 'SERVERS';
OBJECT_ID
----------
84134
BEGIN
IF dbms_stats_internal.is_sensitive_col(84134, 'NETWORK_ID') THEN
dbms_output.put_line('This column contains sensitive data');
ELSE
dbms_output.put_line('This column does not contain sensitive data');
END IF;
END;
/ This column does not contain sensitive data
dbms_stats_internal.is_stale_strict(
tobjn IN NUMBER,
ownname IN VARCHAR2,
tabname IN VARCHAR2,
rows_changed IN NUMBER,
rowcnt IN NUMBER,
dmlflags IN NUMBER,
fobjn IN NUMBER,
check_cu IN VARCHAR2)
RETURN NUMBER;
dbms_stats_internal.is_table_empty(
ownu IN VARCHAR2,
tabu IN VARCHAR2)
RETURN BOOLEAN;
BEGIN
IF dbms_stats_internal.is_table_empty('SYS', 'OBJ$') THEN
dbms_output.put_line('Is Empty');
ELSE
dbms_output.put_line('Not Empty');
END IF;
END;
/ Not Empty
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;
/
dbms_stats_internal.is_urgent_error(shutdown OUT BOOLEAN) RETURN BOOLEAN;
DECLARE
outVal BOOLEAN;
BEGIN
IF dbms_stats_internal.is_urgent_error(outVal) THEN
dbms_output.put_line('Is');
ELSE
dbms_output.put_line('Is Not');
END IF;
IF outVal THEN
dbms_output.put_line('Outval Is True');
ELSE
dbms_output.put_line('Outval Is False');
END IF;
END;
/ Is Not
Outval Is False
DECLARE
jc VARCHAR2(30) := 'DEFAULT_JOB_CLASS';
BEGIN
IF dbms_stats_internal.job_class_exists(jc) THEN
dbms_output.put_line('Job class ' || jc || ' exists');
ELSE
dbms_output.put_line('Job class ' || jc || ' does not exist');
END IF;
END;
/ Job class DEFAULT_JOB_CLASS exists
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;
/
dbms_stats_internal.open_advisor_action_obj_cur(
task_id IN NUMBER,
exec_name IN VARCHAR2,
action_all IN VARCHAR2,
valid_rules IN dbmsstatnumtab,
valid_findings IN dbmsstatnumtab,
adv_cur IN OUT REF CURSOR);
dbms_stats_internal.open_advisor_finding_obj_cur(
task_id IN NUMBER,
exec_name IN VARCHAR2,
rule_id IN NUMBER,
finding_id IN NUMBER,
adv_cur IN OUT REF CURSOR);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
dbms_stats_internal.open_index_cur(
tabowner IN VARCHAR2,
tabname IN VARCHAR2,
ind_pat IN VARCHAR2,
p_status IN VARCHAR,
no_stats_index_only IN NUMBER,
p_uniqueness IN VARCHAR2,
inducr IN OUT REF CURSOR);
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);
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);
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);
dbms_stats_internal.parse_incremental_staleness(
ncre_staleness IN VARCHAR2,
use_stale_percent OUT BOOLEAN,
use_locked_stats OUT BOOLEAN,
allow_mixed_format OUT BOOLEAN);
dbms_stats_internal.parse_stat_category(
stat_category IN VARCHAR2,
deletest IN BOOLEAN,
object_stats OUT BOOLEAN,
synopses OUT BOOLEAN,
realtime_stats OUT BOOLEAN,
models OUT BOOLEAN);
Returns the partition and subpartition types associated with a table
dbms_stats_internal.part_types(
owner IN VARCHAR2,
objname IN VARCHAR2,
namespace_p IN BINARY_INTEGER,
ptype OUT BINARY_INTEGER,
sptype OUT BINARY_INTEGER);
conn sys@pdbdev as sysdba
SELECT namespace
FROM dba_objects
WHERE owner = 'UWCLASS'
AND object_name = 'SERVERS';
dbms_stats_internal.prep_save_cs(sav_time IN TIMESTAMP WITH TIME ZONE) RETURN BOOLEAN;
BEGIN
IF dbms_stats_internal.prep_save_cs(TO_TIMESTAMP_TZ('2024-05-02 11:00:00 -7:00', 'YYYY-MM-DD HH:MI:SS TZH:TZM')) THEN
dbms_output.put_line('TRUE');
ELSE
dbms_output.put_line('FALSE');
END IF;
END;
/ FALSE
dbms_stats_internal.prorate_global_ndv(
intcol IN NUMBER,
hll_ndv IN NUMBER,
hll_cardinality IN NUMBER,
hll_partcnt IN NUMBER,
hll_min IN NUMBER,
hll_max IN NUMBER,
hll_nullcnt IN NUMBER,
as_ndv IN NUMBER,
as_cardinality IN NUMBER,
as_partcnt IN NUMBER,
as_min IN NUMBER,
as_max IN NUMBER,
as_nullcnt IN NUMBER)
RETURN NUMBER;
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);
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;
dbms_stats_internal.reporting_man_log_op(
id IN NUMBER,
operation IN VARCHAR2,
target IN VARCHAR2,
start_time IN TIMESTAMP WITH TIME ZONE,
end_time IN TIMESTAMP WITH TIME ZONE,
status IN NUMBER,
job_name IN VARCHAR2,
session_id IN NUMBER,
notes IN VARCHAR2);
dbms_stats_internal.reporting_man_log_task(
ctx IN OUT sys.dbms_stats_internal.reportingContext,
target IN VARCHAR2,
statusS IN NUMBER,
auto_stats_session IN BOOLEAN);
dbms_stats_internal.reporting_man_log_task_callout(
op_id IN NUMBER,
job_name IN VARCHAR2,
status IN NUMBER,
start_time IN TIMESTAMP WITH TIME ZONE,
end_time IN TIMESTAMP WITH TIME ZONE,
target IN VARCHAR2,
target_objn IN NUMBER,
target_type IN NUMBER,
target_size IN NUMBER,
cost IN NUMBER,
batch_coeff IN NUMBER,
actions IN NUMBER,
priority IN NUMBER,
notes IN VARCHAR2,
flags IN NUMBER);
dbms_stats_internal.reporting_man_update_task(
ctx IN OUT sys.dbms_stats_internal.reportingContext,
statusS IN NUMBER,
auto_stats_session IN BOOLEAN,
commitfree IN BOOLEAN);
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;
/
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
dbms_stats_internal.set_dyn_stats_pref_needed(
OWNNAME IN VARCHAR2,
PACKAGE_NAME IN VARCHAR2,
FUNCTION_NAME IN VARCHAR2,
PVALUE IN VARCHAR2)
RETURN BOOLEAN;
dbms_stats_internal.set_shard_ddl(orig_shard_ddl OUT BOOLEAN);
DECLARE
outVal BOOLEAN;
BEGIN
dbms_stats_internal.set_shard_ddl(outVal);
IF outVal THEN
dbms_output.put_line('T');
ELSE
dbms_output.put_line('F');
END IF;
END;
/
PLS-00904: insufficient privilege to access object SET_SHARD_DDL
BEGIN
IF dbms_stats_internal.similar_object_exists( USER, 'TAB$', 1) THEN
dbms_output.put_line('TRUE');
ELSE
dbms_output.put_line('FALSE');
END IF;
END;
/ FALSE
dbms_stats_internal.similar_schema_exists(uname IN VARCHAR2) RETURN BOOLEAN;
BEGIN
IF dbms_stats_internal.similar_schema_exists('SYSBACKUP') THEN
dbms_output.put_line('TRUE');
ELSE
dbms_output.put_line('FALSE');
END IF;
END;
/ TRUE
dbms_stats_internal.skip_cur_object(
ownname IN VARCHAR2,
objname IN VARCHAR2,
subname IN VARCHAR2,
start_time IN DATE,
max_run_time IN NUMBER,
reportingctx IN OUT sys.dbms_stats_internal.reportingContext)
RETURN BOOLEAN;
SELECT pname, pval1
FROM aux_stats$
WHERE sname = 'SYSSTATS_MAIN';
-- either collect real system stats or use this technique to restore the original values as shown
DECLARE
retVal NUMBER;
BEGIN
retVal := dbms_stats_internal.store_system_stats(10, 4096, 2708.62471);
dbms_output.put_line(TO_CHAR(retVal));
END;
/
SELECT status
FROM dba_tablespaces
WHERE tablespace_name = 'SYSAUX';
STATUS
---------
ONLINE
BEGIN
IF dbms_stats_internal.sysaux_offline THEN
dbms_output.put_line('The SYSAUX Tablespace is Off-line');
ELSE
dbms_output.put_line('The SYSAUX Tablespace is On-line');
END IF;
END;
/ The SYSAUX Tablespace is On-line
Returns TRUE if global stats have been collected on a partitioned table
dbms_stats_internal.tabHasGlobalStats(
owner IN VARCHAR2,
tabname IN VARCHAR2)
RETURN BOOLEAN;
conn sys@pdbdev as sysdba
BEGIN
IF dbms_stats_internal.tabHasGlobalStats('SH', 'SALES') THEN
dbms_output.put_line('Global Stats Collected');
ELSE
dbms_output.put_line('Global Stats Not Collected');
END IF;
END;
/
Returns TRUE if partitions stats have been collected on a partitioned table
dbms_stats_internal.tabPartsHaveStats(
owner IN VARCHAR2,
tabname IN VARCHAR2,
pname IN VARCHAR2)
RETURN BOOLEAN;
conn sys@pdbdev as sysdba
SELECT partition_name
FROM dba_tab_partitions
WHERE table_owner = 'SH'
AND table_name = 'SALES'
ORDER BY 1;
BEGIN
IF dbms_stats_internal.tabPartsHaveStats('SH', 'SALES', 'SALES_Q2_2000') THEN
dbms_output.put_line('Partition Stats Collected');
ELSE
dbms_output.put_line('Partition Stats Not Collected');
END IF;
END;
/
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;
dbms_stats_internal.test_gather_sql_stats(
sql_text IN CLOB,
user_name IN VARCHAR2,
bind_list IN sys.sql_binds,
options IN VARCHAR2,
exec_userid IN NUMBER,
RETURN sys.xmltype;
Returns TRUE if top frequency histograms are enabled on the table
dbms_stats_internal.top_freq_hist_enabled(
owner IN VARCHAR2,
tab IN VARCHAR2,
incremental IN BOOLEAN)
RETURN BOOLEAN;
BEGIN
IF dbms_stats_internal.top_freq_hist_enabled('SYS', 'OBJ$', TRUE) THEN
dbms_output.put_line('Enabled');
ELSE
dbms_output.put_line('Disabled');
END IF;
END;
/ Enabled
Upper cases a string in the form a of a stored procedure rather than a function but also contains input sanitization (will not accept punctuation or spaces)
dbms_stats_internal.upcase(
inname IN VARCHAR2,
outname OUT VARCHAR2);
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);
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);
dbms_stats_internal.update_timedout_stats_target(
objnum IN NUMBER,
endtime_current IN TIMESTAMP WITH TIME ZONE,
sesid IN BINARY_INTEGER,
sesser IN BINARY_INTEGER);
dbms_stats_internal.view_column_exists(
owner IN VARCHAR2,
viewname IN VARCHAR2,
viewcolname IN VARCHAR2)
RETURN BOOLEAN;
BEGIN
IF dbms_stats_internal.view_column_exists(USER, 'USER_ALL_TABLES', 'OWNER') THEN
dbms_output.put_line('Owner Column Exists');
ELSE
dbms_output.put_line('Owner Column Does Not Exists');
END IF;
END;
/ Owner Column Does Not Exists
Creates a physical
file and dumps the contents of a CLOB to the file
dbms_stats_internal.write_clob_to_file(
dir_name IN VARCHAR2,
file_name IN VARCHAR2,
content IN CLOB);
SELECT owner, directory_name, directory_path
FROM dba_directories
ORDER BY 1,2;
DECLARE
cCLOB CLOB := 'Exfiltrating all of your sensitive data';
BEGIN
dbms_stats_internal.write_clob_to_file('WFCJADMIN', 'dumpfile.txt', cCLOB);
END;
/
-- go to the operating system directory WFCJADMIN and open dumpfile.txt
