-- as SYS
create tablespace TBLSPC_01 datafile 'D:\_Oracle_\app\oradata\DWHTEST\tblspc_01.dbf' size 2M autoextend on next 2M maxsize 1G extent management local segment space management auto;
create tablespace TBLSPC_02 datafile 'D:\_Oracle_\app\oradata\DWHTEST\tblspc_02.dbf' size 2M autoextend on next 2M maxsize 1G extent management local segment space management auto;
create tablespace TBLSPC_03 datafile 'D:\_Oracle_\app\oradata\DWHTEST\tblspc_03.dbf' size 2M autoextend on next 2M maxsize 1G extent management local segment space management auto;
create tablespace TBLSPC_04 datafile 'D:\_Oracle_\app\oradata\DWHTEST\tblspc_04.dbf' size 2M autoextend on next 2M maxsize 1G extent management local segment space management auto;
create tablespace TBLSPC_05 datafile 'D:\_Oracle_\app\oradata\DWHTEST\tblspc_05.dbf' size 2M autoextend on next 2M maxsize 1G extent management local segment space management auto;
create tablespace TBLSPC_06 datafile 'D:\_Oracle_\app\oradata\DWHTEST\tblspc_06.dbf' size 2M autoextend on next 2M maxsize 1G extent management local segment space management auto;
create tablespace TBLSPC_07 datafile 'D:\_Oracle_\app\oradata\DWHTEST\tblspc_07.dbf' size 2M autoextend on next 2M maxsize 1G extent management local segment space management auto;
create tablespace TBLSPC_08 datafile 'D:\_Oracle_\app\oradata\DWHTEST\tblspc_08.dbf' size 2M autoextend on next 2M maxsize 1G extent management local segment space management auto;
create tablespace TBLSPC_09 datafile 'D:\_Oracle_\app\oradata\DWHTEST\tblspc_09.dbf' size 2M autoextend on next 2M maxsize 1G extent management local segment space management auto;
create tablespace TBLSPC_10 datafile 'D:\_Oracle_\app\oradata\DWHTEST\tblspc_10.dbf' size 2M autoextend on next 2M maxsize 1G extent management local segment space management auto;
create tablespace TBLSPC_11 datafile 'D:\_Oracle_\app\oradata\DWHTEST\tblspc_11.dbf' size 2M autoextend on next 2M maxsize 1G extent management local segment space management auto;
create tablespace TBLSPC_12 datafile 'D:\_Oracle_\app\oradata\DWHTEST\tblspc_12.dbf' size 2M autoextend on next 2M maxsize 1G extent management local segment space management auto;

alter user dejan 
quota 1500M on TBLSPC_01
quota 1500M on TBLSPC_02 
quota 1500M on TBLSPC_03 
quota 1500M on TBLSPC_04 
quota 1500M on TBLSPC_05 
quota 1500M on TBLSPC_06 
quota 1500M on TBLSPC_07 
quota 1500M on TBLSPC_08 
quota 1500M on TBLSPC_09 
quota 1500M on TBLSPC_10 
quota 1500M on TBLSPC_11 
quota 1500M on TBLSPC_12;


--------

set lines 250 pages 30 trimspool on
spool interval_partitioning.txt

drop table history purge;

