Home » Oracle » Oracle Database » How to import statistics in Oracle

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);
How to import  statistics in Oracle

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);
How to import  statistics in Oracle

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);
How to import  statistics in Oracle

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.

See also  Logical view of the data model in R12.2

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

Related Articles
How to transfer statistics between databases in Oracle
How to create stat table in Oracle
How to delete statistics from Table in Oracle 
How to check column statistics in Oracle
How to export statistics in Oracle

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top