<强>友情提示:请在业务空闲是操作。为什么呢?
<强>
——Oracle 11 g 间隔分区(间隔)重命名,批量生成重命名分区脚本
set linesize
180
set serverout
声明
v_table_owner 才能,,,,,,,,,,varchar2 (100),:=,
“OP %”;
v_table_name_day_p 才能,,,,,varchar2 (100);
v_table_name_month_p 才能,,,
varchar2 (100);
v_print 才能,,,,,,,,,,,,,,,,号码:=1,,,1:只打印执行脚本,2 DB直接执行
v_new_partition_name 才能,,,varchar2 (100);
v_sql 才能,,,,,,,,,,,,,,,,,,
varchar2 (2000);
vSqlerrm 才能,,,,,,,,,,,,,,,varchar2 (256);
开始
for 才能,小姐;
拷贝(select t1.table_owner,
,,,,,,t2.table_name,
,,,,,,t1.partition_name,
,,,,,,t2.interval,
,,,,,t1.high_value
得到才能;
dba_tab_partitions t1, dba_part_tables t2
,where t2.owner =,
t1.table_owner
,,以及t2.table_name =t1.table_name
,,以及t1.interval =,
“是的”
,,以及t2.partitioning_type=胺段А?
,,以及t1.partition_name like
“SYS \ _ %”, escape ”
,,以及table_owner like v_table_owner order by
t1.table_owner t1.partition_name),循环
,,,if
i.interval=' NUMTODSINTERVAL(1天),
,,,v_table_name_day_p :=,
substr (i.table_name,, 1,, 19);,,,日分区取前19位字符;
,,,execute immediate select
to_char (', | |, i.high_value | |
,,,,,,,,,,,,,,,,,,,,,的,安康;
NUMTODSINTERVAL(1天),”“,| |
,,,,,,,,,,,,,,,,,,,,,v_table_name_day_p
| |,“YYYY”M“毫米”D“弟弟”),得到双重的,,,,,
,,,,,into
v_new_partition_name;
,,,elsif i.interval=癗UMTOYMINTERVAL(1月),,
然后
,,,v_table_name_month_p :=, substr (i.table_name,, 1,, 23),,
——月分区取前23位字符;
,,,execute immediate “select to_char (”, | |, i.high_value
||
,,,,,,,,,,,,,,,,,,,,,的,安康;NUMTOYMINTERVAL(1月),“”,
||
,,,,,,,,,,,,,,,,,,,,,v_table_name_month_p | |,”“YYYY“M“毫米”),得到
双重的
,,,,,into v_new_partition_name;
,,,最终获得;如果;
,,,if v_print=1,
然后
,,,dbms_output.put_line (“alter table ”, | |, i.table_owner | |,“。”,
| | i.table_name | |,‘, rename partition ’, | | i.partition_name | |,”,用“,| |,
v_new_partition_name | |, '; ');
,,,elsif v_print=2,
,,,,,,,v_sql :=,
“alter table ”, | |, i.table_owner | |,“。”, | | i.table_name | |,‘, rename partition ’,
| | i.partition_name | |,”,用“,| |,v_new_partition_name;
,,,,,,,execute
immediate v_sql;
,,,最终获得;如果;
最终获得才能;循环;
异常
when 才能;others
然后
,,,vSqlerrm :=, sqlerrm;
,,,dbms_output.put_line (vSqlerrm | |的| | | v_sql);
结束;
