oracle运用范围,Oracle范围分区应用实例

概述

范围分区(Range partition)：就是根据表的某个字段的值，以固定的一个范围作为一个分区来划分数据。

实例:创建销售部门销售数据清单表:

create table sales_list

(order_id number(5),

sales_name varchar2(20),

order_amount number(10),

order_date date

)

partition by range (order_date)

(partition sales_201401 values less than(to_date('2014-02-01','yyyy-mm-dd')),

partition sales_201402 values less than(to_date('2014-03-01','yyyy-mm-dd')),

partition sales_201403 values less than(to_date('2014-04-01','yyyy-mm-dd')),

partition sales_201404 values less than(to_date('2014-05-01','yyyy-mm-dd')),

partition sales_201405 values less than(to_date('2014-06-01','yyyy-mm-dd')),

partition sales_201406 values less than(to_date('2014-07-01','yyyy-mm-dd')),

partition sales_201407 values less than(to_date('2014-08-01','yyyy-mm-dd')),

partition sales_201408 values less than(to_date('2014-09-01','yyyy-mm-dd')),

partition sales_201409 values less than(to_date('2014-10-01','yyyy-mm-dd')),

partition sales_201410 values less than(to_date('2014-11-01','yyyy-mm-dd')),

partition sales_201411 values less than(to_date('2014-12-01','yyyy-mm-dd')),

partition sales_201412 values less than(to_date('2015-01-01','yyyy-mm-dd')),

partition sales_201501 values less than(to_date('2015-02-01','yyyy-mm-dd')),

partition sales_201502 values less than(to_date('2015-03-01','yyyy-mm-dd'))

);

创建了14个分区，14个段

SQL> col partition_name format a20

SQL> col segment_name format a20

SQL> select segment_name,partition_name from user_segments where segment_name='SALES_LIST';

SEGMENT_NAME      PARTITION_NAME

-------------------- --------------------

SALES_LIST      SALES_201401

SALES_LIST      SALES_201402

SALES_LIST      SALES_201403

SALES_LIST      SALES_201404

SALES_LIST      SALES_201405

SALES_LIST      SALES_201406

SALES_LIST      SALES_201407

SALES_LIST      SALES_201408

SALES_LIST      SALES_201409

SALES_LIST      SALES_201410

SALES_LIST      SALES_201411

SALES_LIST      SALES_201412

SALES_LIST      SALES_201501

SALES_LIST      SALES_201502

14 rows selected.

创建过程用于删除旧的分区并添加新的分区：

CREATE OR REPLACE PROCEDURE drop_add_partition AS

v_part_name VARCHAR2(100);

v_next_name VARCHAR2(100);

v_over_time NUMBER;

v_string    VARCHAR2(10);

v_date      DATE;

BEGIN

SELECT MIN(partition_name)

INTO v_part_name

FROM user_tab_partitions

WHERE table_name = 'SALES_LIST'; --找到当前最早的分区

SELECT MAX(partition_name)

INTO v_next_name

FROM user_tab_partitions

WHERE table_name = 'SALES_LIST'; --找到当前最晚的分区

SELECT substr(to_char(add_months(to_date(substr(v_next_name, 7, 6),

'yyyy-mm'),

1),

'yyyymmdd'),

1,

6)

INTO v_next_name

FROM dual; --拼接下一个新的分区的名称串

SELECT round(months_between(SYSDATE,

to_date(substr(v_part_name, 7, 6), 'yyyy-mm')))

INTO v_over_time

FROM dual;

--计算当前时间和最早分区的之间间隔的月数

v_string := to_char(add_months(to_date(v_next_name, 'yyyy-mm'), 1),

'yyyy-mm');

v_date   := to_date(substr(v_string, 1, 4) || '-' ||

substr(v_string, 6, 2) || '-01',

'yyyy-mm-dd');

--拼接新的分区在创建的时候需要指定的截至时间点的串

IF v_over_time > 12

THEN

EXECUTE IMMEDIATE 'alter table sales_list drop partition ' ||

v_part_name; --删除旧的分区

EXECUTE IMMEDIATE 'alter table sales_list add partition SALES_' ||

REPLACE(v_next_name, '-', '') ||

' values less than (''' || v_date || ''')'; --添加新的分区

END IF;

EXCEPTION

WHEN OTHERS THEN

--异常处理

dbms_output.put_line(to_char(SQLCODE));

dbms_output.put_line(SQLERRM);

END;

SQL> set serveroutput on

SQL> exec drop_add_partition;

PL/SQL procedure successfully completed.

SQL> select segment_name,partition_name from user_segments where segment_name='SALES_LIST';

SEGMENT_NAME      PARTITION_NAME

-------------------- --------------------

SALES_LIST      SALES_201402

SALES_LIST      SALES_201403

SALES_LIST      SALES_201404

SALES_LIST      SALES_201405

SALES_LIST      SALES_201406

SALES_LIST      SALES_201407

SALES_LIST      SALES_201408

SALES_LIST      SALES_201503

SALES_LIST      SALES_201409

SALES_LIST      SALES_201410

SALES_LIST      SALES_201411

SALES_LIST      SALES_201412

SALES_LIST      SALES_201501

SALES_LIST      SALES_201502

14 rows selected.

创建后台作业：

declare

job number;

begin

dbms_job.submit(job,'drop_add_partition;',sysdate,'sysdate+1');  --每天运行一次，创建这个job的时候就要执行一次

commit;

end;