Transfer Statistics from one Database to Another
来源:互联网 发布:java aop原理 编辑:程序博客网 时间:2024/04/30 01:37
GOAL
This article describes the steps required to transfer the table/index statistics from one database to another.
Discussion will cover how to accomplish this via table level, schema level, and database level.
SOLUTION
1. Create the STATS table
SQL> EXEC DBMS_STATS.CREATE_STAT_TABLE(ownname=> 'user',stattab=>'STATS');
2. Export the statistics to the STATS table
Retrieving the statistics can be achieved using EXPORT_XXXX_STATS procedure:
- Exporting the statistics of all the objects in the databaseEXEC DBMS_STATS.EXPORT_DATABASE_STATS(stattab => 'STATS');
Make sure you have logged in as SYS or SYSTEM to export database level statistics - Exporting the statistics of all the objects in a schemaEXEC DBMS_STATS.EXPORT_SCHEMA_STATS(ownname => 'user', stattab => 'STATS');
You need to have DBA privileges to export the statistics of other schemas. - Exporting the statistics of tablesEXEC DBMS_STATS.EXPORT_TABLE_STATS(<'username' or NULL>,'TAB1',NULL,'STATS');
3. Export the STATS table using export(exp) or datapump(expdp)
4. Transfer the dump file to the destination database
If you are using ftp to transfer the files make sure that you are transferring the file with binary option to avoid the dump file being corrupted.
5. Import the STATS table to the destination database
6. Import the statistics into the data dictionary
The statistics can be imported using IMPORT_XXXX_STATS procedure:
- Importing the statistics for all the objects in the database
EXEC DBMS_STATS.IMPORT_DATABASE_STATS(stattab => 'STATS'); - Importing the statistics to all the objects in a schemaEXEC DBMS_STATS.IMPORT_SCHEMA_STATS(ownname => user, stattab => 'STATS');
- Importing the statistics to tables
EXEC DBMS_STATS.IMPORT_TABLE_STATS(ownname => user, tabname => 'TAB1', stattab => 'STATS');
7. Changing the schema name
Statistics cannot be directly exported from one schema and imported into a different schema.The schema names much match exactly.
If the target database schema is different from the source database schema, the schema name can be changed by updating column C5 of the STATS table:
where c5 = '<Source schema>'
and statid = <Stat Id used while exporting these stats>;
8. Upgrading statistics table
When importing statistics from an earlier version into a later version of Oracle, the structure of the statistics table may have changed.
Try executing DBMS_STATS.UPGRADE_STAT_TABLE to resolve this
Note:
When exporting and importing database objects, Datapump Export and Import utilities will also export and import optimizer statistics along with the tables even when a column has system-generated names.
Importing statistics will overwrite any previous statistics that existed for the table having statistics imported. For example, if previously there were histogram statistics and you import statistics that do not include histograms, there will no longer be any histogram information.
- Transfer Statistics from one Database to Another
- Move all database objects from one tablespace to another
- How Can One Transfer Xcode 4 Code Snippets from One Machine to Another
- How to move RAC Database ORACLE_HOME from one location to another one (Doc ID 1438719.1)
- How to move ASM database files from one diskgroup to another
- How to move ASM database files from one diskgroup to another
- How to Export and Import the AWR Repository From One Database to Another (文档 ID 785730.1)
- How can we use transfer function to skip search page while going from one component to another?
- Transferring Data from One Table to Another
- Migrating Queries from One User To Another
- copy from one array to another
- Link - Windows Azure - Transfer your account to another one
- Transfer postgres database to another server - backup and restore
- transfer to another team
- BAPI to Copy Materials from one Plant to Another
- BAPI to Copy Materials from one Plant to Another
- BAPI to Copy Materials from one Plant to Another
- BW--ABAP to copy aggregates from one cube to another
- Mediastream2 用法介绍及简明实例分析
- uva11582 Colossal Fibonacci Numbers(分治法)
- 在未提供官方驱动的Windows平板上安装Win10且完美驱动的解决方案
- leetcode 108 —— Convert Sorted Array to Binary Search Trees
- js事件冒泡和事件捕获
- Transfer Statistics from one Database to Another
- IOS版本-两个小球不停的绕中心旋转的进度条
- NYOJ--1A+B Problem(练手题)
- 华为手机logcat不出日志解决方案
- Tomcat组件的生命周期Lifecycle
- 进制转换
- osgi4——启动一个servlet
- Java 循环
- 找出数组中前K小的值&最小堆