PL/SQL Packages and Types Reference 10g Release 1 (10.1) Part Number B1080201 


View PDF 
With the DBMS_STATS
package you can view and modify optimizer statistics gathered for database objects.
This chapter contains the following topics:
The statistics to be viewed or modified can reside in the dictionary or in a table created in the user's schema for this purpose. You can also collect and manage userdefined statistics for tables and domain indexes using this package. For example, if the DELETE_COLUMN_STATS
procedure is invoked on a column for which an association is defined, userdefined statistics for that column are deleted in addition to deletion of the standard statistics.
Only statistics stored in the dictionary have an impact on the costbased optimizer. You can also use DBMS_STATS
to gather statistics in parallel
Types for the minimum and maximum values and histogram endpoints include:
TYPE numarray IS VARRAY(256) OF NUMBER; TYPE datearray IS VARRAY(256) OF DATE; TYPE chararray IS VARRAY(256) OF VARCHAR2(4000); TYPE rawarray IS VARRAY(256) OF RAW(2000); TYPE fltarray IS VARRAY(256) OF BINARY_FLOAT; TYPE dblarray IS VARRAY(256) OF BINARY_DOUBLE; TYPE StatRec IS RECORD ( epc NUMBER, minval RAW(2000), maxval RAW(2000), bkvals NUMARRAY, novals NUMARRAY);
Types for listing stale tables include:
TYPE ObjectElem IS RECORD ( ownname VARCHAR2(30),  owner objtype VARCHAR2(6),  'TABLE' or 'INDEX' objname VARCHAR2(30),  table/index partname VARCHAR2(30),  partition subpartname VARCHAR2(30),  subpartition confidence NUMBER);  not used type ObjectTab is TABLE of ObjectElem;
Use the following constant to indicate that autosample size algorithms should be used:
AUTO_SAMPLE_SIZE CONSTANT NUMBER;
The constant used to determine the system default degree of parallelism, based on the initialization parameters, is:
DEFAULT_DEGREE CONSTANT NUMBER;
Use the following constant to let Oracle select the degree of parallelism based on size of the object, number of CPUs and initialization parameters:
AUTO_DEGREE CONSTANT NUMBER;
Use the following constant to let Oracle decide whether to collect statistics for indexes or not:
AUTO_CASCADE CONSTANT BOOLEAN;
Use the following constant to let oracle decide when to invalidate dependent cursors.
AUTO_INVALIDATE CONSTANT BOOLEAN
The DBMS_STATS
subprograms perform the following general operations:
Most of the DBMS_STATS
procedures include the three parameters statown
, stattab
, and statid
. These parameters allow you to store statistics in your own tables (outside of the dictionary), which does not affect the optimizer. Therefore, you can maintain and experiment with sets of statistics.
The stattab
parameter specifies the name of a table in which to hold statistics, and it is assumed that it resides in the same schema as the object for which statistics are collected (unless the statown
parameter is specified). You can create multiple tables with different stattab
identifiers to hold separate sets of statistics.
Additionally, you can maintain different sets of statistics within a single stattab
by using the statid
parameter, which avoids cluttering the user's schema.
For the SET
and GET
procedures, if stattab
is not provided (that is, NULL
), then the operation works directly on the dictionary statistics; therefore, you do not need to create these statistics tables if they only plan to modify the dictionary directly. However, if stattab
is not NULL
, then the SET
or GET
operation works on the specified user statistics table, and not the dictionary.
You can change the default values of some of the parameters of DBMS_STATS
procedures using the SET_PARAM Procedure.
Most of the procedures in this package commit the current transaction, perform the operation, and then commit again.
When a DBMS_STATS
subprogram modifies or deletes the statistics for an object, all the dependent cursors are invalidated by default and corresponding statements are subject to recompilation next time so that the new statistics have immediate effects. This behavior can be altered with the no_invalidate
argument.
Use the following procedures to store and retrieve individual columnrelated, indexrelated, and tablerelated statistics:
PREPARE_COLUMN_VALUES SET_COLUMN_STATS SET_INDEX_STATS SET_SYSTEM_STATS SET_TABLE_STATS
In the special versions of the SET_*_STATS
procedures for setting userdefined statistics, the following, if provided, are stored in the dictionary or external statistics table:
extstats
)statsschema
)statsname
)The userdefined statistics and the corresponding statistics type are inserted into the USTATS$
dictionary table. You can specify userdefined statistics without specifying the statistics type name.
CONVERT_RAW_VALUE GET_COLUMN_STATS GET_INDEX_STATS GET_SYSTEM_STATS GET_TABLE_STATS
The special versions of the GET_*_STATS
procedures return userdefined statistics and the statistics type owner and name as OUT
arguments corresponding to the schema object specified. If userdefined statistics are not collected, NULL
values are returned.
The DELETE_*
procedures delete both userdefined statistics and the standard statistics for the given schema object.
DELETE_COLUMN_STATS DELETE_DATABASE_STATS DELETE_DICTIONARY_STATS DELETE_FIXED_OBJECTS_STATS DELETE_INDEX_STATS DELETE_SCHEMA_STATS DELETE_SYSTEM_STATS DELETE_TABLE_STATS
Use the following procedures to transfer statistics from the dictionary to a user statistics table (export_
*) and from a user statistics table to the dictionary (import_
*):
CREATE_STAT_TABLE DROP_STAT_TABLE
CREATE_STAT_TABLE
can hold userdefined statistics and the statistics type object number.
EXPORT_COLUMN_STATS EXPORT_DATABASE_STATS EXPORT_DICTIONARY_STATS EXPORT_FIXED_OBJECTS_STATS EXPORT_INDEX_STATS EXPORT_SCHEMA_STATS EXPORT_SYSTEM_STATS EXPORT_TABLE_STATS IMPORT_COLUMN_STATS IMPORT_DICTIONARY_STATS IMPORT_FIXED_OBJECTS_STATS IMPORT_INDEX_STATS IMPORT_SCHEMA_STATS IMPORT_SYSTEM_STATS IMPORT_TABLE_STATS
The IMPORT_*
procedures retrieve statistics, including userdefined statistics, from the stattab
table and store them in the dictionary. Because the SET_*_STATS
and GET_*_STATS
interfaces are supported for userdefined statistics, userdefined statistics can be copied to another database using this interface.
Use the following procedures to gather certain classes of optimizer statistics, with possible performance improvements over the ANALYZE
command:
GATHER_DATABASE_STATS GATHER_DICTIONARY_STATS GATHER_FIXED_OBJECTS_STATS GATHER_INDEX_STATS GATHER_SCHEMA_STATS GATHER_SYSTEM_STATS GATHER_TABLE_STATS
The GATHER_*
procedures also collect userdefined statistics for columns and domain indexes.
The statown
, stattab
, and statid
parameters instruct the package to back up current statistics in the specified table before gathering new statistics.
Oracle also provides the following procedure for generating statistics for derived objects when you have sufficient statistics on related objects:
GENERATE_STATS
Use the following procedures to lock and unlock statistics on objects.
LOCK_TABLE_STATS LOCK_SCHEMA_STATS UNLOCK_TABLE_STATS UNLOCK_SCHEMA_STATS
The LOCK*
procedures either freeze the current set of the statistics or to keep the statistics empty (uncollected).When statistics on a table are locked, all the statistics depending on the table, including table statistics, column statistics, histograms and statistics on all dependent indexes, are considered to be locked.
Use the following procedures to restore statistics as of a specified timestamp. This is useful in case newly collected statistics leads to some suboptimal execution plans and the administrator wants to revert to the previous set of statistics.
RESTORE_TABLE_STATS RESTORE_SCHEMA_STATS RESTORE_DATABASE_STATS RESTORE_FIXED_OBJECTS_STATS RESTORE_DICTIONARY_STATS RESTORE_SYSTEM_STATS
Whenever statistics in dictionary are modified, old versions of statistics are saved automatically for future restoring. The old statistics are purged automatically at regular intervals based on the statistics history retention setting and the time of the recent analysis of the system. Retention is configurable using the ALTER_STATS_HISTORY_RETENTION procedure.
The other DBMS_STATS procedures related to restoring statistics are:
PURGE_STATS
: This procedure lets you manually purge old versions beyond a time stamp.DBMS_STATS
supports operations on userdefined statistics. When a domain index or column is associated with a statistics type (using the associate
statement), operations on the index or column manipulate userdefined statistics. For example, gathering statistics for a domain index (for which an association with a statistics type exists) using the GATHER_INDEX_STATS
interface invokes the userdefined statistics collection method of the associated statistics type. Similarly, delete, transfer, import, and export operations manipulate userdefined statistics.
SET
and GET
operations for userdefined statistics are also supported using a special version of the SET
and GET
interfaces for columns and indexes.
The following subprograms are obsolete with Release 10g:
In earlier releases, you could use these subprograms to operate on statistics. These subprograms are now nonoperational because Oracle performs their functions automatically.
Assume many modifications have been made to the employees
table since the last time statistics were gathered. To ensure that the costbased optimizer is still picking the best plan, statistics should be gathered once again; however, the user is concerned that new statistics will cause the optimizer to choose bad plans when the current ones are acceptable. The user can do the following:
BEGIN DBMS_STATS.CREATE_STAT_TABLE ('hr', 'savestats'); DBMS_STATS.GATHER_TABLE_STATS ('hr', 'employees', stattab => 'savestats'); END;
This operation gathers new statistics on the employees
table, but first saves the original statistics in a user statistics table: hr.savestats
.
If the user believes that the new statistics are causing the optimizer to generate poor plans, then the original statistics can be restored as follows:
BEGIN DBMS_STATS.DELETE_TABLE_STATS ('hr', 'employees'); DBMS_STATS.IMPORT_TABLE_STATS ('hr', 'employees', stattab => 'savestats'); END;
Assume that you want to perform database application processing OLTP transactions during the day and run reports at night.
To collect daytime system statistics, gather statistics for 720 minutes. Store the statistics in the MYSTATS
table.
BEGIN DBMS_STATS.GATHER_SYSTEM_STATS ( interval => 720, stattab => 'mystats', statid => 'OLTP'); END;
To collect nighttime system statistics, gather statistics for 720 minutes. Store the statistics in the MYSTATS
table.
BEGIN DBMS_STATS.GATHER_SYSTEM_STATS ( interval => 720, stattab => 'mystats', statid => 'OLAP'); END;
Update the dictionary with the gathered statistics.
VARIABLE jobno number; BEGIN DBMS_JOB.SUBMIT (:jobno, 'DBMS_STATS.IMPORT_SYSTEM_STATS (''mystats'',''OLTP'');' sysdate, 'sysdate + 1'); COMMIT; END; BEGIN DBMS_JOB.SUBMIT (:jobno, 'DBMS_STATS.IMPORT_SYSTEM_STATS (''mystats'',''OLAP'');' sysdate + 0.5, 'sysdate + 1'); COMMIT; END;
Subprogram  Description 

