范围分区
a.新建一个分区表，通过过程来自动添加或者删除分区：
create table sale_data(sale_id number(5),saleman_name varchar2(30),sales_mount number(10),sales_date date)partition by range (sales_date)(partition sales_2014_1 values less than (to_date('01/02/2014','DD/MM/YYYY')),partition sales_2014_2 values less than (to_date('01/03/2014','DD/MM/YYYY')),partition sales_2014_3 values less than (to_date('01/04/2014','DD/MM/YYYY')),partition sales_2014_4 values less than (to_date('01/05/2014','DD/MM/YYYY')),partition sales_2014_5 values less than (to_date('01/06/2014','DD/MM/YYYY')),partition sales_2014_6 values less than (to_date('01/07/2014','DD/MM/YYYY')),partition sales_2014_7 values less than (to_date('01/08/2014','DD/MM/YYYY')),partition sales_2014_8 values less than (to_date('01/09/2014','DD/MM/YYYY')),partition sales_2014_9 values less than (to_date('01/10/2014','DD/MM/YYYY')),partition sales_2014_10 values less than (to_date('01/11/2014','DD/MM/YYYY')),partition sales_2014_11 values less than (to_date('01/12/2014','DD/MM/YYYY')),partition sales_2014_12 values less than (to_date('01/01/2015','DD/MM/YYYY')),partition sales_2015_1 values less than (to_date('01/02/2015','DD/MM/YYYY'))，partition sales_2015_2 values less than (to_date('01/03/2015','DD/MM/YYYY')),partition sales_2015_3 values less than (to_date('01/04/2015','DD/MM/YYYY')),partition sales_2015_4 values less than (to_date('01/05/2015','DD/MM/YYYY'))，partition sales_2015_5 values less than (to_date('01/06/2015','DD/MM/YYYY'))，partition sales_2015_6 values less than (to_date('01/07/2015','DD/MM/YYYY'))，partition sales_2015_7 values less than (to_date('01/08/2015','DD/MM/YYYY')));SQL> select segment_name,partition_name from user_segments where segment_name='SALE_DATA' order by partition_name desc;SEGMENT_NAME PARTITION_NAME-------------------- --------------------SALE_DATA SALES_2015_7SALE_DATA SALES_2015_6SALE_DATA SALES_2015_5SALE_DATA SALES_2015_4SALE_DATA SALES_2015_3SALE_DATA SALES_2015_2SALE_DATA SALES_2015_1SALE_DATA SALES_2014_9SALE_DATA SALES_2014_8SALE_DATA SALES_2014_7SALE_DATA SALES_2014_6SALE_DATA SALES_2014_5SALE_DATA SALES_2014_4SALE_DATA SALES_2014_3SALE_DATA SALES_2014_2SALE_DATA SALES_2014_12SALE_DATA SALES_2014_11SALE_DATA SALES_2014_10SALE_DATA SALES_2014_119 rows selected.
CREATE OR REPLACE PROCEDURE drop_partition AS v_part_name VARCHAR2(100); v_over_time VARCHAR2(100); v_err_num NUMBER(10); v_err_msg VARCHAR2(10);BEGIN SELECT MIN(partition_name) INTO v_part_name FROM user_tab_partitions WHERE table_name = 'SALE_DATA'; --找到当前最早的分区 --取的最早分区到当前时间的间隔 SELECT months_between(SYSDATE, to_date(substr(MIN(partition_name), 7, 8), 'yyyy-mm')) INTO v_over_time FROM user_tab_partitions WHERE table_name = 'SALE_DATA'; IF v_over_time > 15 THEN EXECUTE IMMEDIATE 'alter table sale_data drop partition ' || v_part_name; END IF;EXCEPTION WHEN OTHERS THEN v_err_num := SQLCODE; v_err_msg := substr(SQLERRM, 1, 100); dbms_output.put_line('Error' || v_err_num || 'Des' || v_err_msg);END;DECLARE job NUMBER;BEGIN dbms_job.submit(job, 'drop_partition;', SYSDATE, 'sysdate+1');END;
SQL> exec drop_partition;PL/SQL procedure successfully completed.SQL> select segment_name,partition_name from user_segments where segment_name='SALE_DATA' order by partition_name desc;SEGMENT_NAME PARTITION_NAME-------------------- --------------------SALE_DATA SALES_2015_7SALE_DATA SALES_2015_6SALE_DATA SALES_2015_5SALE_DATA SALES_2015_4SALE_DATA SALES_2015_3SALE_DATA SALES_2015_2SALE_DATA SALES_2015_1SALE_DATA SALES_2014_9SALE_DATA SALES_2014_8SALE_DATA SALES_2014_7SALE_DATA SALES_2014_6SALE_DATA SALES_2014_5SALE_DATA SALES_2014_4SALE_DATA SALES_2014_3SALE_DATA SALES_2014_2SALE_DATA SALES_2014_12SALE_DATA SALES_2014_11SALE_DATA SALES_2014_1018 rows selected.