Shulou(Shulou.com)05/31 Report--

How to export import statistics in Oracle? In view of this problem, this article introduces the corresponding analysis and answers in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

Export and import of schema-level statistics

This is done by calling DBMS_STATS.EXPORT_SCHEMA_STATS and DBMS_STATS.IMPORT_SCHEMA_STATS.

Export and import of database-level statistics

This is done by calling DBMS_STATS.EXPORT_DATABASE_STATS and DBMS_STATS.IMPORT_DATABASE_STATS.

Tables in which statistics are stored can be created or deleted through DBMS_STATS.CREATE_STAT_TABLE and DBMS_STATS.DROP_STAT_TABLE.

1. Export and import of sample schema level statistics

For example, I export and import all the statistical information under the JINGYU schema:

-- Source-side statistics export: begin DBMS_STATS.CREATE_STAT_TABLE ('SYSTEM','JINGYU_STATS_20181217'); DBMS_STATS.EXPORT_SCHEMA_STATS (OWNNAME = >' JINGYU', STATTAB = > 'JINGYU_STATS_20181217', STATOWN = >' SYSTEM'); end / expdp\'/ as sysdba\ 'directory=xtts dumpfile=stats_jingyu.dmp logfile=stats_jingyu.log tables=system.jingyu_stats_20181217-- destination side statistics import: impdp\' / as sysdba\ 'directory=xtts dumpfile=stats_jingyu.dmp logfile=stats_jingyu.log cluster=nexec DBMS_STATS.IMPORT_SCHEMA_STATS (OWNNAME = >' JINGYU', STATTAB = > 'JINGYU_STATS_20181217', STATOWN = >' SYSTEM') -- delete the table where the statistics are stored (optional according to actual needs): exec DBMS_STATS.DROP_STAT_TABLE ('SYSTEM','JINGYU_STATS_20181217')

two。 Export and import of sample database level statistics

If you want to export and import all the statistics of the database, the method is very similar, using the corresponding process:

-- Source-side statistics export: begin DBMS_STATS.CREATE_STAT_TABLE ('SYSTEM','DB_STATS_20181217'); DBMS_STATS.EXPORT_DATABASE_STATS (STATTAB = >' DB_STATS_20181217', STATOWN = > 'SYSTEM'); end / expdp\'/ as sysdba\ 'directory=xtts dumpfile=stats.dmp logfile=stats.log tables=system.db_stats_20181217-- destination statistics import: impdp\' / as sysdba\ 'directory=xtts dumpfile=stats.dmp logfile=stats.log cluster=nexec DBMS_STATS.IMPORT_DATABASE_STATS (STATTAB = >' DB_STATS_20181217', STATOWN = > 'SYSTEM');-- Delete the table where the statistics are stored (optional according to actual needs): exec DBMS_STATS.DROP_STAT_TABLE (' SYSTEM','DB_STATS_20181217')

3. Verify the effect of statistics export and import

Take the export and import of database-level statistics as an example to verify the actual effect:

At present, the number of statistics recorded in the following tables of database JINGYU users:

SYS@orcl > select owner, table_name, NUM_ROWS from dba_tables where owner = 'JINGYU';OWNER TABLE_NAME NUM_ROWS-- JINGYU TEST 100708JINGYU ASH_TMP

At this point, follow the previous steps to export the statistics of the database, and the steps will not be repeated.

Then insert data into a table and re-collect the statistics for that table:

SYS@orcl > insert into jingyu.ash_tmp select * from jingyu.ash_tmp;SYS@orcl > commit;SYS@orcl > exec dbms_stats.gather_table_stats ('JINGYU','ASH_TMP'); PL/SQL procedure successfully completed.

Then query the number of rows in the table recorded by the statistics:

SYS@orcl > select owner, table_name, num_rows from dba_tables where owner='JINGYU';OWNER TABLE_NAME NUM_ROWS-- JINGYU TEST 100708JINGYU ASH_TMP 452

At this point, follow the previous steps to import the statistics of the database, and the steps will not be repeated.

Then query the number of rows in the table recorded by the statistics, and it has been restored to the export time at that time:

SYS@orcl > select owner, table_name, num_rows from dba_tables where owner='JINGYU';OWNER TABLE_NAME NUM_ROWS-- JINGYU TEST 100708JINGYU ASH_TMP 226SYS@orcl >

In addition, it should be noted that if the target environment in which statistics are imported, the database version is higher than the source environment (mostly in the database upgrade scenario), the following errors will be encountered when importing statistics:

ERROR at line 1:

ORA-20002: Version of statistics table SYSTEM.DB_STATS_20181217 is too old.

Please try upgrading it with dbms_stats.upgrade_stat_table

ORA-06512: at "SYS.DBMS_STATS", line 11648

ORA-06512: at "SYS.DBMS_STATS", line 11665

ORA-06512: at "SYS.DBMS_STATS", line 12800

ORA-06512: at line 1

At this point, just follow the prompts to execute dbms_stats.upgrade_stat_table:

Exec dbms_stats.upgrade_stat_table ('SYSTEM','db_stats_20181217'); this is the answer to the question about how to export and import statistics in Oracle. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.