DROP TABLE RPTMGR.test2 PURGE;

-- ---------- ---------- ----------
-- Create second test table 
-- WITHOUT interval partitioning
-- ---------- ---------- ----------
CREATE TABLE RPTMGR.test2
(  
  aa   DATE NOT NULL,
  bb   NUMBER NOT NULL
) 
PCTFREE 5 
PCTUSED 40 
NOCOMPRESS  
NOLOGGING 
PARALLEL 4
TABLESPACE rptmgr_tbl 
PARTITION BY RANGE ( aa ) 
SUBPARTITION BY LIST ( bb ) 
SUBPARTITION TEMPLATE ( 
    SUBPARTITION sp_odd     VALUES ( 1,3,5,7,9 )  TABLESPACE RPTMGR_TBL , 
    SUBPARTITION sp_even    VALUES ( 0,2,4,6,8 )  TABLESPACE RPTMGR_TBL , 
    SUBPARTITION sp_others  VALUES ( DEFAULT )    TABLESPACE RPTMGR_TBL  ) 
( PARTITION p_2008_10  VALUES LESS THAN ( TO_DATE( ' 2008-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN' ) ) 
PCTFREE 5 PCTUSED 40 TABLESPACE rptmgr_tbl NOCOMPRESS );



-- ---------- ---------- ----------
-- Attempt to put data in not in the above partition
-- ---------- ---------- ----------
INSERT INTO test2 VALUES ( TO_DATE ( '2009-10-01', 'YYYY-MM-DD' ), 1 );
--            *
-- ERROR at line 1:
-- ORA-14400: inserted partition key does not map to any partition


-- ---------- ---------- ----------
-- Insert data into the above partition
-- ---------- ---------- ----------
INSERT INTO test2 VALUES ( TO_DATE ( '2008-10-01', 'YYYY-MM-DD' ), 1 );
COMMIT;
EXEC DBMS_STATS.gather_table_stats( 'RPTMGR', 'TEST2', null, 100, false, 'FOR ALL COLUMNS', 4, 'ALL' );



-- ---------- ---------- ----------
— Where is it?
-- ---------- ---------- ----------
SET LONG 1000
COLUMN high_value         FORMAT A40 HEADING 'High Value' WORD_WRAPPED
COLUMN partition_name     FORMAT A9  HEADING 'Partition|Name'
COLUMN subpartition_name  FORMAT A19 HEADING 'Sub-Partition|Name'
COLUMN tablespace_name    FORMAT A10 HEADING 'Tablespace|Name'
COLUMN interval           FORMAT A8  HEADING 'Interval'
COLUMN num_rows           FORMAT 999 HEADING 'Row|Count'

SELECT 
  partition_name, 
  tablespace_name, 
  high_value, 
  interval,
  num_rows
FROM 
  user_tab_partitions
WHERE 
  table_name = 'TEST2';

-- Partition Tablespace                                                     Row
-- Name      Name       High Value                               Interval Count
-- --------- ---------- ---------------------------------------- -------- -----
-- P_2008_10 RPTMGR_TBL TO_DATE(' 2008-11-01 00:00:00',          NO           1
--                      'SYYYY-MM-DD HH24:MI:SS',
--                      'NLS_CALENDAR=GREGORIAN')



-- ---------- ---------- ----------
COLUMN high_value         FORMAT A12 HEADING 'High Value'

SELECT 
  partition_name, 
  subpartition_name, 
  high_value, 
  interval,
  TO_NUMBER ( DECODE ( num_rows, 0, NULL, num_rows ) ) AS num_rows
FROM 
  user_tab_subpartitions 
WHERE 
  table_name = 'TEST2';

-- Partition Sub-Partition                                                           Row
-- Name      Name                High Value                               Interval Count
-- --------- ------------------- ---------------------------------------- -------- -----
-- P_2008_10 P_2008_10_SP_ODD    1, 3, 5, 7, 9                            NO           1
-- P_2008_10 P_2008_10_SP_EVEN   0, 2, 4, 6, 8                            NO
-- P_2008_10 P_2008_10_SP_OTHERS DEFAULT                                  NO




-- ---------- ---------- ----------
-- Turn on INTERVAL PARTITIONING by month
-- ---------- ---------- ----------
ALTER TABLE test2 SET INTERVAL ( NUMTOYMINTERVAL ( 1, 'MONTH' ) );



-- ---------- ---------- ----------
-- Insert data far in the future
-- ---------- ---------- ----------
INSERT INTO test2 VALUES ( TO_DATE ( '2010-10-01', 'YYYY-MM-DD' ), 4 );
COMMIT;
EXEC DBMS_STATS.gather_table_stats( 'RPTMGR', 'TEST2', null, 100, false, 'FOR ALL COLUMNS', 4, 'ALL' );



-- ---------- ---------- ----------
-- Where is our new data?
-- ---------- ---------- ----------
SELECT 
  partition_name, 
  tablespace_name, 
  high_value, 
  interval,
  num_rows
FROM 
  user_tab_partitions
WHERE 
  table_name = 'TEST2';

-- Partition Tablespace                                                     Row
-- Name      Name       High Value                               Interval Count
-- --------- ---------- ---------------------------------------- -------- -----
-- P_2008_10 RPTMGR_TBL TO_DATE(' 2008-11-01 00:00:00',          NO           1
--                      'SYYYY-MM-DD HH24:MI:SS',
--                      'NLS_CALENDAR=GREGORIAN')
-- 
-- SYS_P44   RPTMGR_TBL TO_DATE(' 2010-11-01 00:00:00',          YES          1
--                      'SYYYY-MM-DD HH24:MI:SS',
--                      'NLS_CALENDAR=GREGORIAN')


-- ---------- ---------- ----------
SELECT 
  partition_name, 
  subpartition_name, 
  high_value, 
  interval,
  TO_NUMBER ( DECODE ( num_rows, 0, NULL, num_rows ) ) AS num_rows
FROM 
  user_tab_subpartitions 
WHERE 
  table_name = 'TEST2';

-- Partition Sub-Partition                                                           Row
-- Name      Name                High Value                               Interval Count
-- --------- ------------------- ---------------------------------------- -------- -----
-- P_2008_10 P_ODD              1, 3, 5, 7, 9                            NO           1
-- P_2008_10 P_EVEN             0, 2, 4, 6, 8                            NO
-- P_2008_10 P_OTHERS DEFAULT                                            NO
-- SYS_P44   SYS_SUBP41          1, 3, 5, 7, 9                            NO
-- SYS_P44   SYS_SUBP42          0, 2, 4, 6, 8                            NO           1
-- SYS_P44   SYS_SUBP43          DEFAULT                                  NO



DROP TABLE RPTMGR.test2 PURGE;