Thursday, November 20, 2014

Partitions

Select partition list for table

select * from dba_tab_partitions
where 
table_owner='OWNER' and table_name = 'TABLE_NAME'

Select min/max date in partition 

select min(trx_date),max(trx_date) from TABLE_NAME partition (SYS_P25020)

Split partition base on date

ALTER TABLE P_TRX_TICKETS
    SPLIT PARTITION FOR(TO_DATE('01-MAY-2014','dd-MON-yyyy'))
    AT (TO_DATE('01-May-2014','dd-MON-yyyy'))
    INTO (PARTITION p_trx_12014,PARTITION p_trx_1a2014);

Split partition using partition name

  ALTER TABLE xms.P_TRX_TICKETS
    SPLIT PARTITION SYS_P25020
    AT (TO_DATE('01-May-2014','dd-MON-yyyy'))
    INTO (PARTITION tic_042014,PARTITION tic_05_062014);

Links
Oracle Introduction to Partitioning