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';