Enables or disables the DML monitoring feature of all tables in the database, except for snapshot logs and the tables, which monitoring does not support [See Deprecated Subprograms]  
Enables or disables the DML monitoring feature of all tables in the schema, except for snapshot logs and the tables, which monitoring does not support [See Deprecated Subprograms]  
Changes the statistics history retention value  
Convert the internal representation of a minimum or maximum value into a datatypespecific value  
Convert the internal representation of a minimum or maximum value into a datatypespecific value  
Convert the internal representation of a minimum or maximum value into a datatypespecific value  
Creates a table with name  
Deletes columnrelated statistics  
Deletes statistics for the entire database  
Deletes statistics for all dictionary schemas ('  
Deletes statistics of all fixed tables  
Deletes indexrelated statistics  
Deletes schemarelated statistics  
Deletes system statistics  
Deletes tablerelated statistics  
Drops a user statistics table created by  
Retrieves statistics for a particular column and stores them in the user statistics table identified by  
Retrieves statistics for all objects in the database and stores them in the user statistics table identified by  
Retrieves statistics for all dictionary schemas ('  
Retrieves statistics for fixed tables and stores them in the user statistics table identified by  
Retrieves statistics for a particular index and stores them in the user statistics table identified by  
Retrieves statistics for all objects in the schema identified by ownname and stores them in the user statistics table identified by  
Retrieves system statistics and stores them in the user statistics table  
Retrieves statistics for a particular table and stores them in the user statistics table  
Flushes inmemory monitoring information for all the tables to the dictionary  
Gathers statistics for all objects in the database  
Gathers statistics for dictionary schemas '  
Gathers statistics of fixed objects  
Gathers index statistics  
Gathers statistics for all objects in a schema  
Gathers system statistics  
Gathers table and column (and index) statistics  
Generates object statistics from previously collected statistics of related objects  
Gets all columnrelated information  
Gets all indexrelated information  
Gets the default value of parameters of  
Gets the oldest timestamp where statistics history is available  
Returns the current retention value  
Gets system statistics from stattab, or from the dictionary if stattab is  
Gets all tablerelated information  
Retrieves statistics for a particular column from the user statistics table identified by  
Retrieves statistics for all objects in the database from the user statistics table and stores them in the dictionary  
Retrieves statistics for all dictionary schemas ('  
Retrieves statistics for fixed tables from the user statistics table identified by  
Retrieves statistics for a particular index from the user statistics table identified by  
Retrieves statistics for all objects in the schema identified by  
Retrieves system statistics from the user statistics table and stores them in the dictionary  
Retrieves statistics for a particular table from the user statistics table identified by  
Locks the statistics of all tables of a schema  
Locks the statistics on the table  
Converts userspecified minimum, maximum, and histogram endpoint datatypespecific values into Oracle's internal representation for future storage using the  
Converts userspecified minimum, maximum, and histogram endpoint datatypespecific values into Oracle's internal representation for future storage using the  
Converts userspecified minimum, maximum, and histogram endpoint datatypespecific values into Oracle's internal representation for future storage using the  
Purges old versions of statistics saved in the dictionary  
Restores statistics of all tables of the database as of a specified timestamp  
Restores statistics of all dictionary tables (tables of '  
Restores statistics of all fixed tables as of a specified timestamp  
Restores statistics of all tables of a schema as of a specified timestamp  
Restores statistics of all tables of a schema as of a specified timestamp  
Restores statistics of a table as of a specified timestamp (as_of_timestamp), as well as statistics of associated indexes and columns  
Sets columnrelated information  
Sets indexrelated information  
Sets default values for parameters of DBMS_STATS procedures  
Sets system statistics  
Sets tablerelated information  
Unlocks the statistics on all the table in a schema  
Unlocks the statistics on the table  
Upgrades user statistics on an older table 
This procedure enables or disables the DML monitoring feature of all the tables in the schema, except for snapshot logs and the tables, which monitoring does not support. Using this procedure is equivalent to issuing ALTER TABLE...MONITORING
(or NOMONITORING
) individually.
DBMS_STATS.ALTER_DATABASE_TAB_MONITORING ( monitoring BOOLEAN DEFAULT TRUE, sysobjs BOOLEAN DEFAULT FALSE);
Parameter  Description 


Enables monitoring if true, and disables monitoring if false. 

If true, changes monitoring on the dictionary objects. 
ORA20000
: Insufficient privileges.
This procedure enables or disables the DML monitoring feature of all the tables in the schema, except for snapshot logs and the tables, which monitoring does not support. Using this procedure is equivalent to issuing ALTER
TABLE...MONITORING
(or NOMONITORING
) individually.
DBMS_STATS.ALTER_SCHEMA_TAB_MONITORING ( ownname VARCHAR2 DEFAULT NULL, monitoring BOOLEAN DEFAULT TRUE);
Parameter  Description 


The name of the schema. ( 

Enables monitoring if true, and disables monitoring if false. 
You should enable monitoring if you use GATHER_DATABASE_STATS
or GATHER_SCHEMA_STATS
with the GATHER AUTO
or GATHER
STALE
options.
ORA20000
: Insufficient privileges.
This procedure changes the statistics history retention value. Statistics history retention is used by both the automatic purge and PURGE_STATS Procedure
.
DBMS_STATS.ALTER_STATS_HISTORY_RETENTION ( retention IN NUMBER);
To run this procedure, you must have the SYSDBA
or both ANALYZE
ANY
DICTIONARY
and ANALYZE
ANY
system privilege.
ORA20000
: Insufficient privileges.
This procedure converts the internal representation of a minimum or maximum value into a datatypespecific value. The minval and maxval fields of the StatRec
structure as filled in by GET_COLUMN_STATS
or PREPARE_COLUMN_VALUES
are appropriate values for input.
DBMS_STATS.CONVERT_RAW_VALUE ( rawval RAW, resval OUT BINARY_FLOAT); DBMS_STATS.CONVERT_RAW_VALUE ( rawval RAW, resval OUT BINARY_DOUBLE); DBMS_STATS.CONVERT_RAW_VALUE ( rawval RAW, resval OUT DATE); DBMS_STATS.CONVERT_RAW_VALUE ( rawval RAW, resval OUT NUMBER); DBMS_STATS.CONVERT_RAW_VALUE ( rawval RAW, resval OUT VARCHAR2);
pragma restrict_references(convert_raw_value, WNDS, RNDS, WNPS, RNPS);
Parameter  Description 


The raw representation of a column minimum or maximum datatypespecific output parameters. 

The converted, typespecific value. 
This procedure converts the internal representation of a minimum or maximum value into a datatypespecific value. The minval and maxval fields of the StatRec
structure as filled in by GET_COLUMN_STATS
or PREPARE_COLUMN_VALUES
are appropriate values for input.
DBMS_STATS.CONVERT_RAW_VALUE_NVARCHAR ( rawval RAW, resval OUT NVARCHAR2);
pragma restrict_references(convert_raw_value_nvarchar, WNDS, RNDS, WNPS, RNPS);
Parameter  Description 


The raw representation of a column minimum or maximum datatypespecific output parameters. 

The converted, typespecific value. 
This procedure converts the internal representation of a minimum or maximum value into a datatypespecific value. The minval and maxval fields of the StatRec
structure as filled in by GET_COLUMN_STATS
or PREPARE_COLUMN_VALUES
are appropriate values for input.
DBMS_STATS.CONVERT_RAW_VALUE_ROWID ( rawval RAW, resval OUT ROWID);
pragma restrict_references(convert_raw_value_rowid, WNDS, RNDS, WNPS, RNPS);
Parameter  Description 


The raw representation of a column minimum or maximum datatypespecific output parameters. 

The converted, typespecific value. 
This procedure creates a table with name stattab
in ownname
's schema which is capable of holding statistics. The columns and types that compose this table are not relevant as it should be accessed solely through the procedures in this package.
DBMS_STATS.CREATE_STAT_TABLE ( ownname VARCHAR2, stattab VARCHAR2, tblspace VARCHAR2 DEFAULT NULL);
ORA20000
: Table already exists or insufficient privileges.
ORA20001
: Tablespace does not exist.
This procedure deletes columnrelated statistics.
DBMS_STATS.DELETE_COLUMN_STATS ( ownname VARCHAR2, tabname VARCHAR2, colname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, cascade_parts BOOLEAN DEFAULT TRUE, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE);
Parameter  Description 


Name of the schema. 

Name of the table to which this column belongs. 

Name of the column. 

Name of the table partition for which to delete the statistics. If the table is partitioned and if 

User statistics table identifier describing from where to delete the statistics. If 

Identifier (optional) to associate with these statistics within 

If the table is partitioned and if 

Schema containing 

If set to the default, does not invalidate the shared cursors dependent on the table. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Use 

When value of this argument is 
ORA20000
: Object does not exist or insufficient privileges.
ORA20005
: Object statistics are locked.
This procedure deletes statistics for all the tables in a database.
DBMS_STATS.DELETE_DATABASE_STATS ( stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE);
Parameter  Description 


User statistics table identifier describing from where to delete the statistics. If 

Identifier (optional) to associate with these statistics within 

Schema containing 

If set to the default, does not invalidate the shared cursors dependent on the table. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Use 

When the value of this argument is 
ORA20000
: Object does not exist or insufficient privileges.
This procedure deletes statistics for all dictionary schemas ('SYS
', 'SYSTEM
' and RDBMS component schemas).
DBMS_STATS.DELETE_DICTIONARY_STATS ( stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE);
Parameter  Description 


User statistics table identifier describing from where to delete the statistics. If 

Identifier (optional) to associate with these statistics within 

Schema containing 

If set to the default, does not invalidate the shared cursors dependent on the table. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.)Use 

When the value of this argument is 
You must have the SYSDBA
or both ANALYZE
ANY
DICTIONARY
and ANALYZE
ANY
system privilege to execute this procedure.
ORA20000
: Object does not exist or insufficient privileges.
ORA20002
: Bad user statistics table, may need to upgrade it.
This procedure deletes statistics of all fixed tables.
DBMS_STATS.DELETE_FIXED_OBJECTS_STATS ( stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE);
Parameter  Description 


The user statistics table identifier describing from where to delete the current statistics. If 

The (optional) identifier to associate with these statistics within stattab. This only applies if 

The schema containing 

Does not invalidate the dependent cursors if set to 

Ignores the statistics lock on objects and deletes the statistics if set to 
You must have the SYSDBA
or ANALYZE
ANY
DICTIONARY
system privilege to execute this procedure.
ORA20000
: Insufficient privileges.
ORA20002
: Bad user statistics table, may need to upgrade it.
This procedure deletes indexrelated statistics.
DBMS_STATS.DELETE_INDEX_STATS ( ownname VARCHAR2, indname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, cascade_parts BOOLEAN DEFAULT TRUE, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE);
Parameter  Description 


Name of the schema. 

Name of the index. 

Name of the index partition for which to delete the statistics. If the index is partitioned and if 

User statistics table identifier describing from where to delete the statistics. If 

Identifier (optional) to associate with these statistics within 

If the index is partitioned and if 

Schema containing 

If set to the default, does not invalidate the shared cursors dependent on the table. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Use 

When value of this argument is 
ORA20000
: Object does not exist or insufficient privileges.
ORA20005
: Object statistics are locked.
This procedure deletes statistics for an entire schema.
DBMS_STATS.DELETE_SCHEMA_STATS ( ownname VARCHAR2, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE);
Parameter  Description 


Name of the schema. 

User statistics table identifier describing from where to delete the statistics. If 

Identifier (optional) to associate with these statistics within 

Schema containing 

If set to the default, does not invalidate the shared cursors dependent on the table. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Use 

When value of this argument is 
ORA20000
: Object does not exist or insufficient privileges
This procedure deletes workload statistics (collected using the 'INTERVAL
' or 'START
' and 'STOP
' options) and resets the default to noworkload
statistics (collected using 'NOWORKLOAD
' option) if stattab
is not specified. If stattab
is specified, the subprogram deletes all system statistics with the associated statid
from the stattab
.
DBMS_STATS.DELETE_SYSTEM_STATS ( stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
ORA20000
: Object does not exist or insufficient privileges.
ORA20002
: Bad user statistics table; may need to be upgraded.
This procedure deletes tablerelated statistics.
DBMS_STATS.DELETE_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, cascade_parts BOOLEAN DEFAULT TRUE, cascade_columns BOOLEAN DEFAULT TRUE, cascade_indexes BOOLEAN DEFAULT TRUE, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE);
Parameter  Description 


Name of the schema. 

Name of the table to which this column belongs. 

Name of the table partition from which to get the statistics. If the table is partitioned and if 

User statistics table identifier describing from where to retrieve the statistics. If 

Identifier (optional) to associate with these statistics within 

If the table is partitioned and if 

Indicates that 

Indicates that 

Schema containing 

If set to the default, does not invalidate the shared cursors dependent on the table. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Use 

When value of this argument is 
ORA20000
: Object does not exist or insufficient privileges.
ORA20005
: Object statistics are locked.
This procedure drops a user statistics table.
DBMS_STATS.DROP_STAT_TABLE ( ownname VARCHAR2, stattab VARCHAR2);
Parameter  Description 


Name of the schema. 

User statistics table identifier. 
ORA20000
: Table does not exists or insufficient privileges.
This procedure retrieves statistics for a particular column and stores them in the user statistics table identified by stattab
.
DBMS_STATS.EXPORT_COLUMN_STATS ( ownname VARCHAR2, tabname VARCHAR2, colname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
ORA20000
: Object does not exist or insufficient privileges.
This procedure retrieves statistics for all objects in the database and stores them in the user statistics tables identified by statown
.stattab
.
DBMS_STATS.EXPORT_DATABASE_STATS ( stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
ORA20000
: Object does not exist or insufficient privileges.
This procedure retrieves statistics for all dictionary schemas ('SYS
', 'SYSTEM
' and RDBMS component schemas) and stores them in the user statistics table identified by stattab
.
DBMS_STATS.EXPORT_DICTIONARY_STATS ( stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
You must have the SYSDBA
or ANALYZE
ANY
DICTIONARY
and ANALYZE
ANY
system privilege to execute this procedure.
ORA20000
: Object does not exist or insufficient privileges.
ORA20002
: Bad user statistics table, may need to upgrade it.
This procedure retrieves statistics for fixed tables and stores them in the user statistics table identified by stattab
.
DBMS_STATS.EXPORT_FIXED_OBJECTS_STATS ( stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
ORA20000
: Object does not exist or insufficient privileges.
ORA20002
: Bad user statistics table, may need to upgrade it.
This procedure retrieves statistics for a particular index and stores them in the user statistics table identified by stattab
.
DBMS_STATS.EXPORT_INDEX_STATS ( ownname VARCHAR2, indname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
ORA20000
: Object does not exist or insufficient privileges.
This procedure retrieves statistics for all objects in the schema identified by ownname
and stores them in the user statistics tables identified by stattab
.
DBMS_STATS.EXPORT_SCHEMA_STATS ( ownname VARCHAR2, stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
ORA20000
: Object does not exist or insufficient privileges.
This procedure retrieves system statistics and stores them in the user statistics table, identified by stattab
.
DBMS_STATS.EXPORT_SYSTEM_STATS ( stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
ORA20000
: Object does not exist or insufficient privileges.
ORA20002
: Bad user statistics table; may need to be upgraded.
ORA20003
: Unable to export system statistics.
This procedure retrieves statistics for a particular table and stores them in the user statistics table. Cascade results in all index and column statistics associated with the specified table being exported as well.
DBMS_STATS.EXPORT_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, cascade BOOLEAN DEFAULT TRUE, statown VARCHAR2 DEFAULT NULL);
ORA20000
: Object does not exist or insufficient privileges.
This procedure flushes inmemory monitoring information for all tables in the dictionary. Corresponding entries in the *_TAB_MODIFICATIONS
views are updated immediately, without waiting for the Oracle database to flush them periodically. This procedure is useful when you need uptodate information in those views. Because the GATHER_*_STATS
procedures internally flush monitoring information, it is not necessary to run this procedure before gathering the statistics.
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
ORA20000
: Insufficient privileges.
This procedure gathers statistics for all objects in the database.
DBMS_STATS.GATHER_DATABASE_STATS ( estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')), block_sample BOOLEAN DEFAULT FALSE, method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'), degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')), granularity VARCHAR2 DEFAULT 'AUTO', cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')), stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, options VARCHAR2 DEFAULT 'GATHER', objlist OUT ObjectTab, statown VARCHAR2 DEFAULT NULL, gather_sys BOOLEAN DEFAULT FALSE, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE'))); DBMS_STATS.GATHER_DATABASE_STATS ( estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')), block_sample BOOLEAN DEFAULT FALSE, method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'), degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')), granularity VARCHAR2 DEFAULT 'AUTO', cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')), stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, options VARCHAR2 DEFAULT 'GATHER', statown VARCHAR2 DEFAULT NULL, gather_sys BOOLEAN DEFAULT FALSE, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')));
Parameter  Description 


Percentage of rows to estimate ( 

Whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics. 

Accepts:
The default is 

Degree of parallelism. The default for 

Granularity of statistics to collect (only pertinent if the table is partitioned).
'


Gather statistics on the indexes as well. Index statistics gathering is not parallelized. Using this option is equivalent to running the 

User statistics table identifier describing where to save the current statistics. The statistics table is assumed to reside in the same schema as the object being analyzed, so there must be one such table in each schema to use this option. 

Identifier (optional) to associate with these statistics within 

Further specification of which objects to gather statistics for:


List of objects found to be stale or empty. 

Schema containing 

Gathers statistics on the objects owned by the 

If set to the default, does not invalidate the shared cursors dependent on the objects for which statistics are being gathered. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Cannot be used with the 
Statistics for external tables are not collected by this procedure.
ORA20000
: Insufficient privileges.
ORA20001
: Bad input value.
This procedure gathers statistics for dictionary schemas 'SYS
', 'SYSTEM
' and schemas of RDBMS components.
DBMS_STATS.GATHER_DICTIONARY_STATS ( comp_id VARCHAR2 DEFAULT NULL, estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')), block_sample BOOLEAN DEFAULT FALSE, method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'), degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')), granularity VARCHAR2 DEFAULT 'AUTO', cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')), stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, options VARCHAR2 DEFAULT 'GATHER AUTO', objlist OUT ObjectTab, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')));
Parameter  Description 


The component id of the schema to analyze ( 

Percentage of rows to estimate ( 

Determines whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk then the sample values may be somewhat correlated. Only pertinent when performing estimate statistics. 

The method options of the following format:
where integer is between 1 and 254 

Degree of parallelism. The default for 

Granularity of statistics to collect (only pertinent if the table is partitioned).
'


Gathers statistics on indexes also.Index statistics gathering will not be parallelized. Using this option is equivalent to running the 

User statistics table identifier describing where to save the current statistics. 

The (optional) identifier to associate with these statistics within 

Further specification of objects for which to gather statistics:


The list of objects found to be stale or empty. 

The schema containing 

Dependent cursors are not invalidated if this parameter is set to 
You must have the SYSDBA
or both ANALYZE
ANY
DICTIONARY
and ANALYZE
ANY
system privilege to execute this procedure.
ORA20000
: Index does not exist or insufficient privileges.
ORA20001
: Bad input value.
ORA20002
: Bad user statistics table, may need to upgrade it.
This procedure gathers statistics for all fixed objects (dynamic performance tables).
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ( stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')));
Parameter  Description 


The user statistics table identifier describing where to save the current statistics. 

The (optional) identifier to associate with these statistics within 

The schema containing 

Does not invalidate the dependent cursors if set to 
You must have the SYSDBA
or ANALYZE
ANY
DICTIONARY
system privilege to execute this procedure.
ORA20000
: Insufficient privileges.
ORA20001
: Bad input value.
ORA20002
: Bad user statistics table, may need to upgrade it.
This procedure gathers index statistics. It attempts to parallelize as much of the work as possible. Restrictions are described in the individual parameters. This operation will not parallelize with certain types of indexes, including cluster indexes, domain indexes, and bitmap join indexes. The granularity
and no_invalidate
arguments are not relevant to these types of indexes.
DBMS_STATS.GATHER_INDEX_STATS ( ownname VARCHAR2, indname VARCHAR2, partname VARCHAR2 DEFAULT NULL, estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')), stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')), granularity VARCHAR2 DEFAULT 'AUTO', no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')));
Parameter  Description 


Schema of index to analyze. 

Name of index. 

Name of partition. 

Percentage of rows to estimate ( 

User statistics table identifier describing where to save the current statistics. 

Identifier (optional) to associate with these statistics within 

Schema containing 

Degree of parallelism. The default for 

Granularity of statistics to collect (only pertinent if the table is partitioned).
'


Dependent cursors are not invalidated if this parameter is set to 
ORA20000
: Index does not exist or insufficient privileges.
ORA20001
: Bad input value.
This procedure gathers statistics for all objects in a schema.
DBMS_STATS.GATHER_SCHEMA_STATS ( ownname VARCHAR2, estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')), block_sample BOOLEAN DEFAULT FALSE, method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'), degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')), granularity VARCHAR2 DEFAULT 'AUTO', cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')), stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, options VARCHAR2 DEFAULT 'GATHER', statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE'))); DBMS_STATS.GATHER_SCHEMA_STATS ( ownname VARCHAR2, estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')), block_sample BOOLEAN DEFAULT FALSE, method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'), degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')), granularity VARCHAR2 DEFAULT 'AUTO', cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')), stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, options VARCHAR2 DEFAULT 'GATHER', statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')));
Parameter  Description 


