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> PROMPT **************************************************** **************************************************** SQL> PROMPT First build the table and index and INSERT some data First build the table and index and INSERT some data SQL> PROMPT **************************************************** **************************************************** SQL> 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> DROP TABLE TEST_TAB1; Table dropped. SQL> SQL> CREATE TABLE TEST_TAB1 2 ( 3 REPORTING_DATE NUMBER NOT NULL, 4 SOURCE_SYSTEM VARCHAR2(30 CHAR) NOT NULL, 5 SEQ_ID NUMBER NOT NULL, 6 STATUS VARCHAR2(1 CHAR) NOT NULL 7 ) 8 PARTITION BY RANGE (REPORTING_DATE) 9 SUBPARTITION BY LIST (SOURCE_SYSTEM) 10 SUBPARTITION TEMPLATE 11 (SUBPARTITION GROT VALUES ('GROT') TABLESPACE TEST_DATA, 12 SUBPARTITION JUNE VALUES ('JUNE') TABLESPACE TEST_DATA, 13 SUBPARTITION HALO VALUES ('HALO') TABLESPACE TEST_DATA, 14 SUBPARTITION OTHERS VALUES (DEFAULT) TABLESPACE TEST_DATA) 15 ( 16 PARTITION P_20100131 VALUES LESS THAN (20100201) NOLOGGING NOCOMPRESS, 17 PARTITION P_20100201 VALUES LESS THAN (20100202) NOLOGGING NOCOMPRESS, 18 PARTITION P_20100202 VALUES LESS THAN (20100203) NOLOGGING NOCOMPRESS, 19 PARTITION P_20100203 VALUES LESS THAN (20100204) NOLOGGING NOCOMPRESS, 20 PARTITION P_20100204 VALUES LESS THAN (20100205) NOLOGGING NOCOMPRESS, 21 PARTITION P_20100205 VALUES LESS THAN (20100206) NOLOGGING NOCOMPRESS, 22 PARTITION P_20100206 VALUES LESS THAN (20100207) NOLOGGING NOCOMPRESS, 23 PARTITION P_20100207 VALUES LESS THAN (20100208) NOLOGGING NOCOMPRESS 24 ) 25 NOCOMPRESS 26 NOCACHE 27 NOPARALLEL 28 MONITORING; Table created. SQL> SQL> SQL> SQL> CREATE UNIQUE INDEX TEST_TAB1_IX1 ON TEST_TAB1 2 (REPORTING_DATE, SOURCE_SYSTEM, SEQ_ID) 3 LOCAL NOPARALLEL COMPRESS 1; Index created. SQL> SQL> ALTER TABLE TEST_TAB1 2 ADD PARTITION P_20100209 VALUES LESS THAN (20100210); Table altered. SQL> SQL> DROP TABLE LOAD_TAB1; Table dropped. SQL> SQL> CREATE TABLE LOAD_TAB1 2 AS SELECT * FROM TEST_TAB1 WHERE 1=0; Table created. SQL> SQL> CREATE UNIQUE INDEX LOAD_TAB1_IX1 ON LOAD_TAB1 2 (REPORTING_DATE, SOURCE_SYSTEM, SEQ_ID) 3 NOPARALLEL COMPRESS 1; Index created. SQL> SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'GROT', 400, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'GROT', 600, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'GROT', 900, 'Z'); 1 row created. SQL> SQL> exec dbms_stats.gather_table_stats('TESTUSER', 'LOAD_TAB1'); PL/SQL procedure successfully completed. SQL> SQL> ALTER TABLE test_tab1 EXCHANGE SUBPARTITION P_20100209_GROT WITH TABLE load_tab1; Table altered. SQL> ALTER TABLE test_tab1 MODIFY SUBPARTITION P_20100209_GROT REBUILD UNUSABLE LOCAL INDEXES; Table altered. SQL> SQL> TRUNCATE TABLE LOAD_TAB1; Table truncated. SQL> SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'JUNE', 400, 'U'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'JUNE', 600, 'U'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'JUNE', 900, 'U'); 1 row created. SQL> SQL> exec dbms_stats.gather_table_stats('TESTUSER', 'LOAD_TAB1'); PL/SQL procedure successfully completed. SQL> SQL> ALTER TABLE test_tab1 EXCHANGE SUBPARTITION P_20100209_JUNE WITH TABLE load_tab1; Table altered. SQL> ALTER TABLE test_tab1 MODIFY SUBPARTITION P_20100209_JUNE REBUILD UNUSABLE LOCAL INDEXES; Table altered. SQL> SQL> TRUNCATE TABLE LOAD_TAB1; Table truncated. SQL> SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'HALO', 400, 'N'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'HALO', 600, 'N'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'HALO', 900, 'N'); 1 row created. SQL> SQL> exec dbms_stats.gather_table_stats('TESTUSER', 'LOAD_TAB1'); PL/SQL procedure successfully completed. SQL> SQL> ALTER TABLE test_tab1 EXCHANGE SUBPARTITION P_20100209_HALO WITH TABLE load_tab1; Table altered. SQL> ALTER TABLE test_tab1 MODIFY SUBPARTITION P_20100209_HALO REBUILD UNUSABLE LOCAL INDEXES; Table altered. SQL> SQL> TRUNCATE TABLE LOAD_TAB1; Table truncated. SQL> SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'ZZZZ', 400, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'ZZZZ', 600, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'ZZZZ', 900, 'Z'); 1 row created. SQL> SQL> exec dbms_stats.gather_table_stats('TESTUSER', 'LOAD_TAB1'); PL/SQL procedure successfully completed. SQL> SQL> ALTER TABLE test_tab1 EXCHANGE SUBPARTITION P_20100209_OTHERS WITH TABLE load_tab1; Table altered. SQL> ALTER TABLE test_tab1 MODIFY SUBPARTITION P_20100209_OTHERS REBUILD UNUSABLE LOCAL INDEXES; Table altered. 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_20100131 NO TEST_TAB1 P_20100201 NO TEST_TAB1 P_20100202 NO TEST_TAB1 P_20100203 NO TEST_TAB1 P_20100204 NO TEST_TAB1 P_20100205 NO TEST_TAB1 P_20100206 NO TEST_TAB1 P_20100207 NO TEST_TAB1 P_20100209 NO 9 rows selected. 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_20100131_GROT NO TEST_TAB1 P_20100131_HALO NO TEST_TAB1 P_20100131_JUNE NO TEST_TAB1 P_20100131_OTHERS NO TEST_TAB1 P_20100201_GROT NO TEST_TAB1 P_20100201_HALO NO TEST_TAB1 P_20100201_JUNE NO TEST_TAB1 P_20100201_OTHERS NO TEST_TAB1 P_20100202_GROT NO TEST_TAB1 P_20100202_HALO NO TEST_TAB1 P_20100202_JUNE NO TEST_TAB1 P_20100202_OTHERS NO TEST_TAB1 P_20100203_GROT NO TEST_TAB1 P_20100203_HALO NO TEST_TAB1 P_20100203_JUNE NO TEST_TAB1 P_20100203_OTHERS NO TEST_TAB1 P_20100204_GROT NO TEST_TAB1 P_20100204_HALO NO TEST_TAB1 P_20100204_JUNE NO TEST_TAB1 P_20100204_OTHERS NO TEST_TAB1 P_20100205_GROT NO TEST_TAB1 P_20100205_HALO NO TEST_TAB1 P_20100205_JUNE NO TEST_TAB1 P_20100205_OTHERS NO TEST_TAB1 P_20100206_GROT NO TEST_TAB1 P_20100206_HALO NO TEST_TAB1 P_20100206_JUNE NO TEST_TAB1 P_20100206_OTHERS NO 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 NO 22-APR-2010 11:24:09 3 TEST_TAB1 P_20100209_HALO NO 22-APR-2010 11:24:10 3 TEST_TAB1 P_20100209_JUNE NO 22-APR-2010 11:24:09 3 TEST_TAB1 P_20100209_OTHERS NO 22-APR-2010 11:24:10 3 36 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_20100131 NUMBER REPORTING_DATE P_20100131 NUMBER REPORTING_DATE P_20100201 NUMBER REPORTING_DATE P_20100201 NUMBER REPORTING_DATE P_20100202 NUMBER REPORTING_DATE P_20100202 NUMBER REPORTING_DATE P_20100203 NUMBER REPORTING_DATE P_20100203 NUMBER REPORTING_DATE P_20100204 NUMBER REPORTING_DATE P_20100204 NUMBER REPORTING_DATE P_20100205 NUMBER REPORTING_DATE P_20100205 NUMBER REPORTING_DATE P_20100206 NUMBER REPORTING_DATE P_20100206 NUMBER REPORTING_DATE P_20100207 NUMBER REPORTING_DATE P_20100207 NUMBER REPORTING_DATE P_20100209 NUMBER REPORTING_DATE P_20100209 NUMBER SEQ_ID P_20100131 NUMBER SEQ_ID P_20100131 NUMBER SEQ_ID P_20100201 NUMBER SEQ_ID P_20100201 NUMBER SEQ_ID P_20100202 NUMBER SEQ_ID P_20100202 NUMBER SEQ_ID P_20100203 NUMBER SEQ_ID P_20100203 NUMBER SEQ_ID P_20100204 NUMBER SEQ_ID P_20100204 NUMBER SEQ_ID P_20100205 NUMBER SEQ_ID P_20100205 NUMBER SEQ_ID P_20100206 NUMBER SEQ_ID P_20100206 NUMBER SEQ_ID P_20100207 NUMBER SEQ_ID P_20100207 NUMBER SEQ_ID P_20100209 NUMBER SEQ_ID P_20100209 NUMBER SOURCE_SYSTEM P_20100131 VARCHAR2 SOURCE_SYSTEM P_20100131 VARCHAR2 SOURCE_SYSTEM P_20100201 VARCHAR2 SOURCE_SYSTEM P_20100201 VARCHAR2 SOURCE_SYSTEM P_20100202 VARCHAR2 SOURCE_SYSTEM P_20100202 VARCHAR2 SOURCE_SYSTEM P_20100203 VARCHAR2 SOURCE_SYSTEM P_20100203 VARCHAR2 SOURCE_SYSTEM P_20100204 VARCHAR2 SOURCE_SYSTEM P_20100204 VARCHAR2 SOURCE_SYSTEM P_20100205 VARCHAR2 SOURCE_SYSTEM P_20100205 VARCHAR2 SOURCE_SYSTEM P_20100206 VARCHAR2 SOURCE_SYSTEM P_20100206 VARCHAR2 SOURCE_SYSTEM P_20100207 VARCHAR2 SOURCE_SYSTEM P_20100207 VARCHAR2 SOURCE_SYSTEM P_20100209 VARCHAR2 SOURCE_SYSTEM P_20100209 VARCHAR2 STATUS P_20100131 VARCHAR2 STATUS P_20100131 VARCHAR2 STATUS P_20100201 VARCHAR2 STATUS P_20100201 VARCHAR2 STATUS P_20100202 VARCHAR2 STATUS P_20100202 VARCHAR2 STATUS P_20100203 VARCHAR2 STATUS P_20100203 VARCHAR2 STATUS P_20100204 VARCHAR2 STATUS P_20100204 VARCHAR2 STATUS P_20100205 VARCHAR2 STATUS P_20100205 VARCHAR2 STATUS P_20100206 VARCHAR2 STATUS P_20100206 VARCHAR2 STATUS P_20100207 VARCHAR2 STATUS P_20100207 VARCHAR2 STATUS P_20100209 VARCHAR2 STATUS P_20100209 VARCHAR2 72 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_20100131_GROT NUMBER REPORTING_DATE P_20100131_GROT NUMBER REPORTING_DATE P_20100131_HALO NUMBER REPORTING_DATE P_20100131_HALO NUMBER REPORTING_DATE P_20100131_JUNE NUMBER REPORTING_DATE P_20100131_JUNE NUMBER REPORTING_DATE P_20100131_OTHERS NUMBER REPORTING_DATE P_20100131_OTHERS NUMBER REPORTING_DATE P_20100201_GROT NUMBER REPORTING_DATE P_20100201_GROT NUMBER REPORTING_DATE P_20100201_HALO NUMBER REPORTING_DATE P_20100201_HALO NUMBER REPORTING_DATE P_20100201_JUNE NUMBER REPORTING_DATE P_20100201_JUNE NUMBER REPORTING_DATE P_20100201_OTHERS NUMBER REPORTING_DATE P_20100201_OTHERS NUMBER REPORTING_DATE P_20100202_GROT NUMBER REPORTING_DATE P_20100202_GROT NUMBER REPORTING_DATE P_20100202_HALO NUMBER REPORTING_DATE P_20100202_HALO NUMBER REPORTING_DATE P_20100202_JUNE NUMBER REPORTING_DATE P_20100202_JUNE NUMBER REPORTING_DATE P_20100202_OTHERS NUMBER REPORTING_DATE P_20100202_OTHERS NUMBER REPORTING_DATE P_20100203_GROT NUMBER REPORTING_DATE P_20100203_GROT NUMBER REPORTING_DATE P_20100203_HALO NUMBER REPORTING_DATE P_20100203_HALO NUMBER REPORTING_DATE P_20100203_JUNE NUMBER REPORTING_DATE P_20100203_JUNE NUMBER REPORTING_DATE P_20100203_OTHERS NUMBER REPORTING_DATE P_20100203_OTHERS NUMBER REPORTING_DATE P_20100204_GROT NUMBER REPORTING_DATE P_20100204_GROT NUMBER REPORTING_DATE P_20100204_HALO NUMBER REPORTING_DATE P_20100204_HALO NUMBER REPORTING_DATE P_20100204_JUNE NUMBER REPORTING_DATE P_20100204_JUNE NUMBER REPORTING_DATE P_20100204_OTHERS NUMBER REPORTING_DATE P_20100204_OTHERS NUMBER REPORTING_DATE P_20100205_GROT NUMBER REPORTING_DATE P_20100205_GROT NUMBER REPORTING_DATE P_20100205_HALO NUMBER REPORTING_DATE P_20100205_HALO NUMBER REPORTING_DATE P_20100205_JUNE NUMBER REPORTING_DATE P_20100205_JUNE NUMBER REPORTING_DATE P_20100205_OTHERS NUMBER REPORTING_DATE P_20100205_OTHERS NUMBER REPORTING_DATE P_20100206_GROT NUMBER REPORTING_DATE P_20100206_GROT NUMBER REPORTING_DATE P_20100206_HALO NUMBER REPORTING_DATE P_20100206_HALO NUMBER REPORTING_DATE P_20100206_JUNE NUMBER REPORTING_DATE P_20100206_JUNE NUMBER REPORTING_DATE P_20100206_OTHERS NUMBER REPORTING_DATE P_20100206_OTHERS NUMBER 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 SEQ_ID P_20100131_GROT NUMBER SEQ_ID P_20100131_GROT NUMBER SEQ_ID P_20100131_HALO NUMBER SEQ_ID P_20100131_HALO NUMBER SEQ_ID P_20100131_JUNE NUMBER SEQ_ID P_20100131_JUNE NUMBER SEQ_ID P_20100131_OTHERS NUMBER SEQ_ID P_20100131_OTHERS NUMBER SEQ_ID P_20100201_GROT NUMBER SEQ_ID P_20100201_GROT NUMBER SEQ_ID P_20100201_HALO NUMBER SEQ_ID P_20100201_HALO NUMBER SEQ_ID P_20100201_JUNE NUMBER SEQ_ID P_20100201_JUNE NUMBER SEQ_ID P_20100201_OTHERS NUMBER SEQ_ID P_20100201_OTHERS NUMBER SEQ_ID P_20100202_GROT NUMBER SEQ_ID P_20100202_GROT NUMBER SEQ_ID P_20100202_HALO NUMBER SEQ_ID P_20100202_HALO NUMBER SEQ_ID P_20100202_JUNE NUMBER SEQ_ID P_20100202_JUNE NUMBER SEQ_ID P_20100202_OTHERS NUMBER SEQ_ID P_20100202_OTHERS NUMBER SEQ_ID P_20100203_GROT NUMBER SEQ_ID P_20100203_GROT NUMBER SEQ_ID P_20100203_HALO NUMBER SEQ_ID P_20100203_HALO NUMBER SEQ_ID P_20100203_JUNE NUMBER SEQ_ID P_20100203_JUNE NUMBER SEQ_ID P_20100203_OTHERS NUMBER SEQ_ID P_20100203_OTHERS NUMBER SEQ_ID P_20100204_GROT NUMBER SEQ_ID P_20100204_GROT NUMBER SEQ_ID P_20100204_HALO NUMBER SEQ_ID P_20100204_HALO NUMBER SEQ_ID P_20100204_JUNE NUMBER SEQ_ID P_20100204_JUNE NUMBER SEQ_ID P_20100204_OTHERS NUMBER SEQ_ID P_20100204_OTHERS NUMBER SEQ_ID P_20100205_GROT NUMBER SEQ_ID P_20100205_GROT NUMBER SEQ_ID P_20100205_HALO NUMBER SEQ_ID P_20100205_HALO NUMBER SEQ_ID P_20100205_JUNE NUMBER SEQ_ID P_20100205_JUNE NUMBER SEQ_ID P_20100205_OTHERS NUMBER SEQ_ID P_20100205_OTHERS NUMBER SEQ_ID P_20100206_GROT NUMBER SEQ_ID P_20100206_GROT NUMBER SEQ_ID P_20100206_HALO NUMBER SEQ_ID P_20100206_HALO NUMBER SEQ_ID P_20100206_JUNE NUMBER SEQ_ID P_20100206_JUNE NUMBER SEQ_ID P_20100206_OTHERS NUMBER SEQ_ID P_20100206_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 SOURCE_SYSTEM P_20100131_GROT VARCHAR2 SOURCE_SYSTEM P_20100131_GROT VARCHAR2 SOURCE_SYSTEM P_20100131_HALO VARCHAR2 SOURCE_SYSTEM P_20100131_HALO VARCHAR2 SOURCE_SYSTEM P_20100131_JUNE VARCHAR2 SOURCE_SYSTEM P_20100131_JUNE VARCHAR2 SOURCE_SYSTEM P_20100131_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100131_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100201_GROT VARCHAR2 SOURCE_SYSTEM P_20100201_GROT VARCHAR2 SOURCE_SYSTEM P_20100201_HALO VARCHAR2 SOURCE_SYSTEM P_20100201_HALO VARCHAR2 SOURCE_SYSTEM P_20100201_JUNE VARCHAR2 SOURCE_SYSTEM P_20100201_JUNE VARCHAR2 SOURCE_SYSTEM P_20100201_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100201_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100202_GROT VARCHAR2 SOURCE_SYSTEM P_20100202_GROT VARCHAR2 SOURCE_SYSTEM P_20100202_HALO VARCHAR2 SOURCE_SYSTEM P_20100202_HALO VARCHAR2 SOURCE_SYSTEM P_20100202_JUNE VARCHAR2 SOURCE_SYSTEM P_20100202_JUNE VARCHAR2 SOURCE_SYSTEM P_20100202_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100202_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100203_GROT VARCHAR2 SOURCE_SYSTEM P_20100203_GROT VARCHAR2 SOURCE_SYSTEM P_20100203_HALO VARCHAR2 SOURCE_SYSTEM P_20100203_HALO VARCHAR2 SOURCE_SYSTEM P_20100203_JUNE VARCHAR2 SOURCE_SYSTEM P_20100203_JUNE VARCHAR2 SOURCE_SYSTEM P_20100203_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100203_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100204_GROT VARCHAR2 SOURCE_SYSTEM P_20100204_GROT VARCHAR2 SOURCE_SYSTEM P_20100204_HALO VARCHAR2 SOURCE_SYSTEM P_20100204_HALO VARCHAR2 SOURCE_SYSTEM P_20100204_JUNE VARCHAR2 SOURCE_SYSTEM P_20100204_JUNE VARCHAR2 SOURCE_SYSTEM P_20100204_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100204_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100205_GROT VARCHAR2 SOURCE_SYSTEM P_20100205_GROT VARCHAR2 SOURCE_SYSTEM P_20100205_HALO VARCHAR2 SOURCE_SYSTEM P_20100205_HALO VARCHAR2 SOURCE_SYSTEM P_20100205_JUNE VARCHAR2 SOURCE_SYSTEM P_20100205_JUNE VARCHAR2 SOURCE_SYSTEM P_20100205_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100205_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100206_GROT VARCHAR2 SOURCE_SYSTEM P_20100206_GROT VARCHAR2 SOURCE_SYSTEM P_20100206_HALO VARCHAR2 SOURCE_SYSTEM P_20100206_HALO VARCHAR2 SOURCE_SYSTEM P_20100206_JUNE VARCHAR2 SOURCE_SYSTEM P_20100206_JUNE VARCHAR2 SOURCE_SYSTEM P_20100206_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100206_OTHERS VARCHAR2 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 STATUS P_20100131_GROT VARCHAR2 STATUS P_20100131_GROT VARCHAR2 STATUS P_20100131_HALO VARCHAR2 STATUS P_20100131_HALO VARCHAR2 STATUS P_20100131_JUNE VARCHAR2 STATUS P_20100131_JUNE VARCHAR2 STATUS P_20100131_OTHERS VARCHAR2 STATUS P_20100131_OTHERS VARCHAR2 STATUS P_20100201_GROT VARCHAR2 STATUS P_20100201_GROT VARCHAR2 STATUS P_20100201_HALO VARCHAR2 STATUS P_20100201_HALO VARCHAR2 STATUS P_20100201_JUNE VARCHAR2 STATUS P_20100201_JUNE VARCHAR2 STATUS P_20100201_OTHERS VARCHAR2 STATUS P_20100201_OTHERS VARCHAR2 STATUS P_20100202_GROT VARCHAR2 STATUS P_20100202_GROT VARCHAR2 STATUS P_20100202_HALO VARCHAR2 STATUS P_20100202_HALO VARCHAR2 STATUS P_20100202_JUNE VARCHAR2 STATUS P_20100202_JUNE VARCHAR2 STATUS P_20100202_OTHERS VARCHAR2 STATUS P_20100202_OTHERS VARCHAR2 STATUS P_20100203_GROT VARCHAR2 STATUS P_20100203_GROT VARCHAR2 STATUS P_20100203_HALO VARCHAR2 STATUS P_20100203_HALO VARCHAR2 STATUS P_20100203_JUNE VARCHAR2 STATUS P_20100203_JUNE VARCHAR2 STATUS P_20100203_OTHERS VARCHAR2 STATUS P_20100203_OTHERS VARCHAR2 STATUS P_20100204_GROT VARCHAR2 STATUS P_20100204_GROT VARCHAR2 STATUS P_20100204_HALO VARCHAR2 STATUS P_20100204_HALO VARCHAR2 STATUS P_20100204_JUNE VARCHAR2 STATUS P_20100204_JUNE VARCHAR2 STATUS P_20100204_OTHERS VARCHAR2 STATUS P_20100204_OTHERS VARCHAR2 STATUS P_20100205_GROT VARCHAR2 STATUS P_20100205_GROT VARCHAR2 STATUS P_20100205_HALO VARCHAR2 STATUS P_20100205_HALO VARCHAR2 STATUS P_20100205_JUNE VARCHAR2 STATUS P_20100205_JUNE VARCHAR2 STATUS P_20100205_OTHERS VARCHAR2 STATUS P_20100205_OTHERS VARCHAR2 STATUS P_20100206_GROT VARCHAR2 STATUS P_20100206_GROT VARCHAR2 STATUS P_20100206_HALO VARCHAR2 STATUS P_20100206_HALO VARCHAR2 STATUS P_20100206_JUNE VARCHAR2 STATUS P_20100206_JUNE VARCHAR2 STATUS P_20100206_OTHERS VARCHAR2 STATUS P_20100206_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 288 rows selected. SQL> SQL> alter session set "_minimal_stats_aggregation"=FALSE; Session altered. SQL> SQL> TRUNCATE TABLE LOAD_TAB1; Table truncated. SQL> SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'GROT', 400, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'GROT', 600, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'GROT', 900, 'Z'); 1 row created. SQL> SQL> exec dbms_stats.gather_table_stats('TESTUSER', 'LOAD_TAB1'); PL/SQL procedure successfully completed. SQL> SQL> ALTER TABLE test_tab1 EXCHANGE SUBPARTITION P_20100209_GROT WITH TABLE load_tab1; Table altered. SQL> ALTER TABLE test_tab1 MODIFY SUBPARTITION P_20100209_GROT REBUILD UNUSABLE LOCAL INDEXES; Table altered. SQL> SQL> TRUNCATE TABLE LOAD_TAB1; Table truncated. SQL> SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'JUNE', 400, 'U'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'JUNE', 600, 'U'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'JUNE', 900, 'U'); 1 row created. SQL> SQL> exec dbms_stats.gather_table_stats('TESTUSER', 'LOAD_TAB1'); PL/SQL procedure successfully completed. SQL> SQL> ALTER TABLE test_tab1 EXCHANGE SUBPARTITION P_20100209_JUNE WITH TABLE load_tab1; Table altered. SQL> ALTER TABLE test_tab1 MODIFY SUBPARTITION P_20100209_JUNE REBUILD UNUSABLE LOCAL INDEXES; Table altered. SQL> SQL> TRUNCATE TABLE LOAD_TAB1; Table truncated. SQL> SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'HALO', 400, 'N'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'HALO', 600, 'N'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'HALO', 900, 'N'); 1 row created. SQL> SQL> exec dbms_stats.gather_table_stats('TESTUSER', 'LOAD_TAB1'); PL/SQL procedure successfully completed. SQL> SQL> ALTER TABLE test_tab1 EXCHANGE SUBPARTITION P_20100209_HALO WITH TABLE load_tab1; Table altered. SQL> ALTER TABLE test_tab1 MODIFY SUBPARTITION P_20100209_HALO REBUILD UNUSABLE LOCAL INDEXES; Table altered. SQL> SQL> TRUNCATE TABLE LOAD_TAB1; Table truncated. SQL> SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'ZZZZ', 400, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'ZZZZ', 600, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100209, 'ZZZZ', 900, 'Z'); 1 row created. SQL> SQL> exec dbms_stats.gather_table_stats('TESTUSER', 'LOAD_TAB1'); PL/SQL procedure successfully completed. SQL> SQL> ALTER TABLE test_tab1 EXCHANGE SUBPARTITION P_20100209_OTHERS WITH TABLE load_tab1; Table altered. SQL> ALTER TABLE test_tab1 MODIFY SUBPARTITION P_20100209_OTHERS REBUILD UNUSABLE LOCAL INDEXES; Table altered. 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_20100131 NO TEST_TAB1 P_20100201 NO TEST_TAB1 P_20100202 NO TEST_TAB1 P_20100203 NO TEST_TAB1 P_20100204 NO TEST_TAB1 P_20100205 NO TEST_TAB1 P_20100206 NO TEST_TAB1 P_20100207 NO TEST_TAB1 P_20100209 NO 22-APR-2010 11:24:13 12 9 rows selected. 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_20100131_GROT NO TEST_TAB1 P_20100131_HALO NO TEST_TAB1 P_20100131_JUNE NO TEST_TAB1 P_20100131_OTHERS NO TEST_TAB1 P_20100201_GROT NO TEST_TAB1 P_20100201_HALO NO TEST_TAB1 P_20100201_JUNE NO TEST_TAB1 P_20100201_OTHERS NO TEST_TAB1 P_20100202_GROT NO TEST_TAB1 P_20100202_HALO NO TEST_TAB1 P_20100202_JUNE NO TEST_TAB1 P_20100202_OTHERS NO TEST_TAB1 P_20100203_GROT NO TEST_TAB1 P_20100203_HALO NO TEST_TAB1 P_20100203_JUNE NO TEST_TAB1 P_20100203_OTHERS NO TEST_TAB1 P_20100204_GROT NO TEST_TAB1 P_20100204_HALO NO TEST_TAB1 P_20100204_JUNE NO TEST_TAB1 P_20100204_OTHERS NO TEST_TAB1 P_20100205_GROT NO TEST_TAB1 P_20100205_HALO NO TEST_TAB1 P_20100205_JUNE NO TEST_TAB1 P_20100205_OTHERS NO TEST_TAB1 P_20100206_GROT NO TEST_TAB1 P_20100206_HALO NO TEST_TAB1 P_20100206_JUNE NO TEST_TAB1 P_20100206_OTHERS NO 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 NO 22-APR-2010 11:24:12 3 TEST_TAB1 P_20100209_HALO NO 22-APR-2010 11:24:13 3 TEST_TAB1 P_20100209_JUNE NO 22-APR-2010 11:24:13 3 TEST_TAB1 P_20100209_OTHERS NO 22-APR-2010 11:24:13 3 36 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_20100131 NUMBER REPORTING_DATE P_20100131 NUMBER REPORTING_DATE P_20100201 NUMBER REPORTING_DATE P_20100201 NUMBER REPORTING_DATE P_20100202 NUMBER REPORTING_DATE P_20100202 NUMBER REPORTING_DATE P_20100203 NUMBER REPORTING_DATE P_20100203 NUMBER REPORTING_DATE P_20100204 NUMBER REPORTING_DATE P_20100204 NUMBER REPORTING_DATE P_20100205 NUMBER REPORTING_DATE P_20100205 NUMBER REPORTING_DATE P_20100206 NUMBER REPORTING_DATE P_20100206 NUMBER 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 SEQ_ID P_20100131 NUMBER SEQ_ID P_20100131 NUMBER SEQ_ID P_20100201 NUMBER SEQ_ID P_20100201 NUMBER SEQ_ID P_20100202 NUMBER SEQ_ID P_20100202 NUMBER SEQ_ID P_20100203 NUMBER SEQ_ID P_20100203 NUMBER SEQ_ID P_20100204 NUMBER SEQ_ID P_20100204 NUMBER SEQ_ID P_20100205 NUMBER SEQ_ID P_20100205 NUMBER SEQ_ID P_20100206 NUMBER SEQ_ID P_20100206 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 SOURCE_SYSTEM P_20100131 VARCHAR2 SOURCE_SYSTEM P_20100131 VARCHAR2 SOURCE_SYSTEM P_20100201 VARCHAR2 SOURCE_SYSTEM P_20100201 VARCHAR2 SOURCE_SYSTEM P_20100202 VARCHAR2 SOURCE_SYSTEM P_20100202 VARCHAR2 SOURCE_SYSTEM P_20100203 VARCHAR2 SOURCE_SYSTEM P_20100203 VARCHAR2 SOURCE_SYSTEM P_20100204 VARCHAR2 SOURCE_SYSTEM P_20100204 VARCHAR2 SOURCE_SYSTEM P_20100205 VARCHAR2 SOURCE_SYSTEM P_20100205 VARCHAR2 SOURCE_SYSTEM P_20100206 VARCHAR2 SOURCE_SYSTEM P_20100206 VARCHAR2 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 STATUS P_20100131 VARCHAR2 STATUS P_20100131 VARCHAR2 STATUS P_20100201 VARCHAR2 STATUS P_20100201 VARCHAR2 STATUS P_20100202 VARCHAR2 STATUS P_20100202 VARCHAR2 STATUS P_20100203 VARCHAR2 STATUS P_20100203 VARCHAR2 STATUS P_20100204 VARCHAR2 STATUS P_20100204 VARCHAR2 STATUS P_20100205 VARCHAR2 STATUS P_20100205 VARCHAR2 STATUS P_20100206 VARCHAR2 STATUS P_20100206 VARCHAR2 STATUS P_20100207 VARCHAR2 STATUS P_20100207 VARCHAR2 STATUS P_20100209 3 N Z VARCHAR2 STATUS P_20100209 3 N Z VARCHAR2 72 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_20100131_GROT NUMBER REPORTING_DATE P_20100131_GROT NUMBER REPORTING_DATE P_20100131_HALO NUMBER REPORTING_DATE P_20100131_HALO NUMBER REPORTING_DATE P_20100131_JUNE NUMBER REPORTING_DATE P_20100131_JUNE NUMBER REPORTING_DATE P_20100131_OTHERS NUMBER REPORTING_DATE P_20100131_OTHERS NUMBER REPORTING_DATE P_20100201_GROT NUMBER REPORTING_DATE P_20100201_GROT NUMBER REPORTING_DATE P_20100201_HALO NUMBER REPORTING_DATE P_20100201_HALO NUMBER REPORTING_DATE P_20100201_JUNE NUMBER REPORTING_DATE P_20100201_JUNE NUMBER REPORTING_DATE P_20100201_OTHERS NUMBER REPORTING_DATE P_20100201_OTHERS NUMBER REPORTING_DATE P_20100202_GROT NUMBER REPORTING_DATE P_20100202_GROT NUMBER REPORTING_DATE P_20100202_HALO NUMBER REPORTING_DATE P_20100202_HALO NUMBER REPORTING_DATE P_20100202_JUNE NUMBER REPORTING_DATE P_20100202_JUNE NUMBER REPORTING_DATE P_20100202_OTHERS NUMBER REPORTING_DATE P_20100202_OTHERS NUMBER REPORTING_DATE P_20100203_GROT NUMBER REPORTING_DATE P_20100203_GROT NUMBER REPORTING_DATE P_20100203_HALO NUMBER REPORTING_DATE P_20100203_HALO NUMBER REPORTING_DATE P_20100203_JUNE NUMBER REPORTING_DATE P_20100203_JUNE NUMBER REPORTING_DATE P_20100203_OTHERS NUMBER REPORTING_DATE P_20100203_OTHERS NUMBER REPORTING_DATE P_20100204_GROT NUMBER REPORTING_DATE P_20100204_GROT NUMBER REPORTING_DATE P_20100204_HALO NUMBER REPORTING_DATE P_20100204_HALO NUMBER REPORTING_DATE P_20100204_JUNE NUMBER REPORTING_DATE P_20100204_JUNE NUMBER REPORTING_DATE P_20100204_OTHERS NUMBER REPORTING_DATE P_20100204_OTHERS NUMBER REPORTING_DATE P_20100205_GROT NUMBER REPORTING_DATE P_20100205_GROT NUMBER REPORTING_DATE P_20100205_HALO NUMBER REPORTING_DATE P_20100205_HALO NUMBER REPORTING_DATE P_20100205_JUNE NUMBER REPORTING_DATE P_20100205_JUNE NUMBER REPORTING_DATE P_20100205_OTHERS NUMBER REPORTING_DATE P_20100205_OTHERS NUMBER REPORTING_DATE P_20100206_GROT NUMBER REPORTING_DATE P_20100206_GROT NUMBER REPORTING_DATE P_20100206_HALO NUMBER REPORTING_DATE P_20100206_HALO NUMBER REPORTING_DATE P_20100206_JUNE NUMBER REPORTING_DATE P_20100206_JUNE NUMBER REPORTING_DATE P_20100206_OTHERS NUMBER REPORTING_DATE P_20100206_OTHERS NUMBER 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 SEQ_ID P_20100131_GROT NUMBER SEQ_ID P_20100131_GROT NUMBER SEQ_ID P_20100131_HALO NUMBER SEQ_ID P_20100131_HALO NUMBER SEQ_ID P_20100131_JUNE NUMBER SEQ_ID P_20100131_JUNE NUMBER SEQ_ID P_20100131_OTHERS NUMBER SEQ_ID P_20100131_OTHERS NUMBER SEQ_ID P_20100201_GROT NUMBER SEQ_ID P_20100201_GROT NUMBER SEQ_ID P_20100201_HALO NUMBER SEQ_ID P_20100201_HALO NUMBER SEQ_ID P_20100201_JUNE NUMBER SEQ_ID P_20100201_JUNE NUMBER SEQ_ID P_20100201_OTHERS NUMBER SEQ_ID P_20100201_OTHERS NUMBER SEQ_ID P_20100202_GROT NUMBER SEQ_ID P_20100202_GROT NUMBER SEQ_ID P_20100202_HALO NUMBER SEQ_ID P_20100202_HALO NUMBER SEQ_ID P_20100202_JUNE NUMBER SEQ_ID P_20100202_JUNE NUMBER SEQ_ID P_20100202_OTHERS NUMBER SEQ_ID P_20100202_OTHERS NUMBER SEQ_ID P_20100203_GROT NUMBER SEQ_ID P_20100203_GROT NUMBER SEQ_ID P_20100203_HALO NUMBER SEQ_ID P_20100203_HALO NUMBER SEQ_ID P_20100203_JUNE NUMBER SEQ_ID P_20100203_JUNE NUMBER SEQ_ID P_20100203_OTHERS NUMBER SEQ_ID P_20100203_OTHERS NUMBER SEQ_ID P_20100204_GROT NUMBER SEQ_ID P_20100204_GROT NUMBER SEQ_ID P_20100204_HALO NUMBER SEQ_ID P_20100204_HALO NUMBER SEQ_ID P_20100204_JUNE NUMBER SEQ_ID P_20100204_JUNE NUMBER SEQ_ID P_20100204_OTHERS NUMBER SEQ_ID P_20100204_OTHERS NUMBER SEQ_ID P_20100205_GROT NUMBER SEQ_ID P_20100205_GROT NUMBER SEQ_ID P_20100205_HALO NUMBER SEQ_ID P_20100205_HALO NUMBER SEQ_ID P_20100205_JUNE NUMBER SEQ_ID P_20100205_JUNE NUMBER SEQ_ID P_20100205_OTHERS NUMBER SEQ_ID P_20100205_OTHERS NUMBER SEQ_ID P_20100206_GROT NUMBER SEQ_ID P_20100206_GROT NUMBER SEQ_ID P_20100206_HALO NUMBER SEQ_ID P_20100206_HALO NUMBER SEQ_ID P_20100206_JUNE NUMBER SEQ_ID P_20100206_JUNE NUMBER SEQ_ID P_20100206_OTHERS NUMBER SEQ_ID P_20100206_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 SOURCE_SYSTEM P_20100131_GROT VARCHAR2 SOURCE_SYSTEM P_20100131_GROT VARCHAR2 SOURCE_SYSTEM P_20100131_HALO VARCHAR2 SOURCE_SYSTEM P_20100131_HALO VARCHAR2 SOURCE_SYSTEM P_20100131_JUNE VARCHAR2 SOURCE_SYSTEM P_20100131_JUNE VARCHAR2 SOURCE_SYSTEM P_20100131_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100131_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100201_GROT VARCHAR2 SOURCE_SYSTEM P_20100201_GROT VARCHAR2 SOURCE_SYSTEM P_20100201_HALO VARCHAR2 SOURCE_SYSTEM P_20100201_HALO VARCHAR2 SOURCE_SYSTEM P_20100201_JUNE VARCHAR2 SOURCE_SYSTEM P_20100201_JUNE VARCHAR2 SOURCE_SYSTEM P_20100201_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100201_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100202_GROT VARCHAR2 SOURCE_SYSTEM P_20100202_GROT VARCHAR2 SOURCE_SYSTEM P_20100202_HALO VARCHAR2 SOURCE_SYSTEM P_20100202_HALO VARCHAR2 SOURCE_SYSTEM P_20100202_JUNE VARCHAR2 SOURCE_SYSTEM P_20100202_JUNE VARCHAR2 SOURCE_SYSTEM P_20100202_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100202_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100203_GROT VARCHAR2 SOURCE_SYSTEM P_20100203_GROT VARCHAR2 SOURCE_SYSTEM P_20100203_HALO VARCHAR2 SOURCE_SYSTEM P_20100203_HALO VARCHAR2 SOURCE_SYSTEM P_20100203_JUNE VARCHAR2 SOURCE_SYSTEM P_20100203_JUNE VARCHAR2 SOURCE_SYSTEM P_20100203_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100203_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100204_GROT VARCHAR2 SOURCE_SYSTEM P_20100204_GROT VARCHAR2 SOURCE_SYSTEM P_20100204_HALO VARCHAR2 SOURCE_SYSTEM P_20100204_HALO VARCHAR2 SOURCE_SYSTEM P_20100204_JUNE VARCHAR2 SOURCE_SYSTEM P_20100204_JUNE VARCHAR2 SOURCE_SYSTEM P_20100204_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100204_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100205_GROT VARCHAR2 SOURCE_SYSTEM P_20100205_GROT VARCHAR2 SOURCE_SYSTEM P_20100205_HALO VARCHAR2 SOURCE_SYSTEM P_20100205_HALO VARCHAR2 SOURCE_SYSTEM P_20100205_JUNE VARCHAR2 SOURCE_SYSTEM P_20100205_JUNE VARCHAR2 SOURCE_SYSTEM P_20100205_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100205_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100206_GROT VARCHAR2 SOURCE_SYSTEM P_20100206_GROT VARCHAR2 SOURCE_SYSTEM P_20100206_HALO VARCHAR2 SOURCE_SYSTEM P_20100206_HALO VARCHAR2 SOURCE_SYSTEM P_20100206_JUNE VARCHAR2 SOURCE_SYSTEM P_20100206_JUNE VARCHAR2 SOURCE_SYSTEM P_20100206_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100206_OTHERS VARCHAR2 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 STATUS P_20100131_GROT VARCHAR2 STATUS P_20100131_GROT VARCHAR2 STATUS P_20100131_HALO VARCHAR2 STATUS P_20100131_HALO VARCHAR2 STATUS P_20100131_JUNE VARCHAR2 STATUS P_20100131_JUNE VARCHAR2 STATUS P_20100131_OTHERS VARCHAR2 STATUS P_20100131_OTHERS VARCHAR2 STATUS P_20100201_GROT VARCHAR2 STATUS P_20100201_GROT VARCHAR2 STATUS P_20100201_HALO VARCHAR2 STATUS P_20100201_HALO VARCHAR2 STATUS P_20100201_JUNE VARCHAR2 STATUS P_20100201_JUNE VARCHAR2 STATUS P_20100201_OTHERS VARCHAR2 STATUS P_20100201_OTHERS VARCHAR2 STATUS P_20100202_GROT VARCHAR2 STATUS P_20100202_GROT VARCHAR2 STATUS P_20100202_HALO VARCHAR2 STATUS P_20100202_HALO VARCHAR2 STATUS P_20100202_JUNE VARCHAR2 STATUS P_20100202_JUNE VARCHAR2 STATUS P_20100202_OTHERS VARCHAR2 STATUS P_20100202_OTHERS VARCHAR2 STATUS P_20100203_GROT VARCHAR2 STATUS P_20100203_GROT VARCHAR2 STATUS P_20100203_HALO VARCHAR2 STATUS P_20100203_HALO VARCHAR2 STATUS P_20100203_JUNE VARCHAR2 STATUS P_20100203_JUNE VARCHAR2 STATUS P_20100203_OTHERS VARCHAR2 STATUS P_20100203_OTHERS VARCHAR2 STATUS P_20100204_GROT VARCHAR2 STATUS P_20100204_GROT VARCHAR2 STATUS P_20100204_HALO VARCHAR2 STATUS P_20100204_HALO VARCHAR2 STATUS P_20100204_JUNE VARCHAR2 STATUS P_20100204_JUNE VARCHAR2 STATUS P_20100204_OTHERS VARCHAR2 STATUS P_20100204_OTHERS VARCHAR2 STATUS P_20100205_GROT VARCHAR2 STATUS P_20100205_GROT VARCHAR2 STATUS P_20100205_HALO VARCHAR2 STATUS P_20100205_HALO VARCHAR2 STATUS P_20100205_JUNE VARCHAR2 STATUS P_20100205_JUNE VARCHAR2 STATUS P_20100205_OTHERS VARCHAR2 STATUS P_20100205_OTHERS VARCHAR2 STATUS P_20100206_GROT VARCHAR2 STATUS P_20100206_GROT VARCHAR2 STATUS P_20100206_HALO VARCHAR2 STATUS P_20100206_HALO VARCHAR2 STATUS P_20100206_JUNE VARCHAR2 STATUS P_20100206_JUNE VARCHAR2 STATUS P_20100206_OTHERS VARCHAR2 STATUS P_20100206_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 288 rows selected. SQL> SQL> SQL> -- set MSA back to default value SQL> alter session set "_minimal_stats_aggregation"=TRUE; Session altered. SQL> SQL> PROMPT ***************************************************************************************** ***************************************************************************************** SQL> PROMPT TEST 12 - Create new partition, insert data and then copy stats from a previous partition TEST 12 - Create new partition, insert data and then copy stats from a previous partition SQL> PROMPT ***************************************************************************************** ***************************************************************************************** SQL> SQL> ALTER TABLE TEST_TAB1 2 ADD PARTITION P_20100210 VALUES LESS THAN (20100211); Table altered. SQL> SQL> TRUNCATE TABLE LOAD_TAB1; Table truncated. SQL> SQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 1000, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 30000, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 2000, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 10000, 'N'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 2400, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 500, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 1200, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 400, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 600, 'P'); 1 row created. SQL> INSERT INTO LOAD_TAB1 VALUES (20100210, 'GROT', 700, 'Z'); 1 row created. SQL> SQL> COMMIT; Commit complete. SQL> SQL> ALTER TABLE test_tab1 EXCHANGE SUBPARTITION P_20100210_GROT WITH TABLE load_tab1; Table altered. SQL> ALTER TABLE test_tab1 MODIFY SUBPARTITION P_20100210_GROT REBUILD UNUSABLE LOCAL INDEXES; Table altered. SQL> SQL> exec dbms_stats.copy_table_stats(ownname => 'TESTUSER', tabname => 'TEST_TAB1', srcpartname => 'P_20100209', dstpartname => 'P_20100210'); 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_20100131 NO TEST_TAB1 P_20100201 NO TEST_TAB1 P_20100202 NO TEST_TAB1 P_20100203 NO TEST_TAB1 P_20100204 NO TEST_TAB1 P_20100205 NO TEST_TAB1 P_20100206 NO TEST_TAB1 P_20100207 NO TEST_TAB1 P_20100209 NO 22-APR-2010 11:24:15 12 TEST_TAB1 P_20100210 NO 10 rows selected. 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_20100131_GROT NO TEST_TAB1 P_20100131_HALO NO TEST_TAB1 P_20100131_JUNE NO TEST_TAB1 P_20100131_OTHERS NO TEST_TAB1 P_20100201_GROT NO TEST_TAB1 P_20100201_HALO NO TEST_TAB1 P_20100201_JUNE NO TEST_TAB1 P_20100201_OTHERS NO TEST_TAB1 P_20100202_GROT NO TEST_TAB1 P_20100202_HALO NO TEST_TAB1 P_20100202_JUNE NO TEST_TAB1 P_20100202_OTHERS NO TEST_TAB1 P_20100203_GROT NO TEST_TAB1 P_20100203_HALO NO TEST_TAB1 P_20100203_JUNE NO TEST_TAB1 P_20100203_OTHERS NO TEST_TAB1 P_20100204_GROT NO TEST_TAB1 P_20100204_HALO NO TEST_TAB1 P_20100204_JUNE NO TEST_TAB1 P_20100204_OTHERS NO TEST_TAB1 P_20100205_GROT NO TEST_TAB1 P_20100205_HALO NO TEST_TAB1 P_20100205_JUNE NO TEST_TAB1 P_20100205_OTHERS NO TEST_TAB1 P_20100206_GROT NO TEST_TAB1 P_20100206_HALO NO TEST_TAB1 P_20100206_JUNE NO TEST_TAB1 P_20100206_OTHERS NO 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 NO 22-APR-2010 11:24:12 3 TEST_TAB1 P_20100209_HALO NO 22-APR-2010 11:24:13 3 TEST_TAB1 P_20100209_JUNE NO 22-APR-2010 11:24:13 3 TEST_TAB1 P_20100209_OTHERS NO 22-APR-2010 11:24:13 3 TEST_TAB1 P_20100210_GROT NO 22-APR-2010 11:24:10 3 TEST_TAB1 P_20100210_HALO NO TEST_TAB1 P_20100210_JUNE NO TEST_TAB1 P_20100210_OTHERS NO 40 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_20100131 NUMBER REPORTING_DATE P_20100131 NUMBER REPORTING_DATE P_20100201 NUMBER REPORTING_DATE P_20100201 NUMBER REPORTING_DATE P_20100202 NUMBER REPORTING_DATE P_20100202 NUMBER REPORTING_DATE P_20100203 NUMBER REPORTING_DATE P_20100203 NUMBER REPORTING_DATE P_20100204 NUMBER REPORTING_DATE P_20100204 NUMBER REPORTING_DATE P_20100205 NUMBER REPORTING_DATE P_20100205 NUMBER REPORTING_DATE P_20100206 NUMBER REPORTING_DATE P_20100206 NUMBER 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 0 20100210 20100211 NUMBER REPORTING_DATE P_20100210 0 20100210 20100211 NUMBER SEQ_ID P_20100131 NUMBER SEQ_ID P_20100131 NUMBER SEQ_ID P_20100201 NUMBER SEQ_ID P_20100201 NUMBER SEQ_ID P_20100202 NUMBER SEQ_ID P_20100202 NUMBER SEQ_ID P_20100203 NUMBER SEQ_ID P_20100203 NUMBER SEQ_ID P_20100204 NUMBER SEQ_ID P_20100204 NUMBER SEQ_ID P_20100205 NUMBER SEQ_ID P_20100205 NUMBER SEQ_ID P_20100206 NUMBER SEQ_ID P_20100206 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 0 400 900 NUMBER SEQ_ID P_20100210 0 400 900 NUMBER SOURCE_SYSTEM P_20100131 VARCHAR2 SOURCE_SYSTEM P_20100131 VARCHAR2 SOURCE_SYSTEM P_20100201 VARCHAR2 SOURCE_SYSTEM P_20100201 VARCHAR2 SOURCE_SYSTEM P_20100202 VARCHAR2 SOURCE_SYSTEM P_20100202 VARCHAR2 SOURCE_SYSTEM P_20100203 VARCHAR2 SOURCE_SYSTEM P_20100203 VARCHAR2 SOURCE_SYSTEM P_20100204 VARCHAR2 SOURCE_SYSTEM P_20100204 VARCHAR2 SOURCE_SYSTEM P_20100205 VARCHAR2 SOURCE_SYSTEM P_20100205 VARCHAR2 SOURCE_SYSTEM P_20100206 VARCHAR2 SOURCE_SYSTEM P_20100206 VARCHAR2 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 0 GROT ZZZZ VARCHAR2 SOURCE_SYSTEM P_20100210 0 GROT ZZZZ VARCHAR2 STATUS P_20100131 VARCHAR2 STATUS P_20100131 VARCHAR2 STATUS P_20100201 VARCHAR2 STATUS P_20100201 VARCHAR2 STATUS P_20100202 VARCHAR2 STATUS P_20100202 VARCHAR2 STATUS P_20100203 VARCHAR2 STATUS P_20100203 VARCHAR2 STATUS P_20100204 VARCHAR2 STATUS P_20100204 VARCHAR2 STATUS P_20100205 VARCHAR2 STATUS P_20100205 VARCHAR2 STATUS P_20100206 VARCHAR2 STATUS P_20100206 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 0 N Z VARCHAR2 STATUS P_20100210 0 N Z VARCHAR2 80 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_20100131_GROT NUMBER REPORTING_DATE P_20100131_GROT NUMBER REPORTING_DATE P_20100131_HALO NUMBER REPORTING_DATE P_20100131_HALO NUMBER REPORTING_DATE P_20100131_JUNE NUMBER REPORTING_DATE P_20100131_JUNE NUMBER REPORTING_DATE P_20100131_OTHERS NUMBER REPORTING_DATE P_20100131_OTHERS NUMBER REPORTING_DATE P_20100201_GROT NUMBER REPORTING_DATE P_20100201_GROT NUMBER REPORTING_DATE P_20100201_HALO NUMBER REPORTING_DATE P_20100201_HALO NUMBER REPORTING_DATE P_20100201_JUNE NUMBER REPORTING_DATE P_20100201_JUNE NUMBER REPORTING_DATE P_20100201_OTHERS NUMBER REPORTING_DATE P_20100201_OTHERS NUMBER REPORTING_DATE P_20100202_GROT NUMBER REPORTING_DATE P_20100202_GROT NUMBER REPORTING_DATE P_20100202_HALO NUMBER REPORTING_DATE P_20100202_HALO NUMBER REPORTING_DATE P_20100202_JUNE NUMBER REPORTING_DATE P_20100202_JUNE NUMBER REPORTING_DATE P_20100202_OTHERS NUMBER REPORTING_DATE P_20100202_OTHERS NUMBER REPORTING_DATE P_20100203_GROT NUMBER REPORTING_DATE P_20100203_GROT NUMBER REPORTING_DATE P_20100203_HALO NUMBER REPORTING_DATE P_20100203_HALO NUMBER REPORTING_DATE P_20100203_JUNE NUMBER REPORTING_DATE P_20100203_JUNE NUMBER REPORTING_DATE P_20100203_OTHERS NUMBER REPORTING_DATE P_20100203_OTHERS NUMBER REPORTING_DATE P_20100204_GROT NUMBER REPORTING_DATE P_20100204_GROT NUMBER REPORTING_DATE P_20100204_HALO NUMBER REPORTING_DATE P_20100204_HALO NUMBER REPORTING_DATE P_20100204_JUNE NUMBER REPORTING_DATE P_20100204_JUNE NUMBER REPORTING_DATE P_20100204_OTHERS NUMBER REPORTING_DATE P_20100204_OTHERS NUMBER REPORTING_DATE P_20100205_GROT NUMBER REPORTING_DATE P_20100205_GROT NUMBER REPORTING_DATE P_20100205_HALO NUMBER REPORTING_DATE P_20100205_HALO NUMBER REPORTING_DATE P_20100205_JUNE NUMBER REPORTING_DATE P_20100205_JUNE NUMBER REPORTING_DATE P_20100205_OTHERS NUMBER REPORTING_DATE P_20100205_OTHERS NUMBER REPORTING_DATE P_20100206_GROT NUMBER REPORTING_DATE P_20100206_GROT NUMBER REPORTING_DATE P_20100206_HALO NUMBER REPORTING_DATE P_20100206_HALO NUMBER REPORTING_DATE P_20100206_JUNE NUMBER REPORTING_DATE P_20100206_JUNE NUMBER REPORTING_DATE P_20100206_OTHERS NUMBER REPORTING_DATE P_20100206_OTHERS NUMBER 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 SEQ_ID P_20100131_GROT NUMBER SEQ_ID P_20100131_GROT NUMBER SEQ_ID P_20100131_HALO NUMBER SEQ_ID P_20100131_HALO NUMBER SEQ_ID P_20100131_JUNE NUMBER SEQ_ID P_20100131_JUNE NUMBER SEQ_ID P_20100131_OTHERS NUMBER SEQ_ID P_20100131_OTHERS NUMBER SEQ_ID P_20100201_GROT NUMBER SEQ_ID P_20100201_GROT NUMBER SEQ_ID P_20100201_HALO NUMBER SEQ_ID P_20100201_HALO NUMBER SEQ_ID P_20100201_JUNE NUMBER SEQ_ID P_20100201_JUNE NUMBER SEQ_ID P_20100201_OTHERS NUMBER SEQ_ID P_20100201_OTHERS NUMBER SEQ_ID P_20100202_GROT NUMBER SEQ_ID P_20100202_GROT NUMBER SEQ_ID P_20100202_HALO NUMBER SEQ_ID P_20100202_HALO NUMBER SEQ_ID P_20100202_JUNE NUMBER SEQ_ID P_20100202_JUNE NUMBER SEQ_ID P_20100202_OTHERS NUMBER SEQ_ID P_20100202_OTHERS NUMBER SEQ_ID P_20100203_GROT NUMBER SEQ_ID P_20100203_GROT NUMBER SEQ_ID P_20100203_HALO NUMBER SEQ_ID P_20100203_HALO NUMBER SEQ_ID P_20100203_JUNE NUMBER SEQ_ID P_20100203_JUNE NUMBER SEQ_ID P_20100203_OTHERS NUMBER SEQ_ID P_20100203_OTHERS NUMBER SEQ_ID P_20100204_GROT NUMBER SEQ_ID P_20100204_GROT NUMBER SEQ_ID P_20100204_HALO NUMBER SEQ_ID P_20100204_HALO NUMBER SEQ_ID P_20100204_JUNE NUMBER SEQ_ID P_20100204_JUNE NUMBER SEQ_ID P_20100204_OTHERS NUMBER SEQ_ID P_20100204_OTHERS NUMBER SEQ_ID P_20100205_GROT NUMBER SEQ_ID P_20100205_GROT NUMBER SEQ_ID P_20100205_HALO NUMBER SEQ_ID P_20100205_HALO NUMBER SEQ_ID P_20100205_JUNE NUMBER SEQ_ID P_20100205_JUNE NUMBER SEQ_ID P_20100205_OTHERS NUMBER SEQ_ID P_20100205_OTHERS NUMBER SEQ_ID P_20100206_GROT NUMBER SEQ_ID P_20100206_GROT NUMBER SEQ_ID P_20100206_HALO NUMBER SEQ_ID P_20100206_HALO NUMBER SEQ_ID P_20100206_JUNE NUMBER SEQ_ID P_20100206_JUNE NUMBER SEQ_ID P_20100206_OTHERS NUMBER SEQ_ID P_20100206_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 SOURCE_SYSTEM P_20100131_GROT VARCHAR2 SOURCE_SYSTEM P_20100131_GROT VARCHAR2 SOURCE_SYSTEM P_20100131_HALO VARCHAR2 SOURCE_SYSTEM P_20100131_HALO VARCHAR2 SOURCE_SYSTEM P_20100131_JUNE VARCHAR2 SOURCE_SYSTEM P_20100131_JUNE VARCHAR2 SOURCE_SYSTEM P_20100131_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100131_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100201_GROT VARCHAR2 SOURCE_SYSTEM P_20100201_GROT VARCHAR2 SOURCE_SYSTEM P_20100201_HALO VARCHAR2 SOURCE_SYSTEM P_20100201_HALO VARCHAR2 SOURCE_SYSTEM P_20100201_JUNE VARCHAR2 SOURCE_SYSTEM P_20100201_JUNE VARCHAR2 SOURCE_SYSTEM P_20100201_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100201_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100202_GROT VARCHAR2 SOURCE_SYSTEM P_20100202_GROT VARCHAR2 SOURCE_SYSTEM P_20100202_HALO VARCHAR2 SOURCE_SYSTEM P_20100202_HALO VARCHAR2 SOURCE_SYSTEM P_20100202_JUNE VARCHAR2 SOURCE_SYSTEM P_20100202_JUNE VARCHAR2 SOURCE_SYSTEM P_20100202_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100202_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100203_GROT VARCHAR2 SOURCE_SYSTEM P_20100203_GROT VARCHAR2 SOURCE_SYSTEM P_20100203_HALO VARCHAR2 SOURCE_SYSTEM P_20100203_HALO VARCHAR2 SOURCE_SYSTEM P_20100203_JUNE VARCHAR2 SOURCE_SYSTEM P_20100203_JUNE VARCHAR2 SOURCE_SYSTEM P_20100203_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100203_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100204_GROT VARCHAR2 SOURCE_SYSTEM P_20100204_GROT VARCHAR2 SOURCE_SYSTEM P_20100204_HALO VARCHAR2 SOURCE_SYSTEM P_20100204_HALO VARCHAR2 SOURCE_SYSTEM P_20100204_JUNE VARCHAR2 SOURCE_SYSTEM P_20100204_JUNE VARCHAR2 SOURCE_SYSTEM P_20100204_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100204_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100205_GROT VARCHAR2 SOURCE_SYSTEM P_20100205_GROT VARCHAR2 SOURCE_SYSTEM P_20100205_HALO VARCHAR2 SOURCE_SYSTEM P_20100205_HALO VARCHAR2 SOURCE_SYSTEM P_20100205_JUNE VARCHAR2 SOURCE_SYSTEM P_20100205_JUNE VARCHAR2 SOURCE_SYSTEM P_20100205_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100205_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100206_GROT VARCHAR2 SOURCE_SYSTEM P_20100206_GROT VARCHAR2 SOURCE_SYSTEM P_20100206_HALO VARCHAR2 SOURCE_SYSTEM P_20100206_HALO VARCHAR2 SOURCE_SYSTEM P_20100206_JUNE VARCHAR2 SOURCE_SYSTEM P_20100206_JUNE VARCHAR2 SOURCE_SYSTEM P_20100206_OTHERS VARCHAR2 SOURCE_SYSTEM P_20100206_OTHERS VARCHAR2 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 STATUS P_20100131_GROT VARCHAR2 STATUS P_20100131_GROT VARCHAR2 STATUS P_20100131_HALO VARCHAR2 STATUS P_20100131_HALO VARCHAR2 STATUS P_20100131_JUNE VARCHAR2 STATUS P_20100131_JUNE VARCHAR2 STATUS P_20100131_OTHERS VARCHAR2 STATUS P_20100131_OTHERS VARCHAR2 STATUS P_20100201_GROT VARCHAR2 STATUS P_20100201_GROT VARCHAR2 STATUS P_20100201_HALO VARCHAR2 STATUS P_20100201_HALO VARCHAR2 STATUS P_20100201_JUNE VARCHAR2 STATUS P_20100201_JUNE VARCHAR2 STATUS P_20100201_OTHERS VARCHAR2 STATUS P_20100201_OTHERS VARCHAR2 STATUS P_20100202_GROT VARCHAR2 STATUS P_20100202_GROT VARCHAR2 STATUS P_20100202_HALO VARCHAR2 STATUS P_20100202_HALO VARCHAR2 STATUS P_20100202_JUNE VARCHAR2 STATUS P_20100202_JUNE VARCHAR2 STATUS P_20100202_OTHERS VARCHAR2 STATUS P_20100202_OTHERS VARCHAR2 STATUS P_20100203_GROT VARCHAR2 STATUS P_20100203_GROT VARCHAR2 STATUS P_20100203_HALO VARCHAR2 STATUS P_20100203_HALO VARCHAR2 STATUS P_20100203_JUNE VARCHAR2 STATUS P_20100203_JUNE VARCHAR2 STATUS P_20100203_OTHERS VARCHAR2 STATUS P_20100203_OTHERS VARCHAR2 STATUS P_20100204_GROT VARCHAR2 STATUS P_20100204_GROT VARCHAR2 STATUS P_20100204_HALO VARCHAR2 STATUS P_20100204_HALO VARCHAR2 STATUS P_20100204_JUNE VARCHAR2 STATUS P_20100204_JUNE VARCHAR2 STATUS P_20100204_OTHERS VARCHAR2 STATUS P_20100204_OTHERS VARCHAR2 STATUS P_20100205_GROT VARCHAR2 STATUS P_20100205_GROT VARCHAR2 STATUS P_20100205_HALO VARCHAR2 STATUS P_20100205_HALO VARCHAR2 STATUS P_20100205_JUNE VARCHAR2 STATUS P_20100205_JUNE VARCHAR2 STATUS P_20100205_OTHERS VARCHAR2 STATUS P_20100205_OTHERS VARCHAR2 STATUS P_20100206_GROT VARCHAR2 STATUS P_20100206_GROT VARCHAR2 STATUS P_20100206_HALO VARCHAR2 STATUS P_20100206_HALO VARCHAR2 STATUS P_20100206_JUNE VARCHAR2 STATUS P_20100206_JUNE VARCHAR2 STATUS P_20100206_OTHERS VARCHAR2 STATUS P_20100206_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 320 rows selected. SQL> SQL> select sysdate from dual; SYSDATE -------------------- 22-APR-2010 11:24:16 SQL> SQL> SPOOL OFF