you can use the job queues or scheduler to do this.
here is an example using the job queues. Just have your process routine accept a pair of partition keys that select out data for ONE partition (we'll eliminate the others and do the right thing). do not be tempted to use dynamic sql and the extended partition name, that'll just make the code really ugly.
ops$tkyte%ORA10GR2> CREATE TABLE t
2 (
3 dt date,
4 x int,
5 y varchar2(30)
6 )
7 PARTITION BY RANGE (dt)
8 (
9 PARTITION part1 VALUES LESS THAN (to_date('13-mar-2008','dd-mon-yyyy')) ,
10 PARTITION part2 VALUES LESS THAN (to_date('14-mar-2008','dd-mon-yyyy')) ,
11 PARTITION part3 VALUES LESS THAN (to_date('15-mar-2008','dd-mon-yyyy'))
12 )
13 /
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t
2 select to_date('12-mar-2008','dd-mon-yyyy')+mod(rownum,3), object_id, object_name
3 from all_objects
4 /
49924 rows created.
ops$tkyte%ORA10GR2> select 'part1', count(*) from t partition(part1) union all
2 select 'part2', count(*) from t partition(part2) union all
3 select 'part3', count(*) from t partition(part2);
'PART COUNT(*)
----- ----------
part1 16641
part2 16642
part3 16642
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table job_parms
2 ( job number primary key,
3 lodate date,
4 hidate date,
5 scheduled date,
6 completed date
7 ) organization index;
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure process_data( p_job in number )
2 as
3 l_rec job_parms%rowtype;
4 begin
5 select * into l_rec from job_parms where job = p_job;
6
7 for x in (select rowid rid, y from t where dt >= l_rec.lodate and dt < l_rec.hidate)
8 loop
9 update t set y = lower(x.y) where rowid = x.rid;
10 end loop;
11
12 update job_parms set completed = sysdate where job = p_job;
13 commit;
14 end;
15 /
Procedure created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure submit_jobs
2 as
3 l_job number;
4 l_dt date := sysdate;
5 l_n number;
6 begin
7 for i in 1 .. 3
8 loop
9 dbms_job.submit( l_job, 'process_data( JOB );' );
10 insert into job_parms (job,lodate,hidate,scheduled)
11 values ( l_job,
12 to_date('12-mar-2008','dd-mon-yyyy')+i-1,
13 to_date('12-mar-2008','dd-mon-yyyy')+i,
14 l_dt );
15 end loop;
16
17 commit; -- they can run now....
18 loop
19 dbms_lock.sleep(3);
20 select count(*) into l_n from job_parms where scheduled = l_dt and completed is null and rownum = 1;
21 exit when (l_n = 0);
22 end loop;
23 end;
24 /
Procedure created.
ops$tkyte%ORA10GR2> show parameter job_queue
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 10
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select count( case when lower(y) = y then 1 end ) already_lower, count(*) from t;
ALREADY_LOWER COUNT(*)
------------- ----------
890 49924
ops$tkyte%ORA10GR2> exec submit_jobs
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> select count( case when lower(y) = y then 1 end ) now_lower, count(*) from t;
NOW_LOWER COUNT(*)
---------- ----------
49924 49924
ops$tkyte%ORA10GR2> select * from job_parms;
JOB LODATE HIDATE SCHEDULED COMPLETED
---------- -------------------- -------------------- -------------------- --------------------
248 12-mar-2008 00:00:00 13-mar-2008 00:00:00 25-jun-2008 09:32:14 25-jun-2008 09:32:21
249 13-mar-2008 00:00:00 14-mar-2008 00:00:00 25-jun-2008 09:32:14 25-jun-2008 09:32:21
250 14-mar-2008 00:00:00 15-mar-2008 00:00:00 25-jun-2008 09:32:14 25-jun-2008 09:32:21
I liked this question :) A nice "how do I do this" question ....