How to Create Partition Table in Oracle Schema and Add Partition in Table.

Step 1: Connect schema using sql plus

sql> scott/tiger;

Step 2: Create table

sql> create table employee
       (
        empcode number,
        empname number,
        joindate date,
        joined_year number(4)
       )
       tablespace system
       pctused 40
       pctfree 10
       initrans 1
       maxtrans 255
       logging
       partition by range (joined_year)
       (
        partition employee2014 values less than (2015)
        logging
        nocompress
        tablespace system
        pctused 0
        pctfree 20
        initrans 1
        maxtrans 255
        storage (
        initial 64K
        minextents 1
        maxextents 2147483645
        freelists 1
        freelist groups 1
        buffer_pool default
        )
       );

Step 3: Create Procedure

sql> create or replace procedure proc_gen_partition(p_year number) as
       cursor cur_gen_par is
       select distinct object_name from all_objects
        where owner in ('SCOTT')
        and object_name is not null
        and object_type = 'TABLE PARTITION';

        v_objname varchar2(100);
        v_qry varchar2(4000);
        v_error varchar2(4000);
        v_partname varchar2(100);
        v_maxbal number;

       begin
         open cur_gen_par;
         loop
            fetch cur_gen_par into var_objname;
            exit when cur_gen_par%notfound;
         begin
            v_partname := var_objname||p_year;
            v_maxval := p_year+1;
            dbms_output.put_line       (var_objname||p_year);
            v_qry := 'alter table '||        var_objname||' add partition
            '||v_partname||' values less than ('||to_char(p_year+1)||') tablespace system';

            execute immediate v_qry;
            exception
            when others then
            v_error := sqlerrm;
           dbms_output.put_line('Partiton already created for '||v_partname);
          end;
         end loop;
         close cur_gen_par;
         exception
          when others then
          v_error := sqlerrm;
         end;

Step 4: Execute Procedure

sql> exec proc_gen_partition(2015);

Step 5: Confirm that Partition created successfully

sql>select partition_name
       from user_tab_partitions
       where lower(table_name) = 'employee';