Oracle Partition

Create a table which hold userdata
CREATE TABLE TXN
   (    TDATE DATE,
        START_DATE NUMBER,
        END_DATE NUMBER,
        PNUMBER NUMBER,
        ACODE CHAR(2)
   )


Insert data into the table with appx. 10000 recs per month for 1 year months can be increased/decreased using 0..11)
begin
for z in 0..11 loop
for i in 1..323 loop
  for j in 1..31 loop
insert into txn1 values(sysdate-433+j+(31*z),1212*i,2323232*i,122323*i,(select
decode(trunc(dbms_random.value(1,5)),1,'CN',2,'MD',3,'CO',4,'TR','CN') from dual));
end loop;
end loop;
end loop;
end;
/


Range_partition

 CREATE TABLE TXN_RANGE
( TDATE DATE,
        START_DATE NUMBER,
        END_DATE NUMBER,
        PNUMBER NUMBER,
        ACODE CHAR(2))
PARTITION BY RANGE (tdate)
(PARTITION p1 VALUES LESS THAN (TO_DATE('31/03/2015', 'DD/MM/YYYY')) TABLESPACE TEST,
 PARTITION p2 VALUES LESS THAN (TO_DATE('30/06/2015', 'DD/MM/YYYY')) TABLESPACE TEST,
 PARTITION p3 VALUES LESS THAN (TO_DATE('30/09/2015', 'DD/MM/YYYY')) TABLESPACE TEST,
 PARTITION p4 VALUES LESS THAN (TO_DATE('31/12/2015', 'DD/MM/YYYY')) TABLESPACE TEST);


List_partition
CREATE TABLE TXN_LIST
( TDATE DATE,
        START_DATE NUMBER,
        END_DATE NUMBER,
        PNUMBER NUMBER,
        ACODE CHAR(2))
PARTITION BY LIST (ACODE)
(
  PARTITION Chennai VALUES ('CN'),
  PARTITION madurai VALUES ('MD'),
  PARTITION Coimb VALUES ('CO'),
  PARTITION trichy VALUES ('TR')
  );


HASH_partition

CREATE TABLE TXN_HASH
(TDATE DATE,
        START_DATE NUMBER,
        END_DATE NUMBER,
        PNUMBER NUMBER,
        ACODE CHAR(2))
PARTITION BY HASH (TDATE)
PARTITIONS 4
STORE IN (TEST, TEST, TEST, TEST);


Composite  partition(Range-List)
CREATE TABLE TXN_RANGE_LIST
(TDATE DATE,
        START_DATE NUMBER,
        END_DATE NUMBER,
        PNUMBER NUMBER,
        ACODE CHAR(2))
PARTITION BY RANGE (TDATE)
SUBPARTITION BY LIST (ACODE)
SUBPARTITION TEMPLATE
(
  SUBPARTITION Chennai VALUES ('CN'),
  SUBPARTITION madurai VALUES ('MD'),
  SUBPARTITION Coimb VALUES ('CO'),
  SUBPARTITION trichy VALUES ('TR')
  )
(PARTITION p1 VALUES LESS THAN (TO_DATE('31/03/2015', 'DD/MM/YYYY')) TABLESPACE TEST,
 PARTITION p2 VALUES LESS THAN (TO_DATE('30/06/2015', 'DD/MM/YYYY')) TABLESPACE TEST,
 PARTITION p3 VALUES LESS THAN (TO_DATE('30/09/2015', 'DD/MM/YYYY')) TABLESPACE TEST,
 PARTITION p4 VALUES LESS THAN (MAXVALUE) TABLESPACE TEST);


To insert data use the below commad for all partitioned tables
insert into TXN_RANGE_LIST select * from txn1;




views :
dba_tab_partitions
select table_name,partition_name,num_rows,blocks,high_value from user_tab_partitions

dba_tab_subpartitions
select table_name,partition_name,subpartition_name,tablespace_name,num_rows from user_tab_subpartitions