Schema to analyze ( 

Percentage of rows to estimate ( 

Whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics. 

Accepts:
The default is 

Degree of parallelism. The default for 

Granularity of statistics to collect (only pertinent if the table is partitioned).
'


Gather statistics on the indexes as well.Index statistics gathering is not parallelized. Using this option is equivalent to running the 

User statistics table identifier describing where to save the current statistics. 

Identifier (optional) to associate with these statistics within 

Further specification of which objects to gather statistics for:


Schema containing 

If set to the default, does not invalidate the shared cursors dependent on the objects for which statistics are being gathered. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Cannot be used with the 
DBMS_STATS.GATHER_SCHEMA_STATS
generates differing sampling rates on partitioned tables when you use the auto_sample_size
constant. DBMS_STATS
tries to determine an adequate sample size for each type of statistic, which is different for each table or column (and each partition, if partitioned). It starts with a sampling rate to get approximately 5000 rows and examines the result based on statistical equations. This process is repeated with increased sampling rate for unsatisfactory results.
In general, the number of distinct values column statistics requires the highest sampling rate among the others, especially when each distinct value repeats a small number of times.
When you use a specific value for the sampling percentage, DBMS_STATS
honors it except for when:
Statistics for external tables are not collected by this procedure.
ORA20000
: Schema does not exist or insufficient privileges.
ORA20001
: Bad input value.
This procedure gathers system statistics.
DBMS_STATS.GATHER_SYSTEM_STATS ( gathering_mode VARCHAR2 DEFAULT 'NOWORKLOAD', interval INTEGER DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid input value.
ORA20002
: Bad user statistics table; may need to be upgraded.
ORA20003
: Unable to gather system statistics.
ORA20004
: Error in the INTERVAL
mode: system parameter job_queue_processes
must be >0.
This procedure gathers table and column (and index) statistics. It attempts to parallelize as much of the work as possible, but there are some restrictions as described in the individual parameters.
DBMS_STATS.GATHER_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2 DEFAULT NULL, estimate_percent NUMBER DEFAULT to_estimate_percent_type (get_param('ESTIMATE_PERCENT')), block_sample BOOLEAN DEFAULT FALSE, method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'), degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')), granularity VARCHAR2 DEFAULT 'AUTO', cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')), stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')));
Parameter  Description 


