Database Reference
In-Depth Information
11 first_value(block_id )
over (partition by grp order by relative_fno, block_id
12 rows between unbounded preceding and unbounded following) lo_block,
13 last_value(relative_fno)
over (partition by grp order by relative_fno, block_id
14 rows between unbounded preceding and unbounded following) hi_fno,
15 last_value(block_id+blocks-1)
over (partition by grp order by relative_fno, block_id
16 rows between unbounded preceding and unbounded following) hi_block,
17 sum(blocks) over (partition by grp) sum_blocks
18 from (
19 select relative_fno,
20 block_id,
21 blocks,
22 trunc( (sum(blocks) over (order by relative_fno, block_id)-0.01) /
23 (sum(blocks) over ()/8) ) grp
24 from dba_extents
25 where segment_name = upper('BIG_TABLE')
26 and owner = user order by block_id
27 )
28 ),
29 (select data_object_id
from user_objects where object_name = upper('BIG_TABLE') )
30 )
31 loop
32 dbms_job.submit( l_job, 'serial(JOB);' );
33 insert into job_parms(job, lo_rid, hi_rid)
34 values ( l_job, x.min_rid, x.max_rid );
35 end loop;
36 end;
37 /
PL/SQL procedure successfully completed.
This PL/SQL block would have scheduled up to eight jobs for us (fewer, if the table could not be broken into
eight pieces due to insufficient extents or size). We can see how many jobs were scheduled and what their inputs were
as follows:
EODA@ORA12CR1> select * from job_parms;
JOB LO_RID HI_RID
---------- ------------------ ------------------
1 AAAEzwAAEAAABKAAAA AAAEzwAAEAAABl/CcQ
2 AAAEzwAAEAAACyAAAA AAAEzwAAEAAADR/CcQ
3 AAAEzwAAEAAAAuAAAA AAAEzwAAEAAABJ/CcQ
4 AAAEzwAAEAAACCAAAA AAAEzwAAEAAACR/CcQ
5 AAAEzwAAEAAADSAAAA AAAEzwAAEAABGUnCcQ
6 AAAEzwAAEAAAAQoAAA AAAEzwAAEAAAAt/CcQ
7 AAAEzwAAEAAABmAAAA AAAEzwAAEAAACB/CcQ
8 AAAEzwAAEAAACSAAAA AAAEzwAAEAAACx/CcQ
8 rows selected.
EODA@ORA12CR1> commit;
Commit complete.
