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