Schema of table to analyze. 

Name of table. 

Name of partition. 

Percentage of rows to estimate ( 

Whether or not to use random block sampling instead of random row sampling. Random block sampling is more efficient, but if the data is not randomly distributed on disk, then the sample values may be somewhat correlated. Only pertinent when doing an estimate statistics. 

Accepts:
The default is 

Degree of parallelism. The default for 

Granularity of statistics to collect (only pertinent if the table is partitioned).
'


Gather statistics on the indexes for this table. Index statistics gathering is not parallelized. Using this option is equivalent to running the 

User statistics table identifier describing where to save the current statistics. 

Identifier (optional) to associate with these statistics within 

Schema containing 

If set to the default, does not invalidate the shared cursors dependent on the table. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Cannot be used with the 
This operation does not parallelize if the user does not have select privilege on the table being analyzed.
ORA20000
: Table does not exist or insufficient privileges.
ORA20001
: Bad input value.
This procedure generates object statistics from previously collected statistics of related objects. The currently supported objects are btree and bitmap indexes.
DBMS_STATS.GENERATE_STATS ( ownname VARCHAR2, objname VARCHAR2, organized NUMBER DEFAULT 7);
For fully populated schemas, the gather procedures should be used instead when more accurate statistics are desired.
ORA20000
: Unsupported object type of object does not exist.
ORA20001
: Invalid option or invalid statistics.
These procedures gets all columnrelated information. In the form of this procedure that deals with userdefined statistics, the statistics type returned is the type stored, in addition to the userdefined statistics.
DBMS_STATS.GET_COLUMN_STATS ( ownname VARCHAR2, tabname VARCHAR2, colname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, distcnt OUT NUMBER, density OUT NUMBER, nullcnt OUT NUMBER, srec OUT StatRec, avgclen OUT NUMBER, statown VARCHAR2 DEFAULT NULL);
Use the following for userdefined statistics:
DBMS_STATS.GET_COLUMN_STATS ( ownname VARCHAR2, tabname VARCHAR2, colname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, ext_stats OUT RAW, stattypown OUT VARCHAR2 DEFAULT NULL, stattypname OUT VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
ORA20000
: Object does not exist or insufficient privileges or no statistics have been stored for requested object.
These procedures get all indexrelated information. In the form of this procedure that deals with userdefined statistics, the statistics type returned is the type stored, in addition to the userdefined statistics.
DBMS_STATS.GET_INDEX_STATS ( ownname VARCHAR2, indname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, numrows OUT NUMBER, numlblks OUT NUMBER, numdist OUT NUMBER, avglblk OUT NUMBER, avgdblk OUT NUMBER, clstfct OUT NUMBER, indlevel OUT NUMBER, statown VARCHAR2 DEFAULT NULL, cachedblk OUT NUMBER, cachehit OUT NUMBER); DBMS_STATS.GET_INDEX_STATS ( ownname VARCHAR2, indname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, numrows OUT NUMBER, numlblks OUT NUMBER, numdist OUT NUMBER, avglblk OUT NUMBER, avgdblk OUT NUMBER, clstfct OUT NUMBER, indlevel OUT NUMBER, statown VARCHAR2 DEFAULT NULL, guessq OUT NUMBER, cachedblk OUT NUMBER, cachehit OUT NUMBER);
Use the following for userdefined statistics:
DBMS_STATS.GET_INDEX_STATS ( ownname VARCHAR2, indname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, ext_stats OUT RAW, stattypown OUT VARCHAR2 DEFAULT NULL, stattypname OUT VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, cachedblk OUT NUMBER, cachehit OUT NUMBER);
cachedblk
and cachehit
at all times but uses correspondent caching statistics for optimization as part of the table and index statistics only when the user calls DBMS_STATS.GATHER_[TABLE/INDEX/SCHEMA/DATABASE]_STATS
procedure for auto mode or DBMS_STATS.GATHER_SYSTEM_STATS
for manual mode. In order to prevent the user from utilizing inaccurate and unreliable data, the optimizer will compute a 'confidence factor' for each cachehit
and a cachedblk
for each object. If the 'confidence factor' for the value meets confidence criteria, this value will be used, otherwise the defaults will be used.ORA20000
: Object does not exist or insufficient privileges or no statistics have been stored for requested object.
This function returns the default value of parameters of DBMS_STATS
procedures.
DBMS_STATS.GET_PARAM ( pname IN VARCHAR2) RETURN VARCHAR2;
Parameter  Description 


The parameter name. 
ORA20001
: Invalid input values
This function returns oldest timestamp where statistics history is available.Users cannot restore statistics to a timestamp older than this one.
DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY RETURN TIMESTAMP WITH TIMEZONE;
This function returns the current retention value.
DBMS_STATS.GET_STATS_HISTORY_RETENTION RETURN NUMBER;
This procedure gets system statistics from stattab
, or from the dictionary if stattab
is NULL
.
DBMS_STATS.GET_SYSTEM_STATS ( status OUT VARCHAR2, dstart OUT DATE, dstop OUT DATE, pname VARCHAR2, pvalue OUT NUMBER, stattab IN VARCHAR2 DEFAULT NULL, statid IN VARCHAR2 DEFAULT NULL, statown IN VARCHAR2 DEFAULT NULL);
ORA20000
: Object does not exist or insufficient privileges.
ORA20002
: Bad user statistics table; may need to be upgraded.
ORA20003
: Unable to gather system statistics.
ORA20004
: Parameter does not exist.
This procedure gets all tablerelated information.
DBMS_STATS.GET_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, numrows OUT NUMBER, numblks OUT NUMBER, avgrlen OUT NUMBER, statown VARCHAR2 DEFAULT NULL, cachedblk OUT NUMBER, cachehit OUT NUMBER);
cachedblk
and cachehit
at all times but uses correspondent caching statistics for optimization as part of the table and index statistics only when the user calls DBMS_STATS.GATHER_[TABLE/INDEX/SCHEMA/DATABASE]_STATS
procedure for auto mode or DBMS_STATS.GATHER_SYSTEM_STATS
for manual mode. In order to prevent the user from utilizing inaccurate and unreliable data, the optimizer will compute a 'confidence factor' for each cachehit
and a cachedblk
for each object. If the 'confidence factor' for the value meets confidence criteria, this value will be used, otherwise the defaults will be used.ORA20000
: Object does not exist or insufficient privileges or no statistics have been stored for requested object
This procedure retrieves statistics for a particular column from the user statistics table identified by stattab
and stores them in the dictionary.
DBMS_STATS.IMPORT_COLUMN_STATS ( ownname VARCHAR2, tabname VARCHAR2, colname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE);
Parameter  Description 

ownname 
The name of the schema. 

The name of the table to which this column belongs. 

The name of the column. 

The name of the table partition. If the table is partitioned and if 

The user statistics table identifier describing from where to retrieve the statistics. 

The (optional) identifier to associate with these statistics within 

The schema containing 

If set to 

If set to 
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or inconsistent values in the user statistics table.
ORA20005
: Object statistics are locked.
This procedure retrieves statistics for all objects in the database from the user statistics table(s) and stores them in the dictionary.
DBMS_STATS.IMPORT_DATABASE_STATS ( stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE);
Parameter  Description 


User statistics table identifier describing from where to retrieve the statistics. 

Identifier (optional) to associate with these statistics within 

Schema containing 

If set to the default, does not invalidate the shared cursors dependent on the objects to which the statistics are being imported. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Use 

Overrides statistics locked at the object (table) level: 
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or inconsistent values in the user statistics table.
This procedure retrieves statistics for all dictionary schemas ('SYS
', 'SYSTEM
' and RDBMS component schemas) from the user statistics table and stores them in the dictionary.
DBMS_STATS.IMPORT_DICTIONARY_STATS ( stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE);
Parameter  Description 


User statistics table identifier describing from where to retrieve the statistics. 

The (optional) identifier to associate with these statistics within 

The schema containing 

Do not invalidate the dependent cursors if set to 

Overrides statistics locked at the object (table) level: 
You must have the SYSDBA
or both ANALYZE
ANY
DICTIONARY
and ANALYZE
ANY
system privilege to execute this procedure.
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or inconsistent values in the user statistics table.
ORA20002
: Bad user statistics table, may need to upgrade it.
This procedure retrieves statistics for fixed tables from the user statistics table(s) and stores them in the dictionary.
DBMS_STATS.IMPORT_FIXED_OBJECTS_STATS ( stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE);
Parameter  Description 


User statistics table identifier describing from where to retrieve the statistics. 

Identifier (optional) to associate with these statistics within 

Schema containing 

If set to the default, does not invalidate the shared cursors dependent on the objects to which the statistics are being imported. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Use 

Overrides statistics lock: 
You must have the SYSDBA
or ANALYZE
ANY
DICTIONARY
system privilege to execute this procedure.
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or inconsistent values in the user statistics table.
ORA20002
: Bad user statistics table, may need to upgrade it.
This procedure retrieves statistics for a particular index from the user statistics table identified by stattab
and stores them in the dictionary.
DBMS_STATS.IMPORT_INDEX_STATS ( ownname VARCHAR2, indname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE);
Parameter  Description 


Name of the schema. 

Name of the index. 

Name of the index partition. If the index is partitioned and if 

User statistics table identifier describing from where to retrieve the statistics. 

Identifier (optional) to associate with these statistics within 

Schema containing 

If set to the default, does not invalidate the shared cursors dependent on the table. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Use 

Imports statistics even if index statistics are locked. 
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or inconsistent values in the user statistics table.
ORA20005
: Object statistics are locked.
This procedure retrieves statistics for all objects in the schema identified by ownname from the user statistics table and stores them in the dictionary.
DBMS_STATS.IMPORT_SCHEMA_STATS ( ownname VARCHAR2, stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULTto_no_invalidate_type( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE);
Parameter  Description 


Name of the schema. 

User statistics table identifier describing from where to retrieve the statistics. 

Identifier (optional) to associate with these statistics within 

Schema containing 

If set to the default, does not invalidate the shared cursors dependent on the objects to which the statistics are being imported. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Use 

Overrides statistics locked at the object (table) level: 
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or inconsistent values in the user statistics table.
This procedure retrieves system statistics from the user statistics table, identified by stattab, and stores the statistics in the dictionary.
DBMS_STATS.IMPORT_SYSTEM_STATS ( stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL);
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or inconsistent values in the user statistics table.
ORA20002
: Bad user statistics table; may need to be upgraded.
ORA20003
: Unable to import system statistics.
This procedure retrieves statistics for a particular table from the user statistics table identified by stattab
and stores them in the dictionary. Cascade results in all index and column statistics associated with the specified table being imported as well.
DBMS_STATS.IMPORT_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2, statid VARCHAR2 DEFAULT NULL, cascade BOOLEAN DEFAULT TRUE, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE);
Parameter  Description 


Name of the schema. 

Name of the table. 

Name of the table partition. If the table is partitioned and if 

User statistics table identifier describing from where to retrieve the statistics. 

Identifier (optional) to associate with these statistics within 

If true, then column and index statistics for this table are also imported. 

Schema containing 

If set to the default, does not invalidate the shared cursors dependent on the table. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Use 

Imports statistics even if table statistics are locked. 
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or inconsistent values in the user statistics table.
This procedure locks the statistics of all tables of a schema.
DBMS_STATS.LOCK_SCHEMA_STATS ( ownname VARCHAR2);
Parameter  Description 


The name of the schema to lock. 
See "Usage Notes" for LOCK_TABLE_STATS Procedure.
This procedure locks the statistics on the table.
DBMS_STATS.LOCK_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2);
Parameter  Description 


The name of the schema. 

The name of the table. 
SET_*,
DELETE_*,
IMPORT_*,
GATHER_*
procedures that modify statistics in the dictionary of an individual table, index or column will raise an error if statistics of the object is locked.GATHER_SCHEMA_STATS
) will skip modifying the statistics of an object if it is locked. Many procedures have force argument to override the lock.EXPORT_*_STATS
procedures.These procedures convert userspecified minimum, maximum, and histogram endpoint datatypespecific values into Oracle's internal representation for future storage using SET_COLUMN_STATS
.
DBMS_STATS.PREPARE_COLUMN_VALUES ( srec IN OUT StatRec, charvals CHARARRAY); DBMS_STATS.PREPARE_COLUMN_VALUES ( srec IN OUT StatRec, datevals DATEARRAY); DBMS_STATS.PREPARE_COLUMN_VALUES ( srec IN OUT StatRec, dblvals DBLARRAY); DBMS_STATS.PREPARE_COLUMN_VALUES ( srec IN OUT StatRec, fltvals FLTARRAY); DBMS_STATS.PREPARE_COLUMN_VALUES ( srec IN OUT StatRec, numvals NUMARRAY); DBMS_STATS.PREPARE_COLUMN_VALUES ( srec IN OUT StatRec, rawvals RAWARRAY);
pragma restrict_references(prepare_column_values, WNDS, RNDS, WNPS, RNPS); pragma restrict_references(prepare_column_values_nvarchar, WNDS, RNDS, WNPS, RNPS); pragma restrict_references(prepare_column_values_rowid, WNDS, RNDS, WNPS, RNPS);
Datatypespecific input parameters (use one) are shown in Table 9350.
ORA20001
: Invalid or inconsistent input values.
This procedure converts userspecified minimum, maximum, and histogram endpoint datatypespecific values into Oracle's internal representation for future storage using SET_COLUMN_STATS
.
DBMS_STATS.PREPARE_COLUMN_VALUES_NVARCHAR2 ( srec IN OUT StatRec, nvmin NVARCHAR2, nvmax NVARCHAR2);
pragma restrict_references(prepare_column_values_nvarchar, WNDS, RNDS, WNPS, RNPS);
Datatypespecific input parameters (use one) are shown in Table 9350.
ORA20001
: Invalid or inconsistent input values.
This procedure converts userspecified minimum, maximum, and histogram endpoint datatypespecific values into Oracle's internal representation for future storage using SET_COLUMN_STATS
.
DBMS_STATS.PREPARE_COLUMN_VALUES_ROWID ( srec IN OUT StatRec, rwmin ROWID, rwmax ROWID);
pragma restrict_references(prepare_column_values_rowid, WNDS, RNDS, WNPS, RNPS);
Datatypespecific input parameters (use one) are shown in Table 9350.
Type  Description 


