Ora-30928

[Kumar_RP]

Hi,
I am getting the following error while running my procedure.

My Query is
-----------
INSERT INTO /*+APPEND*/
INPL_INA_PERF_LOAD(identity,
parent_identity,
category,
name,
tree,
start_time,
duration_ms,
data_center,
file_name)
(SELECT identity,
parent_identity,
category,
name,
LPAD(' ',2*(LEVEL-1)) || TO_CHAR(identity) tree,
start_time,
duration_ms,
p_i_datacenter,
p_i_filename
FROM
WK_LOAD_SRC_PERF
WHERE
file_name = p_i_filename
START WITH PARENT_IDENTITY=0
CONNECT BY PRIOR IDENTITY = PARENT_IDENTITY);

I am having 15 Lakhs records in my source table.

The error is :

ERROR: -- ORA-30928: Connect by filtering phase runs out of temp tablespace

I tried to findout the details of this ORA-30928 error, But i couldn't able to trace this particular error code.

Can anyone please help me to resolve this issue.

Thanks in advance.

Regards,
PalaniKumar. R
prajagopal@inautix.com

[anupamasuresh]

Increase the temp tablespace and check out the code

[balajiyes]

ORA-30928 Connect by filtering phase runs out of temp tablespace

Cause: It is probably caused by the fact that there is a loop in the data.

Action: Retry the query with the NO_FILTERING hint. If the same error still
occurs, then increase temp tablespace.

[Kumar_RP]

Hi..
Thanks to Jegan , Anu...

[Kumar_RP]

Hi,
Increasing the temp tablespace is not that easy in my production environment.. So this is ruled out..
I did the change as Jegan suggested by Using NO_FILLERING Hints.. Still i am getting the same error..
So can anyone give some idea to rewrite this hierarchical query?..

My Existing Query is

INSERT INTO /*+ NO_FILTERING */
INPL_INA_PERF_LOAD(identity,
parent_identity,
category,
name,
tree,
start_time,
duration_ms,
data_center,
file_name)
(SELECT /*+ Parallel(C) */ identity,
parent_identity,
category,
name,
LPAD(' ',2*(LEVEL-1)) || TO_CHAR(identity) tree,
start_time,
duration_ms,
p_i_datacenter,
p_i_filename
FROM
WK_LOAD_SRC_PERF C
WHERE
file_name = p_i_filename
START WITH PARENT_IDENTITY=0
CONNECT BY PRIOR IDENTITY = PARENT_IDENTITY);


Thanks in Advance...