statistics 统计信息的导入导出 跨库传输

------data_only---- 没有统计信息----If Data Pump Import (impdp) is done using the parameter:contentdata_onlywill statistics get imported?SolutionNo.This is verified by the following simple test using a Data Pump Export (expdp) dump file created of the sample SCOTT schema via this syntax:SQL host expdp system/PASSWORD directorydp_dir logfileexpdp.log dumpfiledp.dmp schemasscott2 Data Pump Import (impdp) commands were run to verify the outcome.a. Without: contentdata_onlySQL host impdp system/PASSWORD directorydp_dir logfileimpdp.log dumpfiledp.dmp remap_schemascott:testuserIn this case, you can see that statistics were imported:Import: Release 12.1.0.2.0 - Production on Mon Oct 1 11:37:48 2018Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsMaster table SYSTEM.SYS_IMPORT_FULL_01 successfully loaded/unloadedStarting SYSTEM.SYS_IMPORT_FULL_01: system/******** directorydp_dir logfileimpdp.log dumpfiledp.dmp remap_schemascott:testuser...Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS...Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS...Job SYSTEM.SYS_IMPORT_FULL_01 completed with 1 error(s) at Mon Oct 1 11:38:06 2018 elapsed 0 00:00:17-AND-b. With: contentdata_onlySQL host impdp system/PASSWORD directorydp_dir logfileimpdp2.log dumpfiledp.dmp remap_schemascott:testuser contentdata_onlyIn this case, you can see that statistics were not imported. Only table data:Import: Release 12.1.0.2.0 - Production on Mon Oct 1 11:43:51 2018Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit ProductionWith the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsMaster table SYSTEM.SYS_IMPORT_FULL_01 successfully loaded/unloadedStarting SYSTEM.SYS_IMPORT_FULL_01: system/******** directorydp_dir logfileimpdp2.log dumpfiledp.dmp remap_schemascott:testuser contentdata_onlyProcessing object type SCHEMA_EXPORT/TABLE/TABLE_DATA...Job SYSTEM.SYS_IMPORT_FULL_01 completed with 1 error(s) at Mon Oct 1 11:43:54 2018 elapsed 0 00:00:03--------------------------SummaryThe purpose of this document is to explain how to copy statistics among different schemas, tables, indexes, partition and columns by DBMS_STATS package.It can be used to copy statistics from one table or index to another one with the similar data volumn and distribution to avoid unnecessary statistics gathering.SolutionCreate table to hold statistics.SQL EXEC DBMS_STATS.CREATE_STAT_TABLE(ownname SCHEMA1, stattab STATS_TABLE);PL/SQL procedure successfully completed.Export source statistics by DBMS_STATS.EXPORT_XXXX_STATS.SQL EXEC DBMS_STATS.EXPORT_SCHEMA_STATS(ownname SCHEMA1, stattab STATS_TABLE);PL/SQL procedure successfully completed.SQL COL C5 FOR A10COL C1 FOR A20COL C2 FOR A10COL C3 FOR A2COL C4 FOR A5COL R1 FOR A5COL R2 FOR A5COL R3 FOR A5COL STATID FOR A6COL CH1 FOR A5COL CL1 FOR A5COL BL1 FOR A5SQL SQL SQL SQL SQL SQL SQL SQL SQL SQL SQLSQLSQL SELECT * FROM SCHEMA1.STATS_TABLE;STATID T VERSION FLAGS C1 C2 C3 C4 C5 C6 N1 N2 N3 N4 N5 N6 N7 N8 N9 N10 N11 N12 N13 D1 T1 R1 R2 R3 CH1 CL1 BL1------ - ---------- ---------- -------------------- ---------- -- ----- ---------- ----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---- ------ ---------- ---------- ---------- --------- ----- ----- ----- ----- ----- ----- -----T 8 2 TABLE_NAME1 PART1 SCHEMA1 1000 124 12 1000 0 1 14-AUG-20T 8 2 TABLE_NAME1 PART2 SCHEMA1 1000 1006 12 1000 0 2 14-AUG-20T 8 2 TABLE_NAME1 PART3 SCHEMA1 1000 1006 12 1000 0 3 14-AUG-20T 8 2 TABLE_NAME1 SCHEMA1 3000 2136 12 3000 0 1 4-AUG-20I 8 2 INDEX_NAME1 PART1 SCHEMA1 1000 3 1000 1 1 3 1 1000 1 14-AUG-20I 8 2 INDEX_NAME1 PART2 SCHEMA1 1000 3 1000 1 1 3 1 1000 2 14-AUG-20I 8 2 INDEX_NAME1 PART3 SCHEMA1 1000 3 1000 1 1 3 1 1000 3 14-AUG-20... ...59 rows selected.Export the table holding the statisticsSQL !expdp SCHEMA1/PASSWORD dumpfileSTATS_TABLE.dmp tablesSCHEMA1.STATS_TABLEExport: Release 19.0.0.0.0 - Production on Fri Aug 14 02:50:24 2020Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionStarting SCHEMA1.SYS_EXPORT_TABLE_01: SCHEMA1/******** dumpfileSTATS_TABLE.dmp tablesSCHEMA1.STATS_TABLEProcessing object type TABLE_EXPORT/TABLE/TABLE_DATAProcessing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSProcessing object type TABLE_EXPORT/TABLE/STATISTICS/MARKERProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEX. . exported SCHEMA1.STATS_TABLE 23.78 KB 59 rowsMaster table SCHEMA1.SYS_EXPORT_TABLE_01 successfully loaded/unloaded******************************************************************************Dump file set for SCHEMA1.SYS_EXPORT_TABLE_01 is:/refresh/home/app/oracle/admin/orcl/dpdump/STATS_TABLE.dmpJob SCHEMA1.SYS_EXPORT_TABLE_01 successfully completed at Fri Aug 14 02:51:25 2020 elapsed 0 00:00:55Import the table holding statistics into the target schema of target environment.SQL !impdp SCHEMA2/PASSWORD dumpfileSTATS_TABLE.dmp remap_schemaSCHEMA1:SCHEMA2Import: Release 19.0.0.0.0 - Production on Fri Aug 14 02:51:31 2020Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionMaster table SCHEMA2.SYS_IMPORT_FULL_01 successfully loaded/unloadedStarting SCHEMA2.SYS_IMPORT_FULL_01: SCHEMA2/******** dumpfileSTATS_TABLE.dmp remap_schemaSCHEMA1:SCHEMA2Processing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/TABLE_DATA. . imported SCHEMA2.STATS_TABLE 23.78 KB 59 rowsProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSProcessing object type TABLE_EXPORT/TABLE/STATISTICS/MARKERJob SCHEMA2.SYS_IMPORT_FULL_01 successfully completed at Fri Aug 14 02:52:06 2020 elapsed 0 00:00:32Update source schema, table, index, column, partition name to target schema, table, index, column, partition name.SQL update SCHEMA1.STATS_TABLE set c1 TABLE_NAME2,C5SCHEMA2where c1 PARTTESTAND C5SCHEMA1and TYPE in (T,C); 2 3 454 rows updated.SQL update SCHEMA1.STATS_TABLE set c1 INDEX_NAME2,C5SCHEMA2where c1 INDEX_NAME1AND C5SCHEMA1and TYPE in (I); 2 3 44 rows updated.SQL update SCHEMA1.STATS_TABLE set c4 NCOLUMN1where c1 TABLE_NAME2AND C4COLUMN1and TYPE in (C); 2 3 46 rows updated.SQLSQL update SCHEMA1.STATS_TABLE set c2 NPART1where c1 TABLE_NAME2AND C2PART1and TYPE in (T,C); 2 3 413 rows updated.SQLSQL COMMIT;Commit complete.Import the statstics to dictionary.SQL CONN SCHEMA2/PASSWORDConnected.SQL EXEC DBMS_STATS.IMPORT_SCHEMA_STATS(ownname SCHEMA2, stattab STATS_TABLE);PL/SQL procedure successfully completed.Confirm the statistics from dictionary views.SQL SELECT * FROM DBA_TAB_COL_STATISTICS WHERE TABLE_NAME LIKE TABLENAME% AND COLUMN_NAME LIKE COLUMN1%;OWNER TABLE_NAME COLUMN_NAM NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS NUM_BUCKETS LAST_ANAL SAMPLE_SIZE GLO USE NOTES AVG_COL_LEN HISTOGRAM SCOPE---------- -------------- ---------- ------------ ---------- ---------- ---------- ---------- ----------- --------- ----------- --- --- ----- ----------- --------------- -------SCHEMA1 TABLE_NAME1 COLUMN1 3 C102 C104 .000166667 0 3 14-AUG-20 3000 YES NO 3 FREQUENCY SHAREDSCHEMA2 TABLE_NAME2 NCOLUMN1 3 C102 C104 .000166667 0 3 14-AUG-20 3000 YES NO 3 FREQUENCY SHAREDSQLSQL SELECT * FROM DBA_TAB_HISTOGRAMS WHERE TABLE_NAME LIKE TABLENAME% ORDER BY 2,1,3,4;OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A ENDPOINT_A ENDPOINT_REPEAT_COUNT SCOPE---------- -------------- ---------- --------------- -------------- ---------- ---------- --------------------- -------SCHEMA1 TABLE_NAME1 COLUMN1 1000 1 1 C102 0 SHAREDSCHEMA1 TABLE_NAME1 COLUMN1 2000 2 2 C103 0 SHAREDSCHEMA1 TABLE_NAME1 COLUMN1 3000 3 3 C104 0 SHAREDSCHEMA1 TABLE_NAME1 COLUMN2 3 1 1 C102 3 SHAREDSCHEMA1 TABLE_NAME1 COLUMN2 336 112 112 C2020D 3 SHAREDSCHEMA1 TABLE_NAME1 COLUMN2 669 223 223 C20318 3 SHAREDSCHEMA1 TABLE_NAME1 COLUMN2 1002 334 334 C20423 3 SHAREDSCHEMA1 TABLE_NAME1 COLUMN2 1335 445 445 C2052E 3 SHAREDSCHEMA1 TABLE_NAME1 COLUMN2 1668 556 556 C20639 3 SHAREDSCHEMA1 TABLE_NAME1 COLUMN2 2001 667 667 C20744 3 SHAREDSCHEMA1 TABLE_NAME1 COLUMN2 2334 778 778 C2084F 3 SHAREDSCHEMA1 TABLE_NAME1 COLUMN2 2667 889 889 C2095A 3 SHAREDSCHEMA1 TABLE_NAME1 COLUMN2 3000 1000 1000 C20B 3 SHAREDSCHEMA1 TABLE_NAME1 COLUMN3 3000 4.3756E35 TEST 54455354 0 SHAREDSCHEMA2 TABLE_NAME2 NCOLUMN1 1000 1 1 C102 0 SHAREDSCHEMA2 TABLE_NAME2 NCOLUMN1 2000 2 2 C103 0 SHAREDSCHEMA2 TABLE_NAME2 NCOLUMN1 3000 3 3 C104 0 SHAREDSCHEMA2 TABLE_NAME2 COLUMN2 3 1 1 C102 3 SHAREDSCHEMA2 TABLE_NAME2 COLUMN2 336 112 112 C2020D 3 SHAREDSCHEMA2 TABLE_NAME2 COLUMN2 669 223 223 C20318 3 SHAREDSCHEMA2 TABLE_NAME2 COLUMN2 1002 334 334 C20423 3 SHAREDSCHEMA2 TABLE_NAME2 COLUMN2 1335 445 445 C2052E 3 SHAREDSCHEMA2 TABLE_NAME2 COLUMN2 1668 556 556 C20639 3 SHAREDSCHEMA2 TABLE_NAME2 COLUMN2 2001 667 667 C20744 3 SHAREDSCHEMA2 TABLE_NAME2 COLUMN2 2334 778 778 C2084F 3 SHAREDSCHEMA2 TABLE_NAME2 COLUMN2 2667 889 889 C2095A 3 SHAREDSCHEMA2 TABLE_NAME2 COLUMN2 3000 1000 1000 C20B 3 SHAREDSCHEMA2 TABLE_NAME2 COLUMN3 3000 4.3756E35 TEST 54455354 0 SHARED28 rows selected.SQLSQL col PARTITION_NAME for a10SQL select OWNER,TABLE_NAME,PARTITION_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,SAMPLE_SIZE,LAST_ANALYZED,GLOBAL_STATS,USER_STATS,SCOPEfrom DBA_TAB_STATISTICS where TABLE_NAME LIKE PARTTEST%;2OWNER TABLE_NAME PARTITION_ NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE SAMPLE_SIZE LAST_ANAL GLO USE SCOPE---------- -------------- ---------- ---------- ---------- ------------ ---------- ----------- --------- --- --- -------SCHEMA1 TABLE_NAME1 3000 2136 0 0 3000 14-AUG-20 YES NO SHAREDSCHEMA2 TABLE_NAME2 3000 2136 0 0 3000 14-AUG-20 YES NO SHAREDSCHEMA1 TABLE_NAME1 PART2 1000 1006 0 0 1000 14-AUG-20 YES NO SHAREDSCHEMA2 TABLE_NAME2 PART2 1000 1006 0 0 1000 14-AUG-20 YES NO SHAREDSCHEMA1 TABLE_NAME1 PART3 1000 1006 0 0 1000 14-AUG-20 YES NO SHAREDSCHEMA2 TABLE_NAME2 NPART1 1000 124 0 0 1000 14-AUG-20 YES NO SHAREDSCHEMA1 TABLE_NAME1 PART1 1000 124 0 0 1000 14-AUG-20 YES NO SHAREDSCHEMA2 TABLE_NAME2 PART3 1000 1006 0 0 1000 14-AUG-20 YES NO SHARED8 rows selected.SQLSQL select OWNER,TABLE_NAME,INDEX_NAME,PARTITION_NAME,BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,AVG_DATA_BLOCKS_PER_KEY,CLUSTERING_FACTOR,NUM_ROWS,SAMPLE_SIZE,LAST_ANALYZED,GLOBAL_STATS,USER_STATS,SCOPEfrom DBA_IND_STATISTICS where TABLE_NAME LIKE PARTTEST%; 2OWNER TABLE_NAME INDEX_NAME PARTITION_ BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR NUM_ROWS SAMPLE_SIZE---------- -------------- ------------------ ---------- ---------- ----------- ------------- ----------------------- ----------------------- ----------------- ---------- -----------LAST_ANAL GLO USE SCOPE--------- --- --- -------SCHEMA1 TABLE_NAME1 INDEX_NAME1 1 9 3000 1 1 9 3000 300014-AUG-20 YES NO SHAREDSCHEMA2 TABLE_NAME2 INDEX_NAME2 1 9 3000 1 1 9 3000 300014-AUG-20 YES NO SHAREDSCHEMA1 TABLE_NAME1 INDEX_NAME1 PART1 1 3 1000 1 1 3 1000 100014-AUG-20 YES NO SHAREDSCHEMA1 TABLE_NAME1 INDEX_NAME1 PART2 1 3 1000 1 1 3 1000 100014-AUG-20 YES NO SHAREDSCHEMA1 TABLE_NAME1 INDEX_NAME1 PART3 1 3 1000 1 1 3 1000 100014-AUG-20 YES NO SHAREDSCHEMA2 TABLE_NAME2 INDEX_NAME2 NPART1 1 3 1000 1 1 3 1000 100014-AUG-20 YES NO SHAREDSCHEMA2 TABLE_NAME2 INDEX_NAME2 PART2 1 3 1000 1 1 3 1000 100014-AUG-20 YES NO SHAREDSCHEMA2 TABLE_NAME2 INDEX_NAME2 PART3 1 3 1000 1 1 3 1000 100014-AUG-20 YES NO SHARED8 rows selected.SQLCopy statistics by DBMS_STATS.GET_XXX_STATS and DBMS_STATS.SET_XXX_STATS when both source and target in the same databaseIf both the source table and target table are in the same database, it is better to use DBMS_STATS.GET_XXX_STATS and DBMS_STATS.SET_XXX_STATS to copy statistics between different schemas, tables, indexes and columns. For example copy the column statistics and histogram from COL_NAME of USER1.TABLE_NAME1 to COL_NAME of USER2.TABLE_NAME2.SQL DECLAREva_srec DBMS_STATS.STATREC;va_distinct NUMBER;va_density NUMBER;va_nullcnt NUMBER;va_avglen NUMBER;va_numvals DBMS_STATS.NUMARRAY;BEGINDBMS_STATS.GET_COLUMN_STATS(USER1,TABLE_NAME1,COL_NAME,NULL,NULL,NULL,va_distinct,va_density,va_nullcnt,va_srec,va_avglen);DBMS_STATS.SET_COLUMN_STATS(USER2,TABLE_NAME2,COL_NAME,NULL,NULL,NULL,va_distinct,2 3 4 5 6 7 8 9 10 11 12 va_density,va_nullcnt,va_srec,va_avglen);commit;END;/13 14 15PL/SQL procedure successfully completed.SQLSQL col LOW_VALUE for a10col high_VALUE for a10SELECT * FROM DBA_TAB_COL_STATISTICS WHERE TABLE_NAME LIKE PARTTEST% AND COLUMN_NAMEQID;SQL SQLOWNER TABLE_NAME COLUMN_NAM NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS NUM_BUCKETS LAST_ANAL SAMPLE_SIZE GLO USE NOTES AVG_COL_LEN HISTOGRAM SCOPE-------- --------------- ---------- ------------ ---------- ---------- ---------- ---------- ----------- --------- ----------- --- --- --------------------------------------------------------------------------------------------------- ----------- --------------- -------USER1 TABLE_NAME1 COL_NAME 3 C102 C104 .000166667 0 3 18-AUG-20 3000 YES NO 3 FREQUENCY SHAREDUSER2 TABLE_NAME2 COL_NAME 3 C102 C104 .000166667 0 3 18-AUG-20 YES YES 3 FREQUENCY SHAREDSQL col ENDPOINT_ACTUAL_VALUE for a10col ENDPOINT_ACTUAL_VALUE_RAW for a10SELECT * FROM DBA_TAB_HISTOGRAMS WHERE TABLE_NAME LIKE PARTTEST% and column_name like QID ORDER BY 2,1,3,4;SQL SQLOWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A ENDPOINT_A ENDPOINT_REPEAT_COUNT SCOPE-------- --------------- ---------- --------------- -------------- ---------- ---------- --------------------- -------USER1 TABLE_NAME1 COL_NAME 1000 1 1 C102 0 SHAREDUSER1 TABLE_NAME1 COL_NAME 2000 2 2 C103 0 SHAREDUSER1 TABLE_NAME1 COL_NAME 3000 3 3 C104 0 SHAREDUSER2 TABLE_NAME2 COL_NAME 1000 1 1 C102 0 SHAREDUSER2 TABLE_NAME2 COL_NAME 2000 2 2 C103 0 SHAREDUSER2 TABLE_NAME2 COL_NAME 3000 3 3 C104 0 SHARED6 rows selected.SQLSummaryThis document 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.To provide Oracle Support Services with statistics for debugging, please refer to the following document:KB82707 Transferring Optimizer Statistics to SupportSolution1. Create the STATS tableSQL connect user/pwdSQL EXEC DBMS_STATS.CREATE_STAT_TABLE(ownname user,stattabSTATS);2. Export the statistics to the STATS tableRetrieving 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 statisticsExporting 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)exp system/manager tablesSTATSfilestats.dmp owneruserlogstats.log4. Transfer the dump file to the destination databaseIf 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 databaseimp system/manager tablesSTATS filestats.dmp fullY6. Import the statistics into the data dictionaryThe statistics can be imported using IMPORT_XXXX_STATS procedure:Importing the statistics for all the objects in the databaseEXEC 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 tablesEXEC DBMS_STATS.IMPORT_TABLE_STATS(ownname user,tabname TAB1,stattab STATS);7. Changing the schema nameStatistics 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:update STATS set c5 target schemawhere c5 Source schemaand statid Stat Id used while exporting these stats;8. Upgrading statistics tableWhen 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 thisNOTE: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. expdp impdp 会自动导入统计信息但是前提的不能append/truncate只能replaceImporting 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.If you wish to save your statistics of schema or table, which you can use later during any query issue Or if you wish copy the statistics from production database to development , then this method will be helpful.Here i will take export of statistics of a table RAJ.TEST from PROD and import into TESTDEMO:create a table to store the stats:--- RAJ is the owner of the stats table, STAT_TEST - name of the stats table PROD exec DBMS_STATS.CREATE_STAT_TABLE(RAJ,STAT_TEST,SYSAUX); PL/SQL procedure successfully completed. SQL ; 1* select owner,table_name from dba_tables where table_nameSTAT_TEST SQL / OWNER TABLE_NAME ------------ ------------ RAJ STAT_TEST SQLNow export the statistics of the table RAJ.TEST to stats table ( STAT_TEST)PROD exec dbms_stats.export_table_stats(ownnameRAJ, tabnameTEST, stattabSTAT_TEST, cascadetrue); PL/SQL procedure successfully completed.ow take expdp of this stats table(STAT_TEST)[oraclePROD]$ expdp dumpfilestats.dmp logfilestats.log tablesraj.STAT_TEST directoryDIR1 Export: Release 12.1.0.2.0 - Production on Fri Aug 28 10:09:26 2015 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Username: raj/rajorcl Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Starting RAJ.SYS_EXPORT_TABLE_01: raj/********orcl dumpfilestats.dmp logfilestats.log tablesraj.STAT_TEST directoryDIR1 Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 192 KB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER . . exported RAJ.STAT_TEST 19.24 KB 19 rows Master table RAJ.SYS_EXPORT_TABLE_01 successfully loaded/unloaded ****************************************************************************** Dump file set for RAJ.SYS_EXPORT_TABLE_01 is: /home/oracle/DIR1/stats.dmp Job RAJ.SYS_EXPORT_TABLE_01 successfully completed at Fri Aug 28 10:12:33 2015 elapsed 0 00:03:00Move the dump file from PROD to TEST machine and import the same[oracleTEST]$ impdp dumpfilestats.dmp logfilestats.log tablesraj.STAT_TEST directoryDIR1 table_exists_actionREPLACE Import: Release 12.1.0.2.0 - Production on Fri Aug 28 10:12:42 2015 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Username: raj/rajorcl Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Master table RAJ.SYS_IMPORT_TABLE_01 successfully loaded/unloaded Starting RAJ.SYS_IMPORT_TABLE_01: raj/********orcl dumpfilestats.dmp logfilestats.log tablesraj.STAT_TEST directoryDIR1 table_exists_actionREPLACE Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported RAJ.STAT_TEST 19.24 KB 19 rows Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Job RAJ.SYS_IMPORT_TABLE_01 successfully completed at Fri Aug 28 10:14:36 2015 elapsed 0 00:01:42Now import the statistics in database:SQL set lines 200 SQL set pagesize 200 SQL col table_name for a12 SQL col owner for a12 SQL select owner,table_name,last_analyzed from dba_tables where table_nameTEST; OWNER TABLE_NAME LAST_ANAL ------------ ------------ --------- RAJ TEST 05-AUG-15 SQL exec dbms_stats.import_table_stats(ownnameRAJ, tabnameTEST, stattabSTAT_TEST, cascadetrue); PL/SQL procedure successfully completed. SQL select owner,table_name,last_analyzed from dba_tables where table_nameTEST; OWNER TABLE_NAME LAST_ANAL ------------ ------------ --------- RAJ TEST 28-AUG-15We can observe after import_table_stats, last_analyzed date has been updated.We can do export/import statistics at schema level and database level:For database :----For export exec dbms_stats.EXPORT_DATABASE_STATS(,,); --- For import exec dbms_stats.IMPORT_DATABASE_STATS(,,);For schema:--- For export exec dbms_stats.export_schema_stats( ownname, stattab, statid ); --- For import exec dbms_stats.import_schema_stats( ownname, stattab, statid );statisticsBACKUP RECOVERYPERFORMANCE TUNINGHow to Transfer Statistics from one Database to Another (Doc ID 333742.1)Reply Hi,Nice article .Thanks.Is it possible to export schema level statistics and import only some of the schema table’s statistics from this exported statistics ?like ,exec dbms_stats.export_schema_stats( ownname, stattab”, statid” );exec dbms_stats.import_table_stats(ownname’RAJ’, tabname’TEST’, stattab’STAT_TEST’, cascadetrue);ReplyImporting the statistics to tables------导出可以schema级别导入可以table 级别。EXEC DBMS_STATS.IMPORT_TABLE_STATS(ownname user,tabname TAB1,stattab STATS);