Partition

1-Show Partition Information in Partition Table
2-procedure to add partition into partition table
3-Add partition to partition table
4-select data from a partition in a table
5-move partition from one tablespace to another tablespace

---

1-Show Partition Information in Partition Table
select * from dba_tab_partitions
where table_name='xxxxx'


2-procedure to add partition into partition table

create or replace procedure prc_add_partition IS

PName varchar2(30);
ldate varchar2(30);
str_create varchar2(500);
counter number;

begin

PName :='';
lDate := '';
str_create := '';
counter := 0;



  for  i in 366..456 loop
    select 'TBL_p_1_PP' || i  into PName from dual;

      begin

        PName:= PName;

        select to_char(sysdate + i-367,'DD-MM-YYYY') into lDate from dual;

        str_create := 'ALTER TABLE TBLDATA ADD PARTITION  ' ||
                         PName || ' VALUES LESS THAN (to_date(''' ||
                         lDate ||
                         ''',  ''DD-MM-YYYY'')) TABLESPACE tablespaceName';


        execute immediate str_Create;
       counter := counter +1;
       end;

    end loop;

end prc_add_partition;

3-Add partition to partition table

ALTER TABLE TBL_cc ADD PARTITION TBL_cc_DATA_2013701 VALUES LESS THAN ('02-dec-2014')
TABLESPACE xx_REPORT;


ALTER TABLE TBL_cc ADD PARTITION TBL_cc_DATA_2013706
VALUES LESS THAN (To_date('12/7/2014','mm/dd/yyyy')) TABLESPACE xx_REPORT;

4-select data from a partition in a table

select * from tbl01
partition(partition_01)

5-move partition from one tablespace to another tablespace

Find below sql to move partition from one tablespace to another one

SYNTAX: ALTER TABLE TBL_NAME MOVE PARTITION tbl_part_name TABLESPACE new_tablespace;

ALTER table tab1 move partition part_OVERFLOW tablespace tab_REORG ;

If you face PCTFREE + PCTUSED < 100, use below sql.

ALTER TABLE tab1  MODIFY PARTITION part_OVERFLOW PCTFREE 10; >> this is default in Oracle

ALTER TABLE tab1  MODIFY PARTITION part_OVERFLOW  PCTUSED 40; >> this is default in Oracle