CREATE TABLE history
  (
    hist_date DATE DEFAULT SYSDATE NOT NULL,
    hist_type varchar2(1) default 'K' CONSTRAINT ck_hist_type CHECK (hist_type in('K', 'D')) not null, 
    hist_message varchar2(128)
  )
  PARTITION BY RANGE (hist_date) 
  SUBPARTITION BY LIST (hist_type)
  (
    PARTITION P201201 VALUES LESS THAN (TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
    ( SUBPARTITION SP201201_K VALUES ('K')
    , SUBPARTITION SP201201_D VALUES ('D')
    ),
    PARTITION P201202 VALUES LESS THAN (TO_DATE(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
    ( SUBPARTITION SP201202_K VALUES ('K')
    , SUBPARTITION SP201202_D VALUES ('D')
    ),
    PARTITION P201203 VALUES LESS THAN (TO_DATE(' 2012-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
    ( SUBPARTITION SP201203_K VALUES ('K')
    , SUBPARTITION SP201203_D VALUES ('D')
    ),
    PARTITION P201204 VALUES LESS THAN (TO_DATE(' 2012-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
    ( SUBPARTITION SP201204_K VALUES ('K')
    , SUBPARTITION SP201204_D VALUES ('D')
    ),
    PARTITION P201205 VALUES LESS THAN (TO_DATE(' 2012-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
    ( SUBPARTITION SP201205_K VALUES ('K')
    , SUBPARTITION SP201205_D VALUES ('D')
    ),
    PARTITION P201206 VALUES LESS THAN (TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
    ( SUBPARTITION SP201206_K VALUES ('K')
    , SUBPARTITION SP201206_D VALUES ('D')
    ),
    PARTITION P201207 VALUES LESS THAN (TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
    ( SUBPARTITION SP201207_K VALUES ('K')
    , SUBPARTITION SP201207_D VALUES ('D')
    ),
    PARTITION P201208 VALUES LESS THAN (TO_DATE(' 2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
    ( SUBPARTITION SP201208_K VALUES ('K')
    , SUBPARTITION SP201208_D VALUES ('D')
    ),
    PARTITION P201209 VALUES LESS THAN (TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
    ( SUBPARTITION SP201209_K VALUES ('K')
    , SUBPARTITION SP201209_D VALUES ('D')
    ),
    PARTITION P201210 VALUES LESS THAN (TO_DATE(' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
    ( SUBPARTITION SP201210_K VALUES ('K')
    , SUBPARTITION SP201210_D VALUES ('D')
    ),
    PARTITION P201211 VALUES LESS THAN (TO_DATE(' 2012-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
    ( SUBPARTITION SP201211_K VALUES ('K')
    , SUBPARTITION SP201211_D VALUES ('D')
    ),
    PARTITION P201212 VALUES LESS THAN (TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
    ( SUBPARTITION SP201212_K VALUES ('K')
    , SUBPARTITION SP201212_D VALUES ('D')
    ),
    PARTITION P_MAX VALUES LESS THAN (MAXVALUE) 
    ( SUBPARTITION SPMAX_K VALUES ('K')
    , SUBPARTITION SPMAX_D VALUES ('D')
    )
  )
  ENABLE ROW MOVEMENT
  COMPRESS FOR ALL OPERATIONS;

insert /*+ APPEND */ into HISTORY
select  TO_DATE('01.01.2012', 'DD.MM.RRRR') + dbms_random.value(1,365) as hist_date,
       case when mod(rownum, 5) = 0 then 'D'
            else 'K'
        end as hist_type,
        dbms_random.string('A', 50) as hist_message
  from dual connect by level <=500;

commit;

insert into HISTORY 
values (to_date('01.01.2013', 'DD.MM.YYYY'), 'K', dbms_random.string('A', 50));

commit;

col partition_name for a10
col tblspc for a10
select partition_position as p#, partition_name, subpartition_count as subcnt, high_value, tablespace_name as tblspc
from user_tab_partitions
where table_name = 'HISTORY'
order by partition_position;

col partition_name for a10
col hv for a5
col tblspc for a10
select subpartition_position as sub#, partition_name, subpartition_name, high_value as hv, tablespace_name as tblspc
from user_tab_subpartitions
where table_name = 'HISTORY'
order by partition_name, subpartition_position;

alter table HISTORY
SET INTERVAL (NUMTOYMINTERVAL(1,'MONTH'));

select TO_CHAR(TRUNC(add_months(max(hist_date), +1), 'MONTH'), 'RRRR-MM-DD HH24:MI:SS') 
       as split_at 
 from HISTORY partition (p_max);

ALTER TABLE HISTORY SPLIT PARTITION P_MAX 
      AT (TIMESTAMP'2013-02-01 00:00:00') INTO (PARTITION P201301, PARTITION P_MAX);

ALTER TABLE HISTORY drop PARTITION p_max;

alter table HISTORY
SET INTERVAL (NUMTOYMINTERVAL(1,'MONTH'));

alter table HISTORY
set SUBPARTITION TEMPLATE
  (SUBPARTITION SP_K VALUES ('K'),
   SUBPARTITION SP_D VALUES ('D')
  ); 

alter table HISTORY set STORE IN (TBLSPC_01, TBLSPC_02, TBLSPC_03, TBLSPC_04, 
                TBLSPC_05, TBLSPC_06, TBLSPC_07, TBLSPC_08, 
                TBLSPC_09, TBLSPC_10, TBLSPC_11, TBLSPC_12);
                
insert into HISTORY
values(TO_DATE('15.01.2013', 'DD.MM.RRRR'), 'K', dbms_random.string('A', 50));

insert into HISTORY
values(TO_DATE('15.02.2013', 'DD.MM.RRRR'), 'D', dbms_random.string('A', 50));

commit;

col partition_name for a10
col tblspc for a10
select partition_position as p#, partition_name, subpartition_count as subcnt, high_value, tablespace_name as tblspc
from user_tab_partitions
where table_name = 'HISTORY'
order by partition_position;

col partition_name for a10
col hv for a5
col tblspc for a10
select subpartition_position as sub#, partition_name, subpartition_name, high_value as hv, tablespace_name as tblspc
from user_tab_subpartitions
where table_name = 'HISTORY'
order by partition_name, subpartition_position;


insert into HISTORY
select ADD_MONTHS(TO_DATE('01.11.2012', 'DD.MM.RRRR'), LEVEL), 'K', dbms_random.string('A', 50)
from dual
connect by LEVEL <= 24;

commit;

col partition_name for a10
col tblspc for a10
select partition_position as p#, partition_name, subpartition_count as subcnt, high_value, tablespace_name as tblspc
from user_tab_partitions
where table_name = 'HISTORY'
order by partition_position;

col partition_name for a10
col hv for a5
col tblspc for a10
select subpartition_position as sub#, partition_name, subpartition_name, high_value as hv, tablespace_name as tblspc
from user_tab_subpartitions
where table_name = 'HISTORY'
order by partition_name, subpartition_position;

alter table HISTORY drop partition for (to_date('01.01.2012', 'DD.MM.YYYY'));

alter table HISTORY drop subpartition for (to_date('01.02.2012', 'DD.MM.YYYY'), 'D');

create index idx_hist_type on HISTORY(hist_type);

SELECT index_name, status 
  from user_indexes  
 where index_name='IDX_HIST_TYPE';

alter table HISTORY
   drop subpartition for (to_date('01.03.2012', 'DD.MM.YYYY'), 'D') ;

SELECT index_name, status 
  from user_indexes  
 where index_name='IDX_HIST_TYPE';
 
alter index idx_hist_type rebuild online;

SELECT index_name, status 
  from user_indexes  
 where index_name='IDX_HIST_TYPE';

alter table HISTORY
   drop subpartition for (to_date('01.04.2012', 'DD.MM.YYYY'), 'D')
     UPDATE GLOBAL INDEXES;
     
SELECT index_name, status 
  from user_indexes  
 where index_name='IDX_HIST_TYPE';

col partition_name for a10
col tblspc for a10
select partition_position as p#, partition_name, subpartition_count as subcnt, high_value, tablespace_name as tblspc
from user_tab_partitions
where table_name = 'HISTORY'
order by partition_position;

col partition_name for a10
col hv for a5
col tblspc for a10
select subpartition_position as sub#, partition_name, subpartition_name, high_value as hv, tablespace_name as tblspc
from user_tab_subpartitions
where table_name = 'HISTORY'
order by partition_name, subpartition_position;

spool off

