Shulou(Shulou.com)06/01 Report--

Friendly reminder: please operate when the business is free. Why?

-- Oracle 11g interval partition (INTERVAL) rename, batch generate rename partition script set linesize 180set serverout ondeclare v_table_owner varchar2: = 'OP%'; v_table_name_day_p varchar2 (100); v_table_name_month_p varchar2 (100); v_print number:=1;-- 1: only print execution script, 2 Magi DB directly executes v_new_partition_name varchar2 (100) V_sql varchar2 (2000); vSqlerrm varchar2 (256) Begin for i in (select t1.table_owner, t2.table_name, t1.partition_name, t2.interval, t1.high_value from dba_tab_partitions T1) Dba_part_tables T2 where t2.owner = t1.table_owner and t2.table_name = t1.table_name and t1.interval = 'YES' and t2.partitioningroomtypewritten Range' and t1.partition_name like 'SYS\ _%' escape'\ 'and table_owner like v_table_owner order by t1.Tablekeeper owner.name) loop if i.interval='NUMTODSINTERVAL (1) then v_table_name_day_p: = substr (i.table_name, 1 19) -- Day Partition takes the first 19 characters Execute immediate 'select to_char (' | | i.high_value | |'- NUMTODSINTERVAL),''"| | v_table_name_day_p | |'" YYYY "M" MM "D" DD'') from dual' into v_new_partition_name Elsif i.interval='NUMTOYMINTERVAL 'then v_table_name_month_p: = substr (i.table_name, 1,23);-- the first 23 characters in the month partition Execute immediate 'select to_char (' | | i.high_value | |'- NUMTOYMINTERVAL),''"| | v_table_name_month_p | |'" YYYY "M" MM'') from dual' into If v_print=1 then dbms_output.put_line ('alter table' | | i.table_owner | |'. | | i.table_name | | 'rename partition' | | i.partition_name | |'to'| | v_new_partition_name | |';'); elsif v_print=2 then v_sql: = 'alter table' | | i.table_owner | |'. | | i.table_name | | 'rename partition' | i.partition_name | |'to'| | vested newparties partitionname; execute immediate v_sql | End if; end loop;exception when others then vSqlerrm: = sqlerrm; dbms_output.put_line (vSqlerrm | |'| v_sql); end

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.