建立新年分区脚本.txt declare v_part_type int; /* 20161212

文件名称: 建立新年分区脚本.txt

所属分类: Oracle

开发工具:

文件大小: 13kb

下载次数: 0

上传时间: 2019-09-08

提供者: qq_37******

下载 (13kb)

不能下载？报告错误

详细说明： declare v_part_type int; /* 20161212 按天分区 type:1 201612 按月分区 type:2 2016 按年分区 type:3 161212 按天分区 type:4 自动分区 type:5 */ v_part_key_value varchar2(20); v_part_prefix varchar2(50); v_part_name varchar2(252); v_col_type varchar2(64); v_part_length int; v_ part_key_length int; v_part_key_position int; type cur_type is ref cursor; my_cur sys_refcursor; val varchar2(1000); v_year varchar2(50); v_max_part_name varchar2(256); v_maxdate_part_name varchar2(256); v_part_tablespace varchar2(256); v_part_sums int; begin for v_minp_rec in (select a.table_owner, a.table_name, a.partition_name as minpart from dba_tab_partitions a, (select table_owner, table_name, decode(max(partition_position) - 1, 0, 1, max(partition_position) - 1) as part_posi from dba_tab_partitions t where table_owner NOT in (SYS, SYSTEM) and t.table_name = F_PASSENGER_STOP group by table_owner, table_name) b where a.table_owner = b.table_owner and a.table_name = b.table_name and a.partition_position = b.part_posi) loop v_part_name := v_minp_rec.minpart; --------------------------------------------------获取分区类型--------------------------------------------------- select length(v_part_name) - regexp_instr(reverse(v_part_name), \D) into v_part_key_position from dual; -----获取分区key开始位置 v_part_key_value := substr(v_part_name, v_part_key_position + 2); -----获取分区key v_part_key_length := length(v_part_key_value); -----获取分区 if v_part_key_length = 8 then v_part_type := 1; elsif v_part_key_length = 6 then if regexp_instr(v_part_key_value, 201) = 1 then ---------判定是yyyymm v_part_type := 2; else ---------判定是yymmdd v_part_type := 4; end if; elsif v_part_key_length = 4 then v_part_type := 3; else dbms_output.put_line(------------- || v_minp_rec.table_name || v_part_key_value); end if; v_part_prefix := substr(v_part_name, 0, length(v_part_name) - v_part_key_length); DBMS_OUTPUT.put_line(/*); DBMS_OUTPUT.put_line(用户名： || v_minp_rec.table_owner); DBMS_OUTPUT.put_line(表名： || v_minp_rec.table_name); DBMS_OUTPUT.put_line(分区类型值： || v_part_type); DBMS_OUTPUT.put_line(分区键值： || v_part_key_value); DBMS_OUTPUT.put_line(分区前缀： || v_part_prefix); select DATA_type into v_col_type from dba_tab_columns where table_name = v_minp_rec.table_name and owner = v_minp_rec.table_owner and column_name = (select column_name from dba_part_key_columns where name = v_minp_rec.table_name and owner = v_minp_rec.table_owner); DBMS_OUTPUT.put_line(分区键类型： || v_col_type); select partition_name into v_max_part_name from dba_tab_partitions where table_owner = v_minp_rec.table_owner and table_name = v_minp_rec.table_name and partition_position = (select max(partition_position) from dba_tab_partitions where table_owner = v_minp_rec.table_owner and table_name = v_minp_rec.table_name); DBMS_OUTPUT.put_line(最大分区名： || v_max_part_name); select max(partition_position) - 1 into v_part_sums from dba_tab_partitions where table_owner = v_minp_rec.table_owner and table_name = v_minp_rec.table_name; if v_part_sums = 0 then v_maxdate_part_name := v_max_part_name; v_part_type := 5; else select partition_name into v_maxdate_part_name from dba_tab_partitions where table_owner = v_minp_rec.table_owner and table_name = v_minp_rec.table_name and partition_position = v_part_sums; end if; DBMS_OUTPUT.put_line(最大日期分区名： || v_maxdate_part_name); select tablespace_name into v_part_tablespace from dba_tab_partitions where table_owner = v_minp_rec.table_owner and table_name = v_minp_rec.table_name and partition_name = v_maxdate_part_name; DBMS_OUTPUT.put_line(*/); ------------------------------以to_date划分的日分区--------------------------- if (v_part_type = 1 or v_part_type = 4) and v_col_type = DATE then open my_cur for --删除最大分区 select alter table || v_minp_rec.table_owner || . || v_minp_rec.table_name || drop partition || v_max_part_name || ; from dual union all select alter table || v_minp_rec.table_owner || . || v_minp_rec.table_name || add partition || v_part_prefix || rq || values less than (TO_DATE( || || to_char(to_date(rq, yyyymmdd) + 1, YYYY-MM-DD HH24:MI:SS) || || , || || YYYY-MM-DD HH24:MI:SS || || )) || tablespace || v_part_tablespace || ; from (select to_char(trunc(sysdate + 120, yyyy) + level - 1, yyyymmdd) rq from dual connect by rownum <= 365) union all --增加最大分区 select alter table || v_minp_rec.table_owner || . || v_minp_rec.table_name || add partition || v_max_part_name || values less than (MAXVALUE) || tablespace || v_part_tablespace || ; from dual; LOOP ---输出执行语句 fetch my_cur into val; EXIT WHEN my_cur%NOTFOUND; DBMS_OUTPUT.put_line(val); end loop; ------------------------------以to_date划分的月分区--------------------------- elsif v_part_type = 2 and v_col_type = DATE then open my_cur for --删除最大分区 select alter table || v_minp_rec.table_owner || . || v_minp_rec.table_name || drop partition || v_max_part_name || ; from dual union all select alter table || v_minp_rec.table_owner || . || v_minp_rec.table_name || add partition || v_part_prefix || date_tab.part_name || values less than (TO_DATE( || || to_char(date_tab.lessvalue, YYYY-MM-DD HH24:MI:SS) || || , || || YYYY-MM-DD HH24:MI:SS || || )) || tablespace || v_part_tablespace || ; from (select 2017 || lpad(level, 2, 0) part_name, --需要更改年 add_months(to_date(2017 || lpad(level, 2, 0), --需要更改年 YYYYMM), 1) lessvalue from dual connect by level <= 12) date_tab union all --增加最大分区 select alter table || v_minp_rec.table_owner || . || v_minp_rec.table_name || add partition || v_max_part_name || values less than (MAXVALUE) || tablespace || v_part_tablespace || ; from dual; LOOP ---输出执行语句 fetch my_cur into val; EXIT WHEN my_cur%NOTFOUND; DBMS_OUTPUT.put_line(val); end loop;

(系统自动生成,下载前可以参看下载内容)