Retested it again - Now looks different
Rajeshwaran Jeyabal, May 08, 2012 - 1:21 am UTC
Is that repeatable for you? rajesh@ORA11GR2> @d:\script.sql;
rajesh@ORA11GR2> column owner format a10 trunc;
rajesh@ORA11GR2> column object_type format a15 trunc;
rajesh@ORA11GR2> column object_name format a30 trunc;
rajesh@ORA11GR2> column vals format a20 trunc;
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec runstats_pkg.rs_start;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04
rajesh@ORA11GR2>
rajesh@ORA11GR2> set termout off;
rajesh@ORA11GR2> select owner,object_type,object_name,
2 listagg(txt,';') within group(order by txt) as vals
3 from t
4 group by owner,object_type,object_name;
1000 rows selected.
Elapsed: 00:00:00.07
rajesh@ORA11GR2> set termout on;
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec runstats_pkg.rs_middle;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
rajesh@ORA11GR2>
rajesh@ORA11GR2> set termout off;
rajesh@ORA11GR2> select owner,object_type,object_name,
2 fnc(txt) as vals
3 from t
4 group by owner,object_type,object_name;
1000 rows selected.
Elapsed: 00:00:00.17
rajesh@ORA11GR2> set termout on;
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec runstats_pkg.rs_stop(100);
Run1 ran in 8 hsecs
Run2 ran in 52 hsecs
run 1 ran in 15.38% of the time
Name Run1 Run2 Diff
STAT...session cursor cache hi 2 1,001 999
STAT...sorts (memory) 1 1,001 1,000
STAT...execute count 5 1,005 1,000
STAT...workarea executions - o 4 1,004 1,000
STAT...opened cursors cumulati 5 1,007 1,002
STAT...recursive calls 3 1,005 1,002
STAT...calls to get snapshot s 3 1,007 1,004
STAT...sorts (rows) 6,000 9,000 3,000
STAT...session uga memory max 123,452 65,512 -57,940
STAT...session pga memory max 131,072 65,536 -65,536
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
580 594 14 97.64%
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.62
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> spool off;
A reader, May 08, 2012 - 8:17 am UTC
@Rajeshwaran -
What are trying to say / get out of your question?
May 08, 2012 - 11:24 am UTC
he is saying "no, it doesn't reproduce - listagg is much faster - there was something else going on in my database at that point in time that was happening when I ran listagg but not when I ran stragg"
runstats is only 'reliable' in a single user situation as it looks at database wide metrics, not just session metrics.
Sangeetha P, February 05, 2013 - 2:13 am UTC
Hi Tom,
I have a query like this.
Please ignore the table structure.
SELECT LOOP_DATE,
SHIFT,
stragg( DISTINCT lic_list) lic_list,
SUM(DEMAND_VAL) DEMAND_VAL
FROM
(SELECT SHIFT,
LOOP_DATE,
DEMAND_VAL,
'('
||AC_TYPE
||','
||CNT
||')' lic_list
FROM
(SELECT LOOP_DATE,
DEMAND_VAL,
AC_TYPE,
SHIFT,
NVL( (LENGTH(lic_list) - NVL(LENGTH(REPLACE(lic_list,AC_TYPE,'')),0))/ LENGTH(AC_TYPE),0) cnt
FROM
(SELECT LOOP_DATE,
DEMAND_VAL,
AC_TYPE,
SHIFT,
(SELECT WM_CONCAT(CREW_LIST) CREW_LIST
FROM baseshift_tab BS
WHERE BS.SHIFT_DATE=DEMAND_TAB.LOOP_DATE
AND BS.WORKPOOL =DEMAND_TAB.SHIFT
) lic_list
FROM
(SELECT DEMAND_TAB.LOOP_DATE,
DEMAND_TAB.DEMAND_VAL,
(SELECT MST_AC_TYP.AC_TYP
FROM MST_AC_TYP
WHERE MST_AC_TYP.MAT_ID =DEMAND_TAB.ROW_NUM
AND MST_AC_TYP.isACTIVE ='Y'
) AC_TYPE,
DEMAND_TAB.SHIFT
FROM DEMAND_TAB
) DEMAND_TAB
)
)
)
group by LOOP_DATE,
SHIFT
This query is executing fine in my SQL editor. If I place the same inside a procedure it shows me this error:Error(153,3): PL/SQL: ORA-30482: DISTINCT option not allowed for this function
-Inside procedure I will bulk collect this result into a nested table.
-MST_AC_TYP is a physical table.
-baseshift_tab and demand_tab are actually nested tables for my procedure. But for trying it in the SQL editor I made it as a physical table. In procedure I tried with these tables as Physical as well as nested. In both the cases it is showing this error.
Output :-
lic_list column value with distinct (required result) -> (320,10)|(330,9)
lic_list column value without distinct->(330,9)|(320,10)|(330,9)|(330,9)|(330,9)
Basically I am trying to avoid the duplicates from lic_list column.
Is this a bug or am I doing something wrong?
Thanks in advance.
Regards
Sangeetha
February 06, 2013 - 8:07 am UTC
plsql has an open, in progress bug - it doesn't recognize distinct inside of an analytic.
work around is to use native dynamic sql for now. that "hides" the distinct from plsql
Sangeetha P, February 10, 2013 - 4:39 am UTC
Ok Thanks Tom. I just used a function to remove duplicates.Hope that is fine.
Function Code:
WITH data AS
(SELECT token,
rownum rn
FROM
( select distinct TRIM( SUBSTR (TXT, INSTR (TXT, '|', 1, level ) + 1 , INSTR (TXT, '|', 1, level+1) - INSTR (TXT, '|', 1, level) -1 ) ) as TOKEN
FROM
( SELECT '|' || ltrim(rtrim(p_input,'|'),'|') || '|' txt FROM dual
)
CONNECT BY level <= LENGTH(txt)-LENGTH(REPLACE(txt,',',''))-1
)
)
SELECT LTRIM( MAX( SYS_CONNECT_BY_PATH(TOKEN,'|') ), '|' )
|| '|' STR
INTO v_output_str
FROM data
START WITH RN = 1
connect by prior RN = RN-1 ;
RETURN rtrim( v_output_str, '|' );
Thanks and Regards
Sangeetha
In SQL Developer , result of stragg is showing box characters
Sangeetha, July 23, 2013 - 7:45 am UTC
Hi Tom,
Hope you are doing fine.
I have one more question on stragg/WM_CONCAT:
When I tried to execute the following query, (Stragg/WM_CONCAT on a hierarchical query) in SQL Developer it is showing box characters. But the same query works fine in SQL PLUS.I am using SQL Developer Version 3.2.20.09
select PRIVILEGEID,stragg(menu_Path) from (
select PATH_TAB.PRIVILEGEID, PATH_TAB.menu_Path from
(SELECT MENUNAME,
PRIVILEGEID,
SYS_CONNECT_BY_PATH(MENUNAME, '/') menu_Path
from AMPPS_COMN_MENUITEMS_T
start with PARENTID =0
connect by prior MENUITEMID = PARENTID
) PATH_TAB
where PATH_TAB.PRIVILEGEID<>0) group by PRIVILEGEID;
Will there be any issues if I use this in a procedure and give as a refcursor output to .NET code?
Thanks and Regards
Sangeetha
July 31, 2013 - 6:36 pm UTC
use DUMP() on it to see what characters you see in a) sqlplus, b) sqldev
are your charactersets the same???
if you do not use stragg (just forget about wm_concat ok, no documentation) - does the raw data show boxes or not?
Sangeetha, August 01, 2013 - 10:15 am UTC
Hi Tom,
Thank you very much for the reply. My Menu_Path column data is like this (there is no box):
/Administration
/Planning
/PLANNING/AIRCRAFT SCHEDULING
/Planning/Aircraft Scheduling/Dynamic Planner etc
After doing stragg(), I am expecting it like /Administration|/Planning|/PLANNING/AIRCRAFT SCHEDULING etc.
I tried dump, it is showing numbers :
Typ=1 Len=90: 0,47,0,80,0,108,0,97,0,110,0,110,0,105,0,110,0,103,0,47,0,65,0,105,0,114,0,99,0,114,0,97,0,102,0,116,0,32,0,83,0,99,0,104,0,101,0,100,0,117,0,108,0,105,0,110,0,103,0,47,0,68,0,121,0,110,0,97,0,109,0,105,0,99,0,32,0,80,0,108,0,97,0,110,0,110,0,101,0,114.
Is there anything I am missing here.
Query used:
select PRIVILEGEID, dump((stragg(menu_Path) )) from (
select PATH_TAB.PRIVILEGEID, PATH_TAB.menu_Path from
(SELECT MENUNAME,
PRIVILEGEID,
SYS_CONNECT_BY_PATH(MENUNAME, '/') menu_Path
from AMPPS_COMN_MENUITEMS_T
start with PARENTID =0
connect by prior MENUITEMID = PARENTID
) PATH_TAB
where PATH_TAB.PRIVILEGEID<>0) group by PRIVILEGEID;
Thanks and Regards
Sangeetha
August 02, 2013 - 7:09 pm UTC
and when you compare a dump output from an environment that "works" to one that does not - are they the same or different?
and answer the other question I asked.
and what is your database characterset
listagg question
Sokrates, September 27, 2013 - 7:42 am UTC
sokrates@11.2 > create table t ( i int );
Table created.
sokrates@11.2 > insert into t values( 2 );
1 row created.
sokrates@11.2 > insert into t values( 1 );
1 row created.
sokrates@11.2 > select
2 listagg(i, ', ') within group(order by i) oi,
3 listagg(i, ', ') within group(order by null) onull,
4 listagg(i, ', ') within group(order by 1) o1
5 from t;
OI
----------------------------------------------------------------
ONULL
----------------------------------------------------------------
O1
----------------------------------------------------------------
1, 2
1, 2
1, 2
sql has to return oi = '1, 2'.
Is sql free to return onull = '2, 1' as well ?
Is sql free to return o1 = '2, 1' as well ?
list-un-agg function?...
James, April 04, 2014 - 5:21 pm UTC
Hello Tom.
Just wondering if you knew of any plans to offer a built-in analytic to unpack an aggregated column so that we'd see one row per packed value (with other duplicated attributes, of course due to the "ungrouping").
Does something like that already exist? we've poked around in the docs, but may have missed it. We're on 11.2.
Thanks and have a great weekend.
April 16, 2014 - 5:53 pm UTC
there are no analytics that increase the size of a result set, they preserve the cardinality of the result set...
that said, this can be accomplished using table unnesting:
ops$tkyte%ORA11GR2> create table t
2 as
3 select deptno, listagg( ename, ',' ) within group (order by ename) as enames
4 from scott.emp
5 group by deptno
6 /
Table created.
ops$tkyte%ORA11GR2> select * from t;
DEPTNO ENAMES
---------- --------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCO
TT,SMITH
30 ALLEN,BLAKE,JAMES,MA
RTIN,TURNER,WARD
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select deptno, column_value
2 from (select deptno, ','||enames||',' enames from t),
3 table( cast( multiset(
4 select trim( substr (enames,
5 instr (enames, ',', 1, level ) + 1,
6 instr (enames, ',', 1, level+1)
7 - instr (enames, ',', 1, level) -1 ) ) as token
8 from dual
9 connect by level <= length(enames)-length(replace(enames,',',''))-1 )
10 as sys.odciVarchar2List ) )
11 /
DEPTNO COLUMN_VALUE
---------- ------------
10 CLARK
10 KING
10 MILLER
20 ADAMS
20 FORD
20 JONES
20 SCOTT
20 SMITH
30 ALLEN
30 BLAKE
30 JAMES
30 MARTIN
30 TURNER
30 WARD
14 rows selected.
can be done with model clause too
Igor, April 17, 2014 - 8:40 pm UTC
SQL> select deptno, ename
2 from t
3 model
4 partition by (deptno)
5 dimension by (0 rn)
6 measures (enames, enames ename, enames enames_left)
7 rules iterate(100) until ename[iteration_number] = enames[iteration_number]
8 (
9 enames[iteration_number] = NVL(enames_left[CV()-1], enames[CV()]),
10 ename[iteration_number] = NVL(substr(enames[CV()], 1, instr(enames[CV()],',')-1), enames[CV()]),
11 enames_left[iteration_number] = substr(enames[CV()], instr(enames[CV()],',')+1)
12 )
13 order by 1,2
14 ;
DEPTNO ENAME
---------- -------
10 CLARK
10 KING
10 MILLER
20 ADAMS
20 FORD
20 JONES
20 SCOTT
20 SMITH
30 ALLEN
30 BLAKE
30 JAMES
30 MARTIN
30 TURNER
30 WARD
14 rows selected.
April 17, 2014 - 10:51 pm UTC
or recursion
ops$tkyte%ORA11GR2> with data( deptno, enames, cnt, idx )
2 as
3 (select deptno, ','||enames||',', length(enames)-length(replace(enames,',',''))+1 cnt, 1 idx from t
4 union all
5 select deptno, enames, cnt, idx+1 from data where idx < cnt )
6 select deptno, trim( substr (enames,
7 instr (enames, ',', 1, idx ) + 1,
8 instr (enames, ',', 1, idx+1)
9 - instr (enames, ',', 1, idx) -1 ) ) ename
10 from data
11 order by deptno, idx
12 /
DEPTNO ENAME
---------- ----------
10 CLARK
10 KING
10 MILLER
20 ADAMS
20 FORD
20 JONES
20 SCOTT
20 SMITH
30 ALLEN
30 BLAKE
30 JAMES
30 MARTIN
30 TURNER
30 WARD
14 rows selected.
there are many many many ways to write this, yes.
listagg
Sri, January 30, 2015 - 7:59 pm UTC
Just as sql%rowcount tells the no of rows affected by a DML statement, is there a way to tell how many items were grouped in the delimiter concatenated output?
I am getting this error
Rahul Pardeshi, May 08, 2015 - 4:29 pm UTC
ORA-06502: PL/SQL: numeric or value error: character
string buffer too small
ORA-06512: at
"APPS.CCL_STRING_AGG_TYPE", line 38
its a very big query with many unions
if I break the query I don't get the error
CCL_STRING_AGG_TYPE is identical to STRING_AGG_TYPE
my type coding
Rahul Pardeshi, May 08, 2015 - 5:05 pm UTC
/* Formatted on 5/8/2015 12:59:43 PM (QP5 v5.252.13127.32847) */
DROP TYPE APPS.CCL_STRING_AGG_TYPE;
CREATE OR REPLACE TYPE APPS.ccl_STRING_AGG_TYPE
AS OBJECT
(
v_result VARCHAR2 (32767),
--v_result VARCHAR2 (4000),
--v_result NVARCHAR2
STATIC FUNCTION odciaggregateinitialize (ctx IN OUT ccl_STRING_AGG_TYPE)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateiterate (self IN OUT ccl_STRING_AGG_TYPE,
p_string IN VARCHAR2)
RETURN NUMBER,
MEMBER FUNCTION odciaggregatemerge (self IN OUT ccl_STRING_AGG_TYPE,
ctx2 IN ccl_STRING_AGG_TYPE)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateterminate (self IN ccl_STRING_AGG_TYPE,
returnvalue OUT VARCHAR2,
flags IN NUMBER)
RETURN NUMBER
);
/
DROP TYPE BODY APPS.CCL_STRING_AGG_TYPE;
CREATE OR REPLACE TYPE BODY APPS.ccl_STRING_AGG_TYPE
IS
STATIC FUNCTION odciaggregateinitialize (ctx IN OUT ccl_STRING_AGG_TYPE)
RETURN NUMBER
IS
BEGIN
ctx := ccl_STRING_AGG_TYPE (NULL); -- initialize the concatenation to NULL
RETURN odciconst.success;
END odciaggregateinitialize;
MEMBER FUNCTION odciaggregateiterate (self IN OUT ccl_STRING_AGG_TYPE,
p_string IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
self.v_result := self.v_result || ',' || p_string; -- Append a delimiter and new value
RETURN odciconst.success;
END odciaggregateiterate;
MEMBER FUNCTION odciaggregatemerge (self IN OUT ccl_STRING_AGG_TYPE,
ctx2 IN ccl_STRING_AGG_TYPE)
RETURN NUMBER
IS
BEGIN
-- If merging, simply concatenate them together
-- Since each string will either be NULL or delimiter prefixed, no need to re-delimit
self.v_result := self.v_result || ctx2.v_result;
RETURN odciconst.success;
END odciaggregatemerge;
MEMBER FUNCTION odciaggregateterminate (self IN ccl_STRING_AGG_TYPE,
returnvalue OUT VARCHAR2,
flags IN NUMBER)
RETURN NUMBER
IS
BEGIN
-- Since we prefix the string initially with a comma, remove the extra here before returning
returnvalue := LTRIM (self.v_result, ',');
RETURN odciconst.success;
END odciaggregateterminate;
END;
/
Listagg on 12.2
Rajeshwaran, Jeyabal, January 24, 2017 - 8:01 am UTC
January 24, 2017 - 9:40 am UTC
Sounds like a livesql bug. I've passed it onto the livesql team.
ORA 600 on Stragg
Rajeshwaran, Jeyabal, May 25, 2020 - 2:38 pm UTC
Sorry to reach AskTom for ORA-0600 error.
But we got this error from 12.2 and 18.0 - and working with support for the past 4 days, but no luck yet.
Questions:1) So kindly let us know if you have come across any such situation like this?
2) patching 18.0 to 18.10 will solve this? ( we are on Gen1 Exadata cloud@customer and 19c is not available yet for Gen1 Exadata cloud@customer - so 19c is not possible for us)
3) any workaround this problem available? ( without parallel in-place it works, the application table got around 750M rows, so without parallel it runs for more than 3+hours and progressed only 30% of the data sets, so we need parallel ddl here )
demo@PDB1> create table t ( x number,y number, z varchar2(1000) );
Table created.
demo@PDB1> insert into t (x,y,z)
2 select trunc(rownum/10) x, mod(rownum,10) y, rpad('*',1000,'*') z
3 from all_objects;
68615 rows created.
demo@PDB1> commit;
Commit complete.
demo@PDB1> explain plan for
2 create table t
3 nologging
4 parallel 4
5 as
6 select x,stragg( vcobj(y,z) ) x1
7 from t
8 group by x ;
explain plan for
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [qerpxInitialize2], [0x7FFDC078C178], [], [], [], [], [], [], [], [], [], []
create or replace type vcobj is object( nid number, ntxt clob );
/
create or replace type vcarray is table of vcobj
/
create or replace type stragg_agg_type as object
(
l_total vcarray,
static function odciaggregateinitialize(ctx in out stragg_agg_type) return number,
member function odciaggregateiterate(self in out stragg_agg_type,
val vcobj) return number,
member function odciaggregatemerge(self in out stragg_agg_type,
ctx2 stragg_agg_type) return number,
member function odciaggregateterminate(self in stragg_agg_type,
returnvalue out clob,
flags in number) return number
);
/
create or replace type body stragg_agg_type is
static function odciaggregateinitialize(ctx in out stragg_agg_type)
return number is
begin
ctx := stragg_agg_type( vcarray() );
return ODCIConst.Success;
end;
member function odciaggregateiterate(self in out stragg_agg_type,
val vcobj) return number is
begin
if val.nid is not null then
self.l_total.extend;
self.l_total(self.l_total.count) := val;
end if;
return ODCIConst.Success;
end;
member function odciaggregatemerge(self in out stragg_agg_type,
ctx2 stragg_agg_type)
return number as
begin
for i in 1..ctx2.l_total.count
loop
l_total.extend;
l_total( l_total.count ) := ctx2.l_total(i);
end loop;
return odciconst.success;
end;
member function odciaggregateterminate(self in stragg_agg_type,
returnvalue out clob,
flags in number)
return number as
begin
for x in (select nid,ntxt from table(self.l_total) order by 1)
loop
returnvalue := returnvalue ||x.ntxt;
end loop;
returnvalue := trim(';' from returnvalue);
return odciconst.success;
end;
end;
/
create or replace function stragg(x vcobj)
return clob
parallel_enable
aggregate using stragg_agg_type;
/
May 26, 2020 - 5:10 am UTC
Is it good enough for each element to be capped at vc2(4000) ? and just have a clob as result ?
SQL>
SQL> create table t ( x number,y number, z varchar2(1000) );
Table created.
SQL>
SQL> insert into t (x,y,z)
2 select trunc(rownum/10) x, mod(rownum,10) y, rpad('*',1000,'*') z
3 from dba_objects;
72698 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> drop type stragg_agg_type;
Type dropped.
SQL>
SQL> drop type vcarray;
Type dropped.
SQL>
SQL> create or replace type vcobj is object( nid number, ntxt varchar2(4000) ); <<=====
2 /
Type created.
SQL>
SQL> create or replace type vcarray is table of vcobj
2 /
Type created.
SQL>
SQL> create or replace type stragg_agg_type as object
2 (
3 l_total vcarray,
4
5 static function odciaggregateinitialize(ctx in out stragg_agg_type) return number,
6
7 member function odciaggregateiterate(self in out stragg_agg_type,
8 val vcobj) return number,
9
10 member function odciaggregatemerge(self in out stragg_agg_type,
11 ctx2 stragg_agg_type) return number,
12
13 member function odciaggregateterminate(self in stragg_agg_type,
14 returnvalue out clob,
15 flags in number) return number
16 );
17 /
Type created.
SQL>
SQL> create or replace type body stragg_agg_type is
2 static function odciaggregateinitialize(ctx in out stragg_agg_type)
3 return number is
4 begin
5 ctx := stragg_agg_type( vcarray() );
6 return ODCIConst.Success;
7 end;
8
9 member function odciaggregateiterate(self in out stragg_agg_type,
10 val vcobj) return number is
11 begin
12 if val.nid is not null then
13 self.l_total.extend;
14 self.l_total(self.l_total.count) := val;
15 end if;
16 return ODCIConst.Success;
17 end;
18
19 member function odciaggregatemerge(self in out stragg_agg_type,
20 ctx2 stragg_agg_type)
21 return number as
22 begin
23 for i in 1..ctx2.l_total.count
24 loop
25 l_total.extend;
26 l_total( l_total.count ) := ctx2.l_total(i);
27 end loop;
28 return odciconst.success;
29 end;
30
31 member function odciaggregateterminate(self in stragg_agg_type,
32 returnvalue out clob,
33 flags in number)
34 return number as
35 begin
36 --dbms_lob.createtemporary(returnvalue,true);
37 for x in (select nid,ntxt from table(self.l_total) order by 1)
38 loop
39 returnvalue := returnvalue ||x.ntxt;
40 --dbms_lob.append(returnvalue,x.ntxt);
41 end loop;
42 returnvalue := trim(';' from returnvalue);
43 return odciconst.success;
44 end;
45 end;
46 /
Type body created.
SQL>
SQL> create or replace function stragg(x vcobj)
2 return clob
3 parallel_enable
4 aggregate using stragg_agg_type;
5 /
Function created.
SQL>
SQL>
SQL> explain plan for
2 create table t1
3 nologging
4 parallel 4
5 as
6 select x,stragg( vcobj(y,z) ) x1
7 from t
8 group by x ;
Explained.
SQL>
SQL> create table t1
2 nologging
3 parallel 4
4 as
5 select x,stragg( vcobj(y,z) ) x1
6 from t
7 group by x ;
Table created.
SQL>
SQL>
ORA 600 on Stragg
Rajeshwaran, Jeyabal, May 26, 2020 - 5:44 am UTC
Thanks, thats works for us.
But how did you identified that this particular ORA-600 was from Clob attribute of the object instance?
what mechanism/tool/Trace event was used to identity this?
May 27, 2020 - 1:03 am UTC
process of elimination....The biggest of hitting an oracle bug is when you mix lots of different technologies, because its so hard to test for those boundary conditions. So you had:
- object types
- clobs
- user defined aggregate
- table functions
- concatentation operator on clobs
- parallel plsql
- parallel ddl
Removing parallel also works but I assume that would be a problem in terms of your performance needs.
So then I tried piecemeal removing/replacing things....eg
- dbms_lob for append
- remove table function (we lost ordering but just to see if that was the issue)
- etc etc