Oracle数据库管理-long_TO_CHAR函数使用处理分区键位

1 函数创建 

登录数据库主机

sqlplus / as sysdba

.创建函数

create or replace function long_to_char(p_owner varchar2,p_tname varchar2,p_parname varchar2)
return varchar2
is
l_high_value varchar2(1000);
begin
  select t.high_value into l_high_value from dba_tab_partitions t where t.table_name=p_tname and t.partition_name=p_parname and t.table_owner=p_owner;
  return l_high_value;
  end;

2 函数授权

create public synonym long_to_char for sys.long_to_char

grant execute on wm_concat to public

3 批量生成添加分区语句

--针对range以时间列为范围的分区模式

select 'ALTER TABLE ' || TABLE_NAME || ' ADD PARTITION ' || PARTITION_NAME ||
       ' VALUES LESS THAN ' || '''' ||
       SUBSTR(long_to_char('DNMS_ZZ',t.table_name, t.partition_name), 11, 19) || '''' || ';'
  from dba_tab_partitions t
 where t.table_owner = 'DNMS_ZZ' AND T.table_name='tab';