Exporting and Importing Statistics

Oracle Version : 10.1.0.5.0
OS : Windows XP



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@ORCL
Connected.
SQL> --Create Table to hold statistics
SQL> exec dbms_stats.create_stat_table('SCOTT','STATISTICS');
PL/SQL procedure successfully completed.
SQL> --Export Statistics for Schema Scott
begin
dbms_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=STATISTICS
Export: Release 10.1.0.5.0 - Production on Tue Jul 24 16:26:21 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table STATISTICS 24 rows exported

Export terminated successfully without warnings.

SQL> host imp scott/tiger@OEM file=c:\statistics.dmp tables=statistics
Import: Release 10.1.0.5.0 - Production on Tue Jul 24 16:31:15 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. . importing table "STATISTICS" 24 rows imported

Import terminated successfully without warnings.