set pages 9999
set lines 132
set echo on

COLUMN column_name format a26
COLUMN low_val format a20
COLUMN high_val format a20
COLUMN data_type format a20

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

spool stats_6c.txt 

PROMPT STATS_6C

ALTER TABLE TEST_TAB1 
ADD  PARTITION P_20100211 VALUES LESS THAN (20100212); 

TRUNCATE TABLE LOAD_TAB1; 

INSERT INTO LOAD_TAB1 VALUES (20100211, 'GROT', 1000, 'P'); 
INSERT INTO LOAD_TAB1 VALUES (20100211, 'GROT', 30000, 'P'); 
INSERT INTO LOAD_TAB1 VALUES (20100211, 'GROT', 2000, 'P'); 
INSERT INTO LOAD_TAB1 VALUES (20100211, 'GROT', 10000, 'N'); 
INSERT INTO LOAD_TAB1 VALUES (20100211, 'GROT', 2400, 'P'); 
INSERT INTO LOAD_TAB1 VALUES (20100211, 'GROT', 500, 'P'); 
INSERT INTO LOAD_TAB1 VALUES (20100211, 'GROT', 1200, 'P'); 
INSERT INTO LOAD_TAB1 VALUES (20100211, 'GROT', 400, 'P'); 
INSERT INTO LOAD_TAB1 VALUES (20100211, 'GROT', 600, 'P'); 
INSERT INTO LOAD_TAB1 VALUES (20100211, 'GROT', 700, 'Z'); 

COMMIT; 

ALTER TABLE test_tab1 EXCHANGE SUBPARTITION P_20100211_GROT WITH TABLE load_tab1; 
ALTER TABLE test_tab1 MODIFY SUBPARTITION  P_20100211_GROT REBUILD UNUSABLE LOCAL INDEXES; 

-- set MSA to default value 
alter session set "_minimal_stats_aggregation"=TRUE; 
exec dbms_monitor.session_trace_enable;

exec dbms_stats.copy_table_stats(ownname => 'TESTUSER', tabname => 'TEST_TAB1', srcpartname => 'P_20100209_GROT', dstpartname => 'P_20100211_GROT');

exec dbms_stats.copy_table_stats(ownname => 'TESTUSER', tabname => 'TEST_TAB1', srcpartname => 'P_20100209_JUNE', dstpartname => 'P_20100211_JUNE');

exec dbms_stats.copy_table_stats(ownname => 'TESTUSER', tabname => 'TEST_TAB1', srcpartname => 'P_20100209_HALO', dstpartname => 'P_20100211_HALO');

-- Regardless of _minimal_stats_aggregation setting, this next line will fail as Oracle tries to aggregate stats up to partition and table levels

alter session set "_minimal_stats_aggregation"=TRUE; 

exec dbms_stats.copy_table_stats(ownname => 'TESTUSER', tabname => 'TEST_TAB1', srcpartname => 'P_20100209_OTHERS', dstpartname => 'P_20100211_OTHERS');

show parameter user_dump_dest

-- Reconnect and try with different MSA setting

connect testuser/testuser

alter session set "_minimal_stats_aggregation"=FALSE; 

exec dbms_stats.copy_table_stats(ownname => 'TESTUSER', tabname => 'TEST_TAB1', srcpartname => 'P_20100209_OTHERS', dstpartname => 'P_20100211_OTHERS');

show parameter user_dump_dest

-- Try copying stats at partition level rather than subpartition and hopefully subpartition stats
-- will be copied too.

connect testuser/testuser
set pages 9999
set lines 132
set echo on

COLUMN column_name format a26
COLUMN low_val format a20
COLUMN high_val format a20
COLUMN data_type format a20

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

exec dbms_stats.copy_table_stats(ownname => 'TESTUSER', tabname => 'TEST_TAB1', srcpartname => 'P_20100209', dstpartname => 'P_20100211');

select  table_name, global_stats, last_analyzed, num_rows
from dba_tables
where table_name='TEST_TAB1'
and owner='TESTUSER'
order by 1, 2, 4 desc nulls last;

select  table_name, partition_name, global_stats, last_analyzed, num_rows
from dba_tab_partitions
where table_name='TEST_TAB1'
and table_owner='TESTUSER'
order by 1, 2, 4 desc nulls last;

select  table_name, subpartition_name, global_stats, last_analyzed, num_rows
from dba_tab_subpartitions
where table_name='TEST_TAB1'
and table_owner='TESTUSER'
order by 1, 2, 4 desc nulls last;

PROMPT ************************
PROMPT Table-Level Column Stats
PROMPT ************************

select
a.column_name,
a.num_distinct,
display_raw(a.low_value,b.data_type) as low_val,
display_raw(a.high_value,b.data_type) as high_val,
b.data_type
from
dba_tab_col_statistics a, dba_tab_cols b
where
a.owner='TESTUSER' and
a.table_name='TEST_TAB1' and
a.table_name=b.table_name and
a.column_name=b.column_name
order by 1
/

PROMPT ****************************
PROMPT Partition-Level Column Stats
PROMPT ****************************

select
a.column_name, a.partition_name,
a.num_distinct,
display_raw(a.low_value,b.data_type) as low_val,
display_raw(a.high_value,b.data_type) as high_val,
b.data_type
from
dba_part_col_statistics a, dba_tab_cols b
where
a.owner='TESTUSER' and
a.table_name='TEST_TAB1' and
a.table_name=b.table_name and
a.column_name=b.column_name
order by 1, 2
/

PROMPT *******************************
PROMPT Subpartition-Level Column Stats
PROMPT *******************************

select
   a.column_name, a.subpartition_name,
   a.num_distinct,
   display_raw(a.low_value,b.data_type) as low_val,
   display_raw(a.high_value,b.data_type) as high_val,
   b.data_type
from
   dba_subpart_col_statistics a, dba_tab_cols b
where
   a.owner='TESTUSER' and
   a.table_name='TEST_TAB1' and
   a.table_name=b.table_name and
   a.column_name=b.column_name
order by 1, 2
/

SPOOL OFF 

EXIT