The minimum and maximum values when the column type is 
ORA20001
: Invalid or inconsistent input values.
This procedure purges old versions of statistics saved in the dictionary. To run this procedure, you must have the SYSDBA
or both ANALYZE
ANY
DICTIONARY
and ANALYZE
ANY
system privilege.
DBMS_STATS.PURGE_STATS( before_timestamp TIMESTAMP WITH TIME ZONE);
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or inconsistent values.
This procedure restores statistics of all tables of the database as of a specified timestamp (as_of_timestamp
).
DBMS_STATS.RESTORE_DATABSE_STATS( as_of_timestamp TIMESTAMP WITH TIME ZONE, force BOOLEAN DEFAULT FALSE);
Parameter  Description 


The timestamp to which to restore statistics. 

Restores statistics even if their statistics are locked. 
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or inconsistent values.
ORA20006
: Unable to restore statistics, statistics history not available.
This procedure restores statistics of all dictionary tables (tables of 'SYS
', 'SYSTEM
' and RDBMS component schemas) as of a specified timestamp (as_of_timestamp
).
DBMS_STATS.RESTORE_DICTIONARY_STATS( as_of_timestamp TIMESTAMP WITH TIME ZONE, force BOOLEAN DEFAULT FALSE);
Parameter  Description 


The timestamp to which to restore statistics. 

