spool stats_5_6a.txt 

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'; 

PROMPT **************************************************** 
PROMPT First build the table and index and INSERT some data 
PROMPT **************************************************** 

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'; 

DROP TABLE TEST_TAB1; 

CREATE TABLE TEST_TAB1 
( 
  REPORTING_DATE            NUMBER              NOT NULL, 
  SOURCE_SYSTEM             VARCHAR2(30 CHAR)   NOT NULL, 
  SEQ_ID                    NUMBER              NOT NULL, 
  STATUS                    VARCHAR2(1 CHAR)    NOT NULL 
) 
PARTITION BY RANGE (REPORTING_DATE) 
SUBPARTITION BY LIST (SOURCE_SYSTEM) 
SUBPARTITION TEMPLATE 
  (SUBPARTITION GROT VALUES ('GROT') TABLESPACE TEST_DATA, 
   SUBPARTITION JUNE VALUES ('JUNE') TABLESPACE TEST_DATA, 
   SUBPARTITION HALO VALUES ('HALO')  TABLESPACE TEST_DATA, 
   SUBPARTITION OTHERS  VALUES (DEFAULT)   TABLESPACE TEST_DATA) 
(  
  PARTITION P_20100207 VALUES LESS THAN (20100208) NOLOGGING NOCOMPRESS  
) 
NOCOMPRESS 
NOCACHE 
NOPARALLEL 
MONITORING; 



CREATE UNIQUE INDEX TEST_TAB1_IX1 ON TEST_TAB1 
(REPORTING_DATE, SOURCE_SYSTEM, SEQ_ID) 
  LOCAL NOPARALLEL COMPRESS 1; 

ALTER TABLE TEST_TAB1 
ADD  PARTITION P_20100209 VALUES LESS THAN (20100210); 

DROP TABLE LOAD_TAB1; 

CREATE TABLE LOAD_TAB1 
AS SELECT * FROM TEST_TAB1 WHERE 1=0; 

CREATE UNIQUE INDEX LOAD_TAB1_IX1 ON LOAD_TAB1 
(REPORTING_DATE, SOURCE_SYSTEM, SEQ_ID) 
NOPARALLEL COMPRESS 1; 

INSERT INTO LOAD_TAB1 VALUES (20100209, 'GROT', 400, 'P'); 
INSERT INTO LOAD_TAB1 VALUES (20100209, 'GROT', 600, 'P'); 
INSERT INTO LOAD_TAB1 VALUES (20100209, 'GROT', 900, 'Z'); 

exec dbms_stats.gather_table_stats('TESTUSER', 'LOAD_TAB1');

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

TRUNCATE TABLE LOAD_TAB1; 

INSERT INTO LOAD_TAB1 VALUES (20100209, 'JUNE', 400, 'U'); 
INSERT INTO LOAD_TAB1 VALUES (20100209, 'JUNE', 600, 'U'); 
INSERT INTO LOAD_TAB1 VALUES (20100209, 'JUNE', 900, 'U'); 

exec dbms_stats.gather_table_stats('TESTUSER', 'LOAD_TAB1');

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

TRUNCATE TABLE LOAD_TAB1; 

INSERT INTO LOAD_TAB1 VALUES (20100209, 'HALO', 400, 'N'); 
INSERT INTO LOAD_TAB1 VALUES (20100209, 'HALO', 600, 'N'); 
INSERT INTO LOAD_TAB1 VALUES (20100209, 'HALO', 900, 'N'); 

exec dbms_stats.gather_table_stats('TESTUSER', 'LOAD_TAB1');

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

TRUNCATE TABLE LOAD_TAB1; 

INSERT INTO LOAD_TAB1 VALUES (20100209, 'ZZZZ', 400, 'P'); 
INSERT INTO LOAD_TAB1 VALUES (20100209, 'ZZZZ', 600, 'P'); 
INSERT INTO LOAD_TAB1 VALUES (20100209, 'ZZZZ', 900, 'Z'); 

exec dbms_stats.gather_table_stats('TESTUSER', 'LOAD_TAB1');

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

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 
/ 

alter session set "_minimal_stats_aggregation"=FALSE;

TRUNCATE TABLE LOAD_TAB1;

INSERT INTO LOAD_TAB1 VALUES (20100209, 'GROT', 400, 'P'); 
INSERT INTO LOAD_TAB1 VALUES (20100209, 'GROT', 600, 'P'); 
INSERT INTO LOAD_TAB1 VALUES (20100209, 'GROT', 900, 'Z'); 

exec dbms_stats.gather_table_stats('TESTUSER', 'LOAD_TAB1');

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

TRUNCATE TABLE LOAD_TAB1; 

INSERT INTO LOAD_TAB1 VALUES (20100209, 'JUNE', 400, 'U'); 
INSERT INTO LOAD_TAB1 VALUES (20100209, 'JUNE', 600, 'U'); 
INSERT INTO LOAD_TAB1 VALUES (20100209, 'JUNE', 900, 'U'); 

exec dbms_stats.gather_table_stats('TESTUSER', 'LOAD_TAB1');

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

TRUNCATE TABLE LOAD_TAB1; 

INSERT INTO LOAD_TAB1 VALUES (20100209, 'HALO', 400, 'N'); 
INSERT INTO LOAD_TAB1 VALUES (20100209, 'HALO', 600, 'N'); 
INSERT INTO LOAD_TAB1 VALUES (20100209, 'HALO', 900, 'N'); 

exec dbms_stats.gather_table_stats('TESTUSER', 'LOAD_TAB1');

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

TRUNCATE TABLE LOAD_TAB1; 

INSERT INTO LOAD_TAB1 VALUES (20100209, 'ZZZZ', 400, 'P'); 
INSERT INTO LOAD_TAB1 VALUES (20100209, 'ZZZZ', 600, 'P'); 
INSERT INTO LOAD_TAB1 VALUES (20100209, 'ZZZZ', 900, 'Z'); 

exec dbms_stats.gather_table_stats('TESTUSER', 'LOAD_TAB1');

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

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 
/ 


-- set MSA back to default value 
alter session set "_minimal_stats_aggregation"=TRUE; 

PROMPT ***************************************************************************************** 
PROMPT TEST 12 - Create new partition, insert data and then copy stats from a previous partition 
PROMPT ***************************************************************************************** 

ALTER TABLE TEST_TAB1 
ADD  PARTITION P_20100210 VALUES LESS THAN (20100211); 

TRUNCATE TABLE LOAD_TAB1; 

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

COMMIT; 

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

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

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
/

select sysdate from dual;