How to import statistics in Oracle

We can import statistics from the user statistics table identified by stattab and stored in the data dictionary. Basically, you can import the statistics which you exported sometime before to the use statistics table. To import statistics in Oracle, you can use the DBMS_STATS package. This package provides several procedures and functions for managing database statistics. Here are the basic steps:

(1) Connect to your database as a user with the necessary privileges (such as SYS or SYSTEM).

(2) Here I am assuming, we can STATTAB table containing the statistics data

(3) Suppose you want to import stats for a particular table, then Invoke the DBMS_STATS package using the below package

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,
   stat_category   VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);

Example

EXEC DBMS_STATS.TABLE_TABLE_STATS(OWNNAME=>'SCOTT',TABNAME=>'EMP', STATTAB=>'STATTAB',STATID=>'EXP_1',STATOWN=>'SCOTT);

(4) Suppose you want to import stats for a whole schema, then Invoke the DBMS_STATS package using the below package

DBMS_STATS.IMPORT_SCHEMA_STATS (
   ownname         VARCHAR2,
   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,
   stat_category   VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);

Example

EXEC DBMS_STATS.IMPORT_SCHEMA_STATS(OWNNAME=>'SCOTT', STATTAB=>'STATTAB',STATID=>'EXP_SC_1',STATOWN=>'SCOTT');

(5) Suppose you want to import stats for a particular index, then Invoke the DBMS_STATS package using the below package

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);

Example

EXEC DBMS_STATS.IMPORT_TABLE_STATS(OWNNAME=>'SCOTT',INDNAME=>'EMP_IDX', STATTAB=>'STATTAB',STATID=>'EXP_IND_1',STATOWN=>'SCOTT);

Please be aware that you need the necessary privileges to execute these procedures.

I hope you like this article on How to import statistics in Oracle

Related Posts

Leave a Comment Cancel Reply