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';
