分段表rowid_rowid分区分段提交

drop type get_type;

/

drop type v_get_type;

/

create  type get_type as object(v_sta varchar2(40),v_end varchar2(40));

/

create  type v_get_type is table of get_type;

/

create or replace function get_rowid(v_owner in varchar2,v_tab_name in varchar2,v_row in number)

return v_get_type

pipelined is

r_get_type get_type :=get_type(null,null);

v_pie number;

V_ALL NUMBER;

begin

EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM  '||UPPER(v_owner)||'.'||UPPER(v_tab_name) INTO V_ALL;

v_pie := CEIL(V_ALL/v_row);

for x in (

select dbms_rowid.rowid_create

( 1, data_object_id, lo_fno, lo_block, 0 ) min_rid,

dbms_rowid.rowid_create

( 1, data_object_id, hi_fno, hi_block, 10000 ) max_rid

from (

select distinct grp,

first_value(relative_fno)

over (partition by grp order by relative_fno, block_id

rows between unbounded preceding and unbounded following) lo_fno,

first_value(block_id )

over (partition by grp order by relative_fno, block_id

rows between unbounded preceding and unbounded following) lo_block,

last_value(relative_fno)

over (partition by grp order by relative_fno, block_id

rows between unbounded preceding and unbounded following) hi_fno,

last_value(block_id+blocks-1)

over (partition by grp order by relative_fno, block_id

rows between unbounded preceding and unbounded following) hi_block,

sum(blocks) over (partition by grp) sum_blocks

from (

select relative_fno,

block_id,

blocks,

trunc( (sum(blocks) over (order by relative_fno, block_id)-0.01) /

(sum(blocks) over ()/v_pie) ) grp

from dba_extents

where segment_name = upper(v_tab_name)

and owner = upper(v_owner) order by block_id

)

),

(select data_object_id

from DBA_OBJECTS where object_name = upper(v_tab_name)

and owner = upper(v_owner)

)

)

loop

r_get_type.v_sta :=x.min_rid  ;

r_get_type.v_end :=x.max_rid;

PIPE ROW(r_get_type);

end loop;

return;

end;

/

select * from table(get_rowid('SYS','GH1',40000));

create table nb as

select * from table(get_rowid('SYS','GH1',40000));

select count(1) from gh1

,nb

where gh1.rowid between v_sta and v_end ;

这些天网友经常问大表如何高效的分片delete，分片update等问题，主要是针对大表的dml分段，现在根据tom的脚本编写了个函数并范例如上，贡献给各位网友。