Partitioning

CREATE TABLE A157584.mpart
(
  ename  VARCHAR2(20),
  doj    DATE
)
PARTITION BY RANGE (doj)
INTERVAL( NumToDSInterval(7,'DAY'))
(  
  PARTITION WEEK_01 VALUES LESS than (to_date('07-JAN-2013','DD-MON-YYYY'))
        TABLESPACE USERS
);

select partition_name, high_value,tablespace_name from user_tab_partitions

insert into mpart values ('Joe','02-JAN-2013');
insert into mpart values ('Jac','08-JAN-2013');
insert into mpart values ('Tom','09-JAN-2013');
insert into mpart values ('Mac','15-JAN-2013');
insert into mpart values ('Kay','25-JAN-2013');
insert into mpart values ('Hay','25-FEB-2013');
insert into mpart values ('Kay','25-MAR-2013');

select * from mpart partition (sys_p66)

select * from mpart partition for (to_date('25-MAR-2013','DD-MON-YYYY')); 

alter table mpart drop partition sys_p65;

drop table order_line_items;

drop table orders;  

create table orders
(order# number primary key,
order_date date, 
data varchar2(30)
)
enable row movement
partition by range (order_date)
(
partition part_2009 values less than (to_date('01-01-2010','dd-mm-yyyy')),
partition part_2010 values less than (to_date('01-01-2011','dd-mm-yyyy'))
);

insert into orders values (1, to_date('01-jun-2009','dd-mon-yyyy'),'xxx');

insert into orders values (2, to_date('01-jun-2010','dd-mon-yyyy'),'xxx');

create table order_line_items
(order# number,
line# number,
data varchar2(30),
constraint c1_pk primary key (order#, line#),
constraint c1_fk_p foreign key (order#) references orders
)
enable row movement
partition by reference (c1_fk_p);

insert into order_line_items values (1,1,'yyy');

insert into order_line_items values (2,1,'yyy');

select table_name, partition_name
from user_tab_partitions
where table_name in ('ORDERS','ORDER_LINE_ITEMS')
order by table_name, partition_name

alter table orders drop partition part_2009 update global indexes;

alter table orders add partition part_2011 values less than (to_date('01-01-2012','dd-mm-yyyy'))

select '2010', count(*) from order_line_items partition (part_2010)
union all
select '2011', count(*) from order_line_items partition (part_2011)

update orders set order_date = add_months(order_date,12);

select '2010', count(*) from order_line_items partition (part_2010)
union all
select '2011', count(*) from order_line_items partition (part_2011)


ALTER TABLE a157584.ms_buffer_registry ADD PARTITION WEEKENDING_05_11_2013 VALUES LESS THAN (TO_DATE(' 2013-11-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE BS2_M;
    
alter table  a157584.ms_buffer_registry drop partition WEEKENDING_03_11_2013 update global indexes;
 
 select * from dba_tab_partitions where table_name = 'MS_BUFFER_REGISTRY'