Oracle Создание PARTITION BY RANGE по полю DATE
21 Ноябрь 2012
1 комментарий
Скрипт помогающий создать PARTITION BY RANGE по полю DATE
SET LINESIZE 150 SET SERVEROUTPUT ON SIZE 1000000 WHENEVER SQLERROR CONTINUE SPOOL C:\create_partition_date.log declare iter number; l_date_start date := to_date('01-11-2011','dd-mm-yyyy'); l_date_end date := to_date('01-01-2023','dd-mm-yyyy'); l_name_part varchar2(20) := 'TEST'; l_part_field varchar2(20) := 'MY_DATE'; begin dbms_output.put_line('PARTITION BY RANGE('||l_part_field||')'); dbms_output.put_line('('); dbms_output.put_line(' PARTITION '||l_name_part ||'_MIN VALUES LESS THAN (to_date(''' ||to_char(add_months(l_date_start,-1),'dd-mm-yyyy') ||''',''dd-mm-yyyy''))'); for j in ( with t as ( select l_date_start d1, l_date_end d2 from dual ) -- select decode(level,1,d1, trunc(add_months(d1,level-1),'mm') ) as date_from from t connect by add_months(trunc(d1,'mm'),level-1) <= d2 ) loop dbms_output.put_line(',PARTITION '||l_name_part||'_' ||to_char(add_months(j.date_from,-1),'mm_yyyy') ||' VALUES LESS THAN (to_date(''' ||to_char(j.date_from,'dd-mm-yyyy') ||''',''dd-mm-yyyy''))'); end loop; dbms_output.put_line(',PARTITION '||l_name_part ||'_MAX VALUES LESS THAN (MAXVALUE)'); dbms_output.put_line(')'); end; / spool off PARTITION BY RANGE(MY_DATE) ( PARTITION TEST_MIN VALUES LESS THAN (to_date('01-10-2011','dd-mm-yyyy')) ,PARTITION TEST_10_2011 VALUES LESS THAN (to_date('01-11-2011','dd-mm-yyyy')) ,PARTITION TEST_11_2011 VALUES LESS THAN (to_date('01-12-2011','dd-mm-yyyy')) ... ,PARTITION TEST_11_2022 VALUES LESS THAN (to_date('01-12-2022','dd-mm-yyyy')) ,PARTITION TEST_12_2022 VALUES LESS THAN (to_date('01-01-2023','dd-mm-yyyy')) ,PARTITION TEST_MAX VALUES LESS THAN (MAXVALUE) ) PL/SQL procedure successfully completed SQL> spool off Stopped spooling to C:\create_partition_date.log
Последние комментарии