Restores statistics even if their statistics are locked. 
To run this procedure, you must have the SYSDBA
or both ANALYZE
ANY
DICTIONARY
and ANALYZE
ANY
system privilege.
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or inconsistent values.
ORA20006
: Unable to restore statistics, statistics history not available.
This procedure restores statistics of all fixed tables as of a specified timestamp (as_of_timestamp
).
DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS( as_of_timestamp TIMESTAMP WITH TIME ZONE, force BOOLEAN DEFAULT FALSE);
Parameter  Description 


The timestamp to which to restore statistics. 

Restores statistics even if their statistics are locked. 
To run this procedure, you must have the SYSDBA
or ANALYZE
ANY
DICTIONARY
system privilege.
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or inconsistent values.
ORA20006
: Unable to restore statistics, statistics history not available.
This procedure restores statistics of all tables of a schema as of a specified timestamp (as_of_timestamp
).
DBMS_STATS.RESTORE_SCHEMA_STATS( ownname VARCHAR2, as_of_timestamp TIMESTAMP WITH TIME ZONE, force BOOLEAN DEFAULT FALSE);
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or inconsistent values.
ORA20006
: Unable to restore statistics, statistics history not available.
This procedure restores system statistics as of a specified timestamp (as_of_timestamp
).
DBMS_STATS.RESTORE_SCHEMA_STATS( as_of_timestamp TIMESTAMP WITH TIME ZONE);
Parameter  Description 


