How To Export And Import Statistics In Oracle

How To Export And Import Statistics In Oracle

For Table:

PROD> exec dbms_stats.export_table_stats(ownname=>’SCOTT’, tabname=>’TEST’, stattab=>’STAT_TEST’, cascade=>true);

PL/SQL procedure successfully completed.

SQL> set lines 200
SQL> set pagesize 200
SQL> col table_name for a12
SQL> col owner for a12
SQL> select owner,table_name,last_analyzed from dba_tables where table_name=’TEST’;

OWNER TABLE_NAME LAST_ANAL
———— ———— ———
SCOTT TEST 05-AUG-19

SQL> exec dbms_stats.import_table_stats(ownname=>’SCOTT’, tabname=>’TEST’, stattab=>’STAT_TEST’, cascade=>true);

PL/SQL procedure successfully completed.

SQL> select owner,table_name,last_analyzed from dba_tables where table_name=’TEST’;

OWNER TABLE_NAME LAST_ANAL
———— ———— ———
SCOTT TEST 10-AUG-19

For database :

—For export
exec dbms_stats.EXPORT_DATABASE_STATS(‘<stat_table_name>’,'<stat_name>’,'<stat_owner>’);

— For import
exec dbms_stats.IMPORT_DATABASE_STATS(‘<stat_table_name>’,'<stat_name>’,'<stat_owner>’);

For schema:

— For export
exec dbms_stats.export_schema_stats( ownname=>, stattab=>”, statid=>” );

— For import
exec dbms_stats.import_schema_stats( ownname=>, stattab=>”, statid=>” );