execute immediate

declare
v_sql varchar2(500);
v_table_name varchar2(50):='test';
v_max_date date;
v_partition_max varchar2(20)
begin
v_sql :='select min(to_date(log_date,''dd-Mon-yyyy'')) from '||v_table_name||'_HIST_EXT';
execute immediate (v_sql) into v_max_date;
v_partition_date := trunc(v_max_date -i);
v_partition_name := upper('P') ||to_char(v_max_date - 1, 'yyyy_mm_dd');
v_sql := 'alter table ' || v_table_name || ' split partition ' ||v_partition_max ;
v_sql := v_sql || ' at ( TO_DATE(''' || to_char(v_partition_date, 'DD-MM-YYYY hh24:mi:ss') ||
''',''DD-MM-YYYY hh24:mi:ss'')) ';
v_sql := v_sql || ' into (partition ' || v_partition_name || ' , partition ' ||
v_partition_max || ' ) ';
v_sql := v_sql || ' update global indexes; ';
dbms_output.put_line(v_sql);
EXECUTE IMMEDIATE (v_sql);
end loop;

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/573930/viewspace-1057034/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/573930/viewspace-1057034/