The timestamp to which to restore statistics. 
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or inconsistent values.
ORA20006
: Unable to restore statistics, statistics history not available.
This procedure restores statistics of a table as of a specified timestamp (as_of_timestamp
). The procedure will restore statistics of associated indexes and columns as well. If the table statistics were locked at the specified timestamp the procedure will lock the statistics. The procedure will not restore user defined statistics.
DBMS_STATS.RESTORE_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, as_of_timestamp TIMESTAMP WITH TIME ZONE, restore_cluster_index BOOLEAN DEFAULT FALSE, force BOOLEAN DEFAULT FALSE);
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or inconsistent values.
ORA20006
: Unable to restore statistics, statistics history not available.
This procedure sets columnrelated information. In the version of this procedure that deals with userdefined statistics, the statistics type specified is the type to store in the dictionary, in addition to the actual userdefined statistics. If this statistics type is NULL
, the statistics type associated with the index or column is stored.
DBMS_STATS.SET_COLUMN_STATS ( ownname VARCHAR2, tabname VARCHAR2, colname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, distcnt NUMBER DEFAULT NULL, density NUMBER DEFAULT NULL, nullcnt NUMBER DEFAULT NULL, srec StatRec DEFAULT NULL, avgclen NUMBER DEFAULT NULL, flags NUMBER DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE);
Use the following for userdefined statistics:
DBMS_STATS.SET_COLUMN_STATS ( ownname VARCHAR2, tabname VARCHAR2, colname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, ext_stats RAW, stattypown VARCHAR2 DEFAULT NULL, stattypname VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type( get_param('NO_INVALIDATE')), force BOOLEAN DEFAULT FALSE);
Parameter  Description 


Name of the schema. 

Name of the table to which this column belongs. 

Name of the column. 

Name of the table partition in which to store the statistics. If the table is partitioned and 

User statistics table identifier describing where to store the statistics. If 

Identifier (optional) to associate with these statistics within 

The userdefined statistics. 

Schema of the statistics type. 

Name of the statistics type. 

Number of distinct values. 

Column density. If this value is 

Number of 



Average length for the column (in bytes). 

