If you wish to save your statistics of schema or table, which you can use later during any query issue Or if you wish copy the statistics from production database to development , then this method will be helpful.
Here i will take export of statistics of a table RAJ.TEST from PROD and import into TEST
DEMO:
create a table to store the stats:
--- RAJ is the owner of the stats table, STAT_TEST - name of the stats table PROD> exec DBMS_STATS.CREATE_STAT_TABLE('RAJ','STAT_TEST','SYSAUX'); PL/SQL procedure successfully completed. SQL> ; 1* select owner,table_name from dba_tables where table_name='STAT_TEST' SQL> / OWNER TABLE_NAME ------------ ------------ RAJ STAT_TEST SQL>
Now export the statistics of the table RAJ.TEST to stats table ( STAT_TEST)
PROD> exec dbms_stats.export_table_stats(ownname=>'RAJ', tabname=>'TEST', stattab=>'STAT_TEST', cascade=>true); PL/SQL procedure successfully completed.
Now take expdp of this stats table(STAT_TEST)
[oracle@PROD]$ expdp dumpfile=stats.dmp logfile=stats.log tables=raj.STAT_TEST directory=DIR1 Export: Release 12.1.0.2.0 - Production on Fri Aug 28 10:09:26 2015 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Username: raj/raj@orcl Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Starting "RAJ"."SYS_EXPORT_TABLE_01": raj/********@orcl dumpfile=stats.dmp logfile=stats.log tables=raj.STAT_TEST directory=DIR1 Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 192 KB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER . . exported "RAJ"."STAT_TEST" 19.24 KB 19 rows Master table "RAJ"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for RAJ.SYS_EXPORT_TABLE_01 is: /home/oracle/DIR1/stats.dmp Job "RAJ"."SYS_EXPORT_TABLE_01" successfully completed at Fri Aug 28 10:12:33 2015 elapsed 0 00:03:00
Move the dump file from PROD to TEST machine and import the same
[oracle@TEST]$ impdp dumpfile=stats.dmp logfile=stats.log tables=raj.STAT_TEST directory=DIR1 table_exists_action=REPLACE Import: Release 12.1.0.2.0 - Production on Fri Aug 28 10:12:42 2015 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Username: raj/raj@orcl Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Master table "RAJ"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "RAJ"."SYS_IMPORT_TABLE_01": raj/********@orcl dumpfile=stats.dmp logfile=stats.log tables=raj.STAT_TEST directory=DIR1 table_exists_action=REPLACE Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "RAJ"."STAT_TEST" 19.24 KB 19 rows Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Job "RAJ"."SYS_IMPORT_TABLE_01" successfully completed at Fri Aug 28 10:14:36 2015 elapsed 0 00:01:42
Now import the statistics in database:
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 ------------ ------------ --------- RAJ TEST 05-AUG-15 SQL> exec dbms_stats.import_table_stats(ownname=>'RAJ', 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 ------------ ------------ --------- RAJ TEST 28-AUG-15
We can observe after import_table_stats, last_analyzed date has been updated.
We can do export/import statistics at schema level and database level:
For database :
----For export exec dbms_stats.EXPORT_DATABASE_STATS('',' ',' '); --- For import exec dbms_stats.IMPORT_DATABASE_STATS(' ',' ',' ');
For schema:
--- For export exec dbms_stats.export_schema_stats( ownname=>, stattab=>'', statid=>'' ); --- For import exec dbms_stats.import_schema_stats( ownname=>, stattab=>'', statid=>'' );
Thanks, nice article.
How to Transfer Statistics from one Database to Another (Doc ID 333742.1)
Yes you can follow this metalink
Hi,
Nice article .Thanks.
Is it possible to export schema level statistics and import only some of the schema table’s statistics from this exported statistics ?
like ,
exec dbms_stats.export_schema_stats( ownname=>, stattab=>”, statid=>” );
exec dbms_stats.import_table_stats(ownname=>’RAJ’, tabname=>’TEST’, stattab=>’STAT_TEST’, cascade=>true);
Yes Dear, It is possible for schema level also.
exec DBMS_STATS.CREATE_STAT_TABLE(‘DBACLASS’,’SCOTT_STAT_27SEP2016′);
exec dbms_stats.export_schema_stats( ownname=>’SCOTT’, STATOWN=>’DBACLASS’,stattab=>’SCOTT_STAT_27SEP2016′);
Regards
admin