ora-30004:when using sys_connect_by_path function,cannot have seperator as part of column value
今天在做在线重定义表对一个8000w行的大表进行分区之前拼写脚本的时候报了一个错 ora-30004
EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'TESTTT1', 'TESTTT1_PART', 'OWNER OWNER ,OBJECT_NAME OBJECT_NAME ,SUBOBJECT_NAME SUBOBJECT_NAME ,OBJECT_ID OBJECT_ID ,DATA_OBJECT_ID DATA_OBJECT_ID ,OBJECT_TYPE OBJECT_TYPE ,CREATED CREATED ,LAST_DDL_TIME LAST_DDL_TIME ,TIMESTAMP TIMESTAMP ,STATUS STATUS ,TEMPORARY TEMPORARY ,GENERATED GENERATED ,SECONDARY SECONDARY ', DBMS_REDEFINITION.cons_use_rowid);
用下面的sql取上面的column mapping
select name
from (select substr(sys_connect_by_path(a, ','), 2) name
from (select a,
rnFirst,
lead(rnFirst) over(order by rnFirst) rnNext
from (select a.a, row_number() over(order by a.b) rnFirst
from (select '1' b,
column_name || ' ' || column_name || '' a
from user_tab_columns
where table_name = '&TABLE_NAME') a) tmpTable1) tmpTable2
start with rnNext is null
connect by rnNext = prior rnFirst
order by 1 desc)
where rownum = 1
ora-30004:when using sys_connect_by_path function,cannot have seperator as part of column value
上面的错误很明显，就是提示分隔符不能出现在字段的内容中
也对哦，如果字段中含有分隔符字符，就不能和真正的分隔符进行区分了，你也可以换个分隔符试试看，应该就不报错了
[@more@]来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/70612/viewspace-1049473/ ，如需转载，请注明出处，否则将追究法律责任。
