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@]