For internal Oracle use (should be left as 

Schema containing 

If set to the default, does not invalidate the shared cursors dependent on the table. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Use 

Sets the values even if statistics of the column are locked. 
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or inconsistent input values.
ORA20005
: Object statistics are locked.
These procedures set indexrelated information. In the version of this procedure that deals with userdefined statistics, the statistics type specified is the type to store in the dictionary, in addition to the actual userdefined statistics. If this statistics type is NULL, the statistics type associated with the index or column is stored.
DBMS_STATS.SET_INDEX_STATS ( ownname VARCHAR2, indname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, numrows NUMBER DEFAULT NULL, numlblks NUMBER DEFAULT NULL, numdist NUMBER DEFAULT NULL, avglblk NUMBER DEFAULT NULL, avgdblk NUMBER DEFAULT NULL, clstfct NUMBER DEFAULT NULL, indlevel NUMBER DEFAULT NULL, flags NUMBER DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type( get_param('NO_INVALIDATE')), guessq NUMBER DEFAULT NULL, cachedblk NUMBER DEFAULT NULL, cachehit NUMBER DEFUALT NULL, force BOOLEAN DEFAULT FALSE);
Use the following for userdefined statistics:
DBMS_STATS.SET_INDEX_STATS ( ownname VARCHAR2, indname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, ext_stats RAW, stattypown VARCHAR2 DEFAULT NULL, stattypname VARCHAR2 DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type( get_param('NO_INVALIDATE')), cachedblk NUMBER DEFAULT NULL, cachehit NUMBER DEFUALT NULL, force BOOLEAN DEFAULT FALSE);
Parameter  Description 


Name of the schema. 

Name of the index. 

Name of the index partition in which to store the statistics. If the index is partitioned and if 

User statistics table identifier describing where to store the statistics. If 

Identifier (optional) to associate with these statistics within 

The userdefined statistics. 

Schema of the statistics type. 

Name of the statistics type. 

Number of rows in the index (partition). 

Number of leaf blocks in the index (partition). 

Number of distinct keys in the index (partition). 

Average integral number of leaf blocks in which each distinct key appears for this index (partition). If not provided, then this value is derived from 

Average integral number of data blocks in the table pointed to by a distinct key for this index (partition). If not provided, then this value is derived from 

See 

Height of the index (partition). 

For internal Oracle use (should be left as 

Schema containing 

If set to the default, does not invalidate the shared cursors dependent on the table. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Use 

Guess quality. See the 

The average number of blocks in the buffer cache for the segment (index/table/index partition/table partition). 

The average cache hit ratio for the segment (index/table/index partition/table partition). 

Sets the values even if statistics of the index are locked. 
cachedblk
and cachehit
at all times but uses correspondent caching statistics for optimization as part of the table and index statistics only when the user calls DBMS_STATS.GATHER_[TABLE/INDEX/SCHEMA/DATABASE]_STATS
procedure for auto mode or DBMS_STATS.GATHER_SYSTEM_STATS
for manual mode. In order to prevent the user from utilizing inaccurate and unreliable data, the optimizer will compute a 'confidence factor' for each cachehit
and a cachedblk
for each object. If the 'confidence factor' for the value meets confidence criteria, this value will be used, otherwise the defaults will be used.ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid input value.
ORA20005
: Object statistics are locked.
This procedure sets default values for parameters of DBMS_STATS
procedures. You can use the GET_PARAM
Function
to get the current default value of a parameter.
DBMS_STATS.SET_PARAM ( pname IN VARCHAR2, pval IN VARCHAR2);
Parameter  Description 


The parameter name The default value for following parameters can be set. 

The parameter value. If 
To run this procedure, you must have the SYSDBA
or both the ANALYZE
ANY
DICTIONARY
and ANALYZE
ANY
system privileges.
Note that both arguments are of type VARCHAR2
and the values need to be enclosed in quotes.
ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid or illegal input value.
dbms_stats.set_param('CASCADE','DBMS_STATS.AUTO_CASCADE'); dbms_stats.set_param('ESTIMATE_PERCENT','5'); dbms_stats.set_param('DEGREE','NULL');
This procedure sets systems statistics.
DBMS_STATS.SET_SYSTEM_STATS ( pname VARCHAR2, pvalue NUMBER, stattab IN VARCHAR2 DEFAULT NULL, statid IN VARCHAR2 DEFAULT NULL, statown IN VARCHAR2 DEFAULT NULL);
cachedblk
and cachehit
at all times but uses correspondent caching statistics for optimization as part of the table and index statistics only when the user calls DBMS_STATS.GATHER_[TABLE/INDEX/SCHEMA/DATABASE]_STATS
procedure for auto mode or DBMS_STATS.GATHER_SYSTEM_STATS
for manual mode. In order to prevent the user from utilizing inaccurate and unreliable data, the optimizer will compute a 'confidence factor' for each cachehit
and a cachedblk
for each object. If the 'confidence factor' for the value meets confidence criteria, this value will be used, otherwise the defaults will be used.ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid input value.
ORA20002
: Bad user statistics table; may need to be upgraded.
ORA20003
: Unable to set system statistics.
ORA20004
: Parameter does not exist.
This procedure sets tablerelated information.
DBMS_STATS.SET_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2 DEFAULT NULL, stattab VARCHAR2 DEFAULT NULL, statid VARCHAR2 DEFAULT NULL, numrows NUMBER DEFAULT NULL, numblks NUMBER DEFAULT NULL, avgrlen NUMBER DEFAULT NULL, flags NUMBER DEFAULT NULL, statown VARCHAR2 DEFAULT NULL, no_invalidate BOOLEAN DEFAULT to_no_invalidate_type ( get_param('NO_INVALIDATE')), cachedblk NUMBER DEFAULT NULL, cachehit NUMBER DEFUALT NULL, force BOOLEAN DEFAULT FALSE);
Parameter  Description 


Name of the schema. 

Name of the table. 

Name of the table partition in which to store the statistics. If the table is partitioned and 

User statistics table identifier describing where to store the statistics. If 

Identifier (optional) to associate with these statistics within 

Number of rows in the table (partition). 

Number of blocks the table (partition) occupies. 

Average row length for the table (partition). 

For internal Oracle use (should be left as 

Schema containing 

If set to the default, does not invalidate the shared cursors dependent on the table. (The statements corresponding to those cursors are not recompiled until they are gone or invalidated.) Use 

The average number of blocks in the buffer cache for the segment (index/table/index partition/table partition). 

The average cache hit ratio for the segment (index/table/index partition/table partition). 

Sets the values even if statistics of the table are locked. 
cachedblk
and cachehit
at all times but uses correspondent caching statistics for optimization as part of the table and index statistics only when the user calls DBMS_STATS.GATHER_[TABLE/INDEX/SCHEMA/DATABASE]_STATS
procedure for auto mode or DBMS_STATS.GATHER_SYSTEM_STATS
for manual mode. In order to prevent the user from utilizing inaccurate and unreliable data, the optimizer will compute a 'confidence factor' for each cachehit
and a cachedblk
for each object. If the 'confidence factor' for the value meets confidence criteria, this value will be used, otherwise the defaults will be used.ORA20000
: Object does not exist or insufficient privileges.
ORA20001
: Invalid input value.
ORA20005
: Object statistics are locked.
This procedure unlocks the statistics on all the tables in schema.
DBMS_STATS.UNLOCK_SCHEMA_STATS ( ownname VARCHAR2);
Parameter  Description 


The name of the schema. 
SET_*,
DELETE_*,
IMPORT_*,
GATHER_*
procedures that modify statistics in the dictionary of an individual table, index or column will raise an error if statistics of the object is locked.GATHER_SCHEMA_STATS
) will skip modifying the statistics of an object if it is locked. Many procedures have force argument to override the lock.This procedure unlocks the statistics on the table.
DBMS_STATS.UNLOCK_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2);
Parameter  Description 


The name of the schema. 

The name of the table. 
SET_*,
DELETE_*,
IMPORT_*,
GATHER_*
procedures that modify statistics in the dictionary of an individual table, index or column will raise an error if statistics of the object is locked.GATHER_SCHEMA_STATS
) will skip modifying the statistics of an object if it is locked. Many procedures have force argument to override the lock.This procedure upgrades a user statistics table from an older version.
DBMS_STATS.UPGRADE_STAT_TABLE ( ownname VARCHAR2, stattab VARCHAR2);
Parameter  Description 


Name of the schema. 

Name of the table. 
ORA20000: Unable to upgrade table.