oracle 10g 分区管理,Oracle 10g自动表分区

create table test(x int)

partition by range(x)

(

partition part_100 values less than(101),

partition part_200 values less than(201),

partition part_other values less than(maxvalue)

);

--insert--

insert into test

select level from dual

connect by level<=200;

insert into test

select * from (

select level id

from dual

connect by level<=1000

)

where id between 300 and 999;

--job demo--

declare

v_pname user_tab_partitions.partition_name%type;

v_part_maxval test.x%type;

type tab_type is table of test.x%type

index by pls_integer;

v_cnt tab_type;

begin

select partition_name

into v_pname

from user_tab_partitions

where table_name='TEST' and

partition_position=

(

select max(partition_position)-1

from user_tab_partitions

where table_name='TEST'

);

execute immediate 'select max(x) from test partition('

||v_pname

||')'

into v_part_maxval;

select distinct round(x/100)

bulk collect into v_cnt

from test

where x>v_part_maxval order by 1;

for i in 1..v_cnt.count loop

dbms_output.put_line(v_cnt(i));

execute immediate 'ALTER TABLE test

SPLIT PARTITION part_other AT ('

||v_cnt(i)

||'01)'

||'INTO (PARTITION part_'

||v_cnt(i)

||'00,PARTITION part_other)

UPDATE GLOBAL INDEXES';

end loop;

end;