SQL> SQL> PROMPT STATS_6C STATS_6C SQL> SQL> ALTER TABLE TEST_TAB1 2 ADD PARTITION P_20100211 VALUES LESS THAN (20100212); Table altered. SQL> SQL> TRUNCATE TABLE LOAD_TAB1; Table truncated. SQL> SQL> INSERT INTO LOAD_TAB1 VALUES (20100211, 'GROT', 1000, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100211, 'GROT', 30000, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100211, 'GROT', 2000, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100211, 'GROT', 10000, 'N'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100211, 'GROT', 2400, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100211, 'GROT', 500, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100211, 'GROT', 1200, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100211, 'GROT', 400, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100211, 'GROT', 600, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100211, 'GROT', 700, 'Z'); 1 row created. SQL> SQL> COMMIT; Commit complete. SQL> SQL> ALTER TABLE test_tab1 EXCHANGE SUBPARTITION P_20100211_GROT WITH TABLE load_tab1; Table altered. SQL> ALTER TABLE test_tab1 MODIFY SUBPARTITION P_20100211_GROT REBUILD UNUSABLE LOCAL INDEXES; Table altered. SQL> SQL> -- set MSA to default value SQL> alter session set "_minimal_stats_aggregation"=TRUE; Session altered. SQL> exec dbms_monitor.session_trace_enable; PL/SQL procedure successfully completed. SQL> SQL> exec dbms_stats.copy_table_stats(ownname => 'TESTUSER', tabname => 'TEST_TAB1', srcpartname => 'P_20100209_GROT', dstpartname => 'P_20100211_GROT'); PL/SQL procedure successfully completed. SQL> SQL> exec dbms_stats.copy_table_stats(ownname => 'TESTUSER', tabname => 'TEST_TAB1', srcpartname => 'P_20100209_JUNE', dstpartname => 'P_20100211_JUNE'); PL/SQL procedure successfully completed. SQL> SQL> exec dbms_stats.copy_table_stats(ownname => 'TESTUSER', tabname => 'TEST_TAB1', srcpartname => 'P_20100209_HALO', dstpartname => 'P_20100211_HALO'); PL/SQL procedure successfully completed. SQL> SQL> -- Regardless of _minimal_stats_aggregation setting, this next line will fail as Oracle tries to aggregate stats up to partition and table levels SQL> SQL> alter session set "_minimal_stats_aggregation"=TRUE; Session altered. SQL> SQL> exec dbms_stats.copy_table_stats(ownname => 'TESTUSER', tabname => 'TEST_TAB1', srcpartname => 'P_20100209_OTHERS', dstpartname => 'P_20100211_OTHERS'); SQL> SQL> show parameter user_dump_dest ERROR: ORA-03114: not connected to ORACLE SQL> SQL> -- Reconnect and try with different MSA setting SQL> SQL> connect testuser/testuser Connected. SQL> SQL> alter session set "_minimal_stats_aggregation"=FALSE; Session altered. SQL> SQL> exec dbms_stats.copy_table_stats(ownname => 'TESTUSER', tabname => 'TEST_TAB1', srcpartname => 'P_20100209_OTHERS', dstpartname => 'P_20100211_OTHERS'); SQL> SQL> show parameter user_dump_dest ERROR: ORA-03114: not connected to ORACLE SQL> SQL> -- Try copying stats at partition level rather than subpartition and hopefully subpartition stats SQL> -- will be copied too. SQL> SQL> connect testuser/testuser Connected. SQL> set pages 9999 SQL> set lines 132 SQL> set echo on SQL> SQL> COLUMN column_name format a26 SQL> COLUMN low_val format a20 SQL> COLUMN high_val format a20 SQL> COLUMN data_type format a20 SQL> SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS'; Session altered. SQL> SQL> exec dbms_stats.copy_table_stats(ownname => 'TESTUSER', tabname => 'TEST_TAB1', srcpartname => 'P_20100209', dstpartname => 'P_20100211'); PL/SQL procedure successfully completed. SQL> SQL> select table_name, global_stats, last_analyzed, num_rows 2 from dba_tables 3 where table_name='TEST_TAB1' 4 and owner='TESTUSER' 5 order by 1, 2, 4 desc nulls last; TABLE_NAME GLO LAST_ANALYZED NUM_ROWS ------------------------------ --- -------------------- ---------- TEST_TAB1 NO SQL> SQL> select table_name, partition_name, global_stats, last_analyzed, num_rows 2 from dba_tab_partitions 3 where table_name='TEST_TAB1' 4 and table_owner='TESTUSER' 5 order by 1, 2, 4 desc nulls last; TABLE_NAME PARTITION_NAME GLO LAST_ANALYZED NUM_ROWS ------------------------------ ------------------------------ --- -------------------- ---------- TEST_TAB1 P_20100207 NO TEST_TAB1 P_20100209 NO 02-MAY-2010 22:24:43 12 TEST_TAB1 P_20100210 NO TEST_TAB1 P_20100211 NO 02-MAY-2010 22:24:43 12 SQL> SQL> select table_name, subpartition_name, global_stats, last_analyzed, num_rows 2 from dba_tab_subpartitions 3 where table_name='TEST_TAB1' 4 and table_owner='TESTUSER' 5 order by 1, 2, 4 desc nulls last; TABLE_NAME SUBPARTITION_NAME GLO LAST_ANALYZED NUM_ROWS ------------------------------ ------------------------------ --- -------------------- ---------- TEST_TAB1 P_20100207_GROT NO TEST_TAB1 P_20100207_HALO NO TEST_TAB1 P_20100207_JUNE NO TEST_TAB1 P_20100207_OTHERS NO TEST_TAB1 P_20100209_GROT YES 02-MAY-2010 22:24:23 3 TEST_TAB1 P_20100209_HALO YES 02-MAY-2010 22:24:24 3 TEST_TAB1 P_20100209_JUNE YES 02-MAY-2010 22:24:23 3 TEST_TAB1 P_20100209_OTHERS YES 02-MAY-2010 22:24:24 3 TEST_TAB1 P_20100210_GROT YES 02-MAY-2010 22:24:22 3 TEST_TAB1 P_20100210_HALO NO TEST_TAB1 P_20100210_JUNE NO TEST_TAB1 P_20100210_OTHERS NO TEST_TAB1 P_20100211_GROT YES 02-MAY-2010 22:24:23 3 TEST_TAB1 P_20100211_HALO YES 02-MAY-2010 22:24:24 3 TEST_TAB1 P_20100211_JUNE YES 02-MAY-2010 22:24:23 3 TEST_TAB1 P_20100211_OTHERS YES 02-MAY-2010 22:24:24 3 16 rows selected. SQL> SQL> PROMPT ************************ ************************ SQL> PROMPT Table-Level Column Stats Table-Level Column Stats SQL> PROMPT ************************ ************************ SQL> SQL> select 2 a.column_name, 3 a.num_distinct, 4 display_raw(a.low_value,b.data_type) as low_val, 5 display_raw(a.high_value,b.data_type) as high_val, 6 b.data_type 7 from 8 dba_tab_col_statistics a, dba_tab_cols b 9 where 10 a.owner='TESTUSER' and 11 a.table_name='TEST_TAB1' and 12 a.table_name=b.table_name and 13 a.column_name=b.column_name 14 order by 1 15 / no rows selected SQL> SQL> PROMPT **************************** **************************** SQL> PROMPT Partition-Level Column Stats Partition-Level Column Stats SQL> PROMPT **************************** **************************** SQL> SQL> select 2 a.column_name, a.partition_name, 3 a.num_distinct, 4 display_raw(a.low_value,b.data_type) as low_val, 5 display_raw(a.high_value,b.data_type) as high_val, 6 b.data_type 7 from 8 dba_part_col_statistics a, dba_tab_cols b 9 where 10 a.owner='TESTUSER' and 11 a.table_name='TEST_TAB1' and 12 a.table_name=b.table_name and 13 a.column_name=b.column_name 14 order by 1, 2 15 / COLUMN_NAME PARTITION_NAME NUM_DISTINCT LOW_VAL HIGH_VAL -------------------------- ------------------------------ ------------ -------------------- -------------------- DATA_TYPE -------------------- REPORTING_DATE P_20100207 NUMBER REPORTING_DATE P_20100207 NUMBER REPORTING_DATE P_20100209 1 20100209 20100209 NUMBER REPORTING_DATE P_20100209 1 20100209 20100209 NUMBER REPORTING_DATE P_20100210 NUMBER REPORTING_DATE P_20100210 NUMBER REPORTING_DATE P_20100211 NUMBER REPORTING_DATE P_20100211 NUMBER SEQ_ID P_20100207 NUMBER SEQ_ID P_20100207 NUMBER SEQ_ID P_20100209 3 400 900 NUMBER SEQ_ID P_20100209 3 400 900 NUMBER SEQ_ID P_20100210 NUMBER SEQ_ID P_20100210 NUMBER SEQ_ID P_20100211 NUMBER SEQ_ID P_20100211 NUMBER SOURCE_SYSTEM P_20100207 VARCHAR2 SOURCE_SYSTEM P_20100207 VARCHAR2 SOURCE_SYSTEM P_20100209 4 GROT ZZZZ VARCHAR2 SOURCE_SYSTEM P_20100209 4 GROT ZZZZ VARCHAR2 SOURCE_SYSTEM P_20100210 VARCHAR2 SOURCE_SYSTEM P_20100210 VARCHAR2 SOURCE_SYSTEM P_20100211 VARCHAR2 SOURCE_SYSTEM P_20100211 VARCHAR2 STATUS P_20100207 VARCHAR2 STATUS P_20100207 VARCHAR2 STATUS P_20100209 3 N Z VARCHAR2 STATUS P_20100209 3 N Z VARCHAR2 STATUS P_20100210 VARCHAR2 STATUS P_20100210 VARCHAR2 STATUS P_20100211 VARCHAR2 STATUS P_20100211 VARCHAR2 32 rows selected. SQL> SQL> PROMPT ******************************* ******************************* SQL> PROMPT Subpartition-Level Column Stats Subpartition-Level Column Stats SQL> PROMPT ******************************* ******************************* SQL> SQL> select 2 a.column_name, a.subpartition_name, 3 a.num_distinct, 4 display_raw(a.low_value,b.data_type) as low_val, 5 display_raw(a.high_value,b.data_type) as high_val, 6 b.data_type 7 from 8 dba_subpart_col_statistics a, dba_tab_cols b 9 where 10 a.owner='TESTUSER' and 11 a.table_name='TEST_TAB1' and 12 a.table_name=b.table_name and 13 a.column_name=b.column_name 14 order by 1, 2 15 / COLUMN_NAME SUBPARTITION_NAME NUM_DISTINCT LOW_VAL HIGH_VAL -------------------------- ------------------------------ ------------ -------------------- -------------------- DATA_TYPE -------------------- REPORTING_DATE P_20100207_GROT NUMBER REPORTING_DATE P_20100207_GROT NUMBER REPORTING_DATE P_20100207_HALO NUMBER REPORTING_DATE P_20100207_HALO NUMBER REPORTING_DATE P_20100207_JUNE NUMBER REPORTING_DATE P_20100207_JUNE NUMBER REPORTING_DATE P_20100207_OTHERS NUMBER REPORTING_DATE P_20100207_OTHERS NUMBER REPORTING_DATE P_20100209_GROT 1 20100209 20100209 NUMBER REPORTING_DATE P_20100209_GROT 1 20100209 20100209 NUMBER REPORTING_DATE P_20100209_HALO 1 20100209 20100209 NUMBER REPORTING_DATE P_20100209_HALO 1 20100209 20100209 NUMBER REPORTING_DATE P_20100209_JUNE 1 20100209 20100209 NUMBER REPORTING_DATE P_20100209_JUNE 1 20100209 20100209 NUMBER REPORTING_DATE P_20100209_OTHERS 1 20100209 20100209 NUMBER REPORTING_DATE P_20100209_OTHERS 1 20100209 20100209 NUMBER REPORTING_DATE P_20100210_GROT 1 20100209 20100209 NUMBER REPORTING_DATE P_20100210_GROT 1 20100209 20100209 NUMBER REPORTING_DATE P_20100210_HALO NUMBER REPORTING_DATE P_20100210_HALO NUMBER REPORTING_DATE P_20100210_JUNE NUMBER REPORTING_DATE P_20100210_JUNE NUMBER REPORTING_DATE P_20100210_OTHERS NUMBER REPORTING_DATE P_20100210_OTHERS NUMBER REPORTING_DATE P_20100211_GROT 1 20100209 20100209 NUMBER REPORTING_DATE P_20100211_GROT 1 20100209 20100209 NUMBER REPORTING_DATE P_20100211_HALO 1 20100209 20100209 NUMBER REPORTING_DATE P_20100211_HALO 1 20100209 20100209 NUMBER REPORTING_DATE P_20100211_JUNE 1 20100209 20100209 NUMBER REPORTING_DATE P_20100211_JUNE 1 20100209 20100209 NUMBER REPORTING_DATE P_20100211_OTHERS NUMBER REPORTING_DATE P_20100211_OTHERS NUMBER SEQ_ID P_20100207_GROT NUMBER SEQ_ID P_20100207_GROT NUMBER SEQ_ID P_20100207_HALO NUMBER SEQ_ID P_20100207_HALO NUMBER SEQ_ID P_20100207_JUNE NUMBER SEQ_ID P_20100207_JUNE NUMBER SEQ_ID P_20100207_OTHERS NUMBER SEQ_ID P_20100207_OTHERS NUMBER SEQ_ID P_20100209_GROT 3 400 900 NUMBER SEQ_ID P_20100209_GROT 3 400 900 NUMBER SEQ_ID P_20100209_HALO 3 400 900 NUMBER SEQ_ID P_20100209_HALO 3 400 900 NUMBER SEQ_ID P_20100209_JUNE 3 400 900 NUMBER SEQ_ID P_20100209_JUNE 3 400 900 NUMBER SEQ_ID P_20100209_OTHERS 3 400 900 NUMBER SEQ_ID P_20100209_OTHERS 3 400 900 NUMBER SEQ_ID P_20100210_GROT 3 400 900 NUMBER SEQ_ID P_20100210_GROT 3 400 900 NUMBER SEQ_ID P_20100210_HALO NUMBER SEQ_ID P_20100210_HALO NUMBER SEQ_ID P_20100210_JUNE NUMBER SEQ_ID P_20100210_JUNE NUMBER SEQ_ID P_20100210_OTHERS NUMBER SEQ_ID P_20100210_OTHERS NUMBER SEQ_ID P_20100211_GROT 3 400 900 NUMBER SEQ_ID P_20100211_GROT 3 400 900 NUMBER SEQ_ID P_20100211_HALO 3 400 900 NUMBER SEQ_ID P_20100211_HALO 3 400 900 NUMBER SEQ_ID P_20100211_JUNE 3 400 900 NUMBER SEQ_ID P_20100211_JUNE 3 400 900 NUMBER SEQ_ID P_20100211_OTHERS NUMBER SEQ_ID P_20100211_OTHERS NUMBER SOURCE_SYSTEM P_20100207_GROT VARCHAR2 SOURCE_SYSTEM P_20100207_GROT VARCHAR2 SOURCE_SYSTEM P_20100207_HALO VARCHAR2 SOURCE_SYSTEM P_20100207_HALO VARCHAR2 SOURCE_SYSTEM P_20100207_JUNE VARCHAR2 SOURCE_SYSTEM P_20100207_JUNE VARCHAR2 SOURCE_SYSTEM P_20100207_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100207_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100209_GROT 1 GROT GROT VARCHAR2 SOURCE_SYSTEM P_20100209_GROT 1 GROT GROT VARCHAR2 SOURCE_SYSTEM P_20100209_HALO 1 HALO HALO VARCHAR2 SOURCE_SYSTEM P_20100209_HALO 1 HALO HALO VARCHAR2 SOURCE_SYSTEM P_20100209_JUNE 1 JUNE JUNE VARCHAR2 SOURCE_SYSTEM P_20100209_JUNE 1 JUNE JUNE VARCHAR2 SOURCE_SYSTEM P_20100209_OTHERS 1 ZZZZ ZZZZ VARCHAR2 SOURCE_SYSTEM P_20100209_OTHERS 1 ZZZZ ZZZZ VARCHAR2 SOURCE_SYSTEM P_20100210_GROT 1 ZZZZ ZZZZ VARCHAR2 SOURCE_SYSTEM P_20100210_GROT 1 ZZZZ ZZZZ VARCHAR2 SOURCE_SYSTEM P_20100210_HALO VARCHAR2 SOURCE_SYSTEM P_20100210_HALO VARCHAR2 SOURCE_SYSTEM P_20100210_JUNE VARCHAR2 SOURCE_SYSTEM P_20100210_JUNE VARCHAR2 SOURCE_SYSTEM P_20100210_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100210_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100211_GROT 1 GROT GROT VARCHAR2 SOURCE_SYSTEM P_20100211_GROT 1 GROT GROT VARCHAR2 SOURCE_SYSTEM P_20100211_HALO 1 HALO HALO VARCHAR2 SOURCE_SYSTEM P_20100211_HALO 1 HALO HALO VARCHAR2 SOURCE_SYSTEM P_20100211_JUNE 1 JUNE JUNE VARCHAR2 SOURCE_SYSTEM P_20100211_JUNE 1 JUNE JUNE VARCHAR2 SOURCE_SYSTEM P_20100211_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100211_OTHERS VARCHAR2 STATUS P_20100207_GROT VARCHAR2 STATUS P_20100207_GROT VARCHAR2 STATUS P_20100207_HALO VARCHAR2 STATUS P_20100207_HALO VARCHAR2 STATUS P_20100207_JUNE VARCHAR2 STATUS P_20100207_JUNE VARCHAR2 STATUS P_20100207_OTHERS VARCHAR2 STATUS P_20100207_OTHERS VARCHAR2 STATUS P_20100209_GROT 2 P Z VARCHAR2 STATUS P_20100209_GROT 2 P Z VARCHAR2 STATUS P_20100209_HALO 1 N N VARCHAR2 STATUS P_20100209_HALO 1 N N VARCHAR2 STATUS P_20100209_JUNE 1 U U VARCHAR2 STATUS P_20100209_JUNE 1 U U VARCHAR2 STATUS P_20100209_OTHERS 2 P Z VARCHAR2 STATUS P_20100209_OTHERS 2 P Z VARCHAR2 STATUS P_20100210_GROT 2 P Z VARCHAR2 STATUS P_20100210_GROT 2 P Z VARCHAR2 STATUS P_20100210_HALO VARCHAR2 STATUS P_20100210_HALO VARCHAR2 STATUS P_20100210_JUNE VARCHAR2 STATUS P_20100210_JUNE VARCHAR2 STATUS P_20100210_OTHERS VARCHAR2 STATUS P_20100210_OTHERS VARCHAR2 STATUS P_20100211_GROT 2 P Z VARCHAR2 STATUS P_20100211_GROT 2 P Z VARCHAR2 STATUS P_20100211_HALO 1 N N VARCHAR2 STATUS P_20100211_HALO 1 N N VARCHAR2 STATUS P_20100211_JUNE 1 U U VARCHAR2 STATUS P_20100211_JUNE 1 U U VARCHAR2 STATUS P_20100211_OTHERS VARCHAR2 STATUS P_20100211_OTHERS VARCHAR2 128 rows selected. SQL> SQL> SPOOL OFF