Exporting and Importing Statistics

Exporting and Importing Statistics
Statistics can be exported and imported from the data dictionary to user-owned tables. This enables you to create multiple versions of statistics for the same schema. It also enables you to copy statistics from one database to another database. You may want to do this to copy the statistics from a production database to a scaled-down test database.Before exporting statistics, you first need to create a table for holding the statistics. This statistics table is created using the procedure DBMS_STATS.CREATE_STAT_TABLE. After this table is created, then you can export statistics from the data dictionary into your statistics table using the DBMS_STATS.EXPORT_*_STATS procedures. The statistics can then be imported using the DBMS_STATS.IMPORT_*_STATS procedures.Note that the optimizer does not use statistics stored in a user-owned table. The only statistics used by the optimizer are the statistics stored in the data dictionary. In order to have the optimizer use the statistics in user-owned tables, you must import those statistics into the data dictionary using the statistics import procedures.In order to move statistics from one database to another, you must first export the statistics on the first database, then copy the statistics table to the second database, using the EXP and IMP utilities or other mechanisms, and finally import the statistics into the second database.SQL> conn system/oracle@ORCLConnected.SQL> --Create Table to hold statisticsSQL> exec dbms_stats.create_stat_table('SCOTT','STATISTICS');PL/SQL procedure successfully completed.SQL> --Export Statistics for Schema Scottbegindbms_stats.export_schema_stats(ownname => 'SCOTT',stattab => 'STATISTICS');end;/PL/SQL procedure successfully completed.SQL> host exp scott/tiger@orcl file=c:\statistics.dmp tables=STATISTICSExport: Release 10.1.0.5.0 - Production on Tue Jul 24 16:26:21 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsExport done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character setAbout to export specified tables via Conventional Path .... . exporting table STATISTICS 24 rows exportedExport terminated successfully without warnings.SQL> host imp scott/tiger@OEM file=c:\statistics.dmp tables=statisticsImport: Release 10.1.0.5.0 - Production on Tue Jul 24 16:31:15 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsExport file created by EXPORT:V10.01.00 via conventional pathimport done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set. importing SCOTT's objects into SCOTT. . importing table "STATISTICS" 24 rows importedImport terminated successfully without warnings.