How to Export and Import Oracle Database Statistics Information

Some times  not convenient to the database for analyisis when the database have a large mount of data.There may be analyisis it on the test database first and then applied the statistics information to the production database.We can use the following way to achieve:

1.create a table for storage the statistics information on test database:
SQL> exec dbms_stats.create_stat_table('HONCHO','STATS_TABLE','HONCHO');

PL/SQL procedure successfully completed.

SQL> desc honcho.stats_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STATID                                             VARCHAR2(30)
 TYPE                                               CHAR(1)
 VERSION                                            NUMBER
 FLAGS                                              NUMBER
 C1                                                 VARCHAR2(30)
 C2                                                 VARCHAR2(30)
 C3                                                 VARCHAR2(30)
 C4                                                 VARCHAR2(30)
 C5                                                 VARCHAR2(30)
 N1                                                 NUMBER
 N2                                                 NUMBER
 N3                                                 NUMBER
 N4                                                 NUMBER
 N5                                                 NUMBER
 N6                                                 NUMBER
 N7                                                 NUMBER
 N8                                                 NUMBER
 N9                                                 NUMBER
 N10                                                NUMBER
 N11                                                NUMBER
 N12                                                NUMBER
 D1                                                 DATE
 R1                                                 RAW(32)
 R2                                                 RAW(32)
 CH1                                                VARCHAR2(1000)

2.Gather the statistics information on test database:

SQL> exec dbms_stats.gather_schema_stats('HONCHO');
BEGIN dbms_stats.gather_schema_stats('HONCHO'); END;

*
ERROR at line 1:
ORA-20000: Insufficient privileges to analyze an object in Schema
ORA-06512: at "SYS.DBMS_STATS", line 13197
ORA-06512: at "SYS.DBMS_STATS", line 13556
ORA-06512: at "SYS.DBMS_STATS", line 13634
ORA-06512: at "SYS.DBMS_STATS", line 13593
ORA-06512: at line 1

SQL> show user
USER is "SYS"

SQL> exec dbms_space.auto_space_advisor_job_proc;
BEGIN dbms_space.auto_space_advisor_job_proc; END;

*
ERROR at line 1:
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.PRVT_ADVISOR", line 1624
ORA-06512: at "SYS.DBMS_ADVISOR", line 186
ORA-06512: at "SYS.DBMS_SPACE", line 1338
ORA-06512: at "SYS.DBMS_SPACE", line 1554
ORA-06512: at line 1

SQL> select name from v$tempfile;

no rows selected

SQL> select name,status from v$tempfile;

no rows selected

Why did the test database without the temporarty file ,I don't know,Let us no matter it first.

SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/honcho/temp01.dbf' size 100m;
alter tablespace temp add tempfile '/u01/app/oracle/oradata/honcho/temp01.dbf' size 100m
*
ERROR at line 1:
ORA-01119: error in creating database file
'/u01/app/oracle/oradata/honcho/temp01.dbf'
ORA-27038: created file already exists
Additional information: 1

[oracle@honcho honcho]$ ls -lrt
total 1361772
-rw-r-----  1 oracle oinstall  20979712 Dec  4 11:18 temp01.dbf
-rw-r-----  1 oracle oinstall  52429312 Jan 12 16:03 redo02.log
-rw-r-----  1 oracle oinstall   5251072 Jan 12 16:03 users01.dbf
-rw-r-----  1 oracle oinstall  10493952 Jan 12 16:03 tt.dbf
-rw-r-----  1 oracle oinstall  52429312 Jan 12 16:03 redo03.log
-rw-r-----  1 oracle oinstall   1056768 Jan 12 16:03 flm_tbs01.dbf
-rw-r-----  1 oracle oinstall 104865792 Jan 12 16:03 example01.dbf
-rw-r-----  1 oracle oinstall 104865792 Jan 12 17:47 honcho01.dbf
-rw-r-----  1 oracle oinstall  31465472 Jan 12 18:25 undotbs01.dbf
-rw-r-----  1 oracle oinstall 692068352 Jan 12 18:25 system01.dbf
-rw-r-----  1 oracle oinstall 262152192 Jan 12 18:26 sysaux01.dbf
-rw-r-----  1 oracle oinstall  52429312 Jan 12 18:27 redo01.log
-rw-r-----  1 oracle oinstall   7389184 Jan 12 18:27 control03.ctl
-rw-r-----  1 oracle oinstall   7389184 Jan 12 18:27 control02.ctl
-rw-r-----  1 oracle oinstall   7389184 Jan 12 18:27 control01.ctl
[oracle@honcho honcho]$ rm temp01.dbf

SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/honcho/temp01.dbf' size 100m;

Tablespace altered.

SQL> exec dbms_stats.gather_schema_stats('HONCHO');

PL/SQL procedure successfully completed.

OK, the problem is sloved.

3. Confirm statistics information on test database.

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select table_name,last_analyzed from dba_tables where owner = 'HONCHO' order by last_analyzed desc;

TABLE_NAME                     LAST_ANALYZED
------------------------------ -------------------
TEST3                          2012-01-12 18:28:09
T1                             2012-01-12 18:28:08
TEST                           2012-01-12 18:28:08
T                              2012-01-12 18:28:08
STATS_TABLE                    2012-01-12 18:28:07
LOG_MNR                        2012-01-12 18:28:07

4. Export the statistics information to table STATS_TABLE on test database:

SQL> exec dbms_stats.export_schema_stats('HONCHO','STATS_TABLE');

PL/SQL procedure successfully completed.

5. Check the statistics information on test database.

SQL> select count(*) from honcho.stats_table;

  COUNT(*)
----------
        56
       
6.Delete the statistics information on product database.

SQL> exec dbms_stats.delete_schema_stats('HONCHO');

PL/SQL procedure successfully completed.

7.export table stats_table on test db and import to product database.
exp/imp or expdp/impdp

Because I just test it at test database,omit this step.

8. Import the statistics information on product database.

SQL> exec dbms_stats.import_schema_stats('HONCHO','STATS_TABLE');

PL/SQL procedure successfully completed.

9. Check the statistics on product database.

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select table_name,last_analyzed from dba_tables where owner = 'HONCHO' order by last_analyzed desc;

TABLE_NAME                     LAST_ANALYZED
------------------------------ -------------------
TEST3                          2012-01-12 18:28:09
T1                             2012-01-12 18:28:08
TEST                           2012-01-12 18:28:08
T                              2012-01-12 18:28:08
STATS_TABLE                    2012-01-12 18:28:07
LOG_MNR                        2012-01-12 18:28:07

10. Drop the statistics table.

SQL> exec dbms_stats.drop_stat_table('HONCHO','STATS_TABLE');

PL/SQL procedure successfully completed.

SQL> desc honcho.stats_table
ERROR:
ORA-04043: object honcho.stats_table does not exist