OraFAQ Forum: SQL & PL/SQL » ORA-30928: Connect by filtering phase runs out of temp tablespace 30928. 00000

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-30928: Connect by filtering phase runs out of temp tablespace 30928. 00000 - "Connect by filter (Oracle 11G R2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-30928: Connect by filtering phase runs out of temp tablespace 30928. 00000 - "Connect by filter [message #650766]

Tue, 03 May 2016 04:46

Kanagaraj2007
Messages: 3
Registered: May 2016
Location: BANGALORE

Junior Member

Hi All,
I am trying to execute the below Query:
select C.namecontainerinfo ContainerName,
'/Default'||sys_connect_by_path(D.NAME,'/') FOLDER_PATH from pdmlinkproduct C,
subfolder D,WTPart S
where
D.CLASSNAMEKEYCONTAINERREFEREN='wt.pdmlink.PDMLinkProduct' AND
C.ida2a2=D.IDA3CONTAINERREFERENCE and s.ida3b2folderinginfo<>D.ida2a2
D.IDA2A2 not in (select IDA3B2FOLDERINGINFO from wtpart)
D.IDA2A2 not in (select IDA3B2FOLDERINGINFO from wtdocument) and
D.IDA2A2 not in (select IDA3B2FOLDERINGINFO from epmdocument) and
D.IDA2A2 not in (select IDA3B2FOLDERINGINFO from wtchangeissue) and
D.IDA2A2 not in (select IDA3B2FOLDERINGINFO from wtchangeorder2) and
D.IDA2A2 not in (select IDA3B2FOLDERINGINFO from wtchangerequest2) and
D.IDA2A2 not in (select IDA3B2FOLDERINGINFO from subfolder)
START WITH D.IDA3B2FOLDERINGINFO=0
CONNECT BY D.IDA2A2=D.IDA3B2FOLDERINGINFO ORDER BY D.IDA3CONTAINERREFERENCE;

Getting the below Error:
ORA-30928: Connect by filtering phase runs out of temp tablespace
30928. 00000 - "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: Please retry the query with the NO_FILTERING hint. If the same error
still occurs, then increase temp tablespace.

Please help me on this above Query what is the Wrong Loop in this?

Report message to a moderator

Re: ORA-30928: Connect by filtering phase runs out of temp tablespace 30928. 00000 - "Connect by filter [message #650767 is a reply to message #650766]

Tue, 03 May 2016 04:56

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Did you try what the "Action" tell you to try?

I'd first change:
D.IDA2A2 not in (select IDA3B2FOLDERINGINFO from wtpart)
D.IDA2A2 not in (select IDA3B2FOLDERINGINFO from wtdocument) and
D.IDA2A2 not in (select IDA3B2FOLDERINGINFO from epmdocument) and
D.IDA2A2 not in (select IDA3B2FOLDERINGINFO from wtchangeissue) and
D.IDA2A2 not in (select IDA3B2FOLDERINGINFO from wtchangeorder2) and
D.IDA2A2 not in (select IDA3B2FOLDERINGINFO from wtchangerequest2) and
D.IDA2A2 not in (select IDA3B2FOLDERINGINFO from subfolder)
to

D.IDA2A2 not in 
  (select IDA3B2FOLDERINGINFO from wtpart union all
   select IDA3B2FOLDERINGINFO from wtdocument union all
   select IDA3B2FOLDERINGINFO from epmdocument union all
   select IDA3B2FOLDERINGINFO from wtchangeissue union all
   select IDA3B2FOLDERINGINFO from wtchangeorder2 union all
   select IDA3B2FOLDERINGINFO from wtchangerequest2 union all
   select IDA3B2FOLDERINGINFO from subfolder)

Note that your CONNECT BY condition does not contain any PRIOR operator and so I bet this will either return nothing or loop for ever.

Report message to a moderator

Re: ORA-30928: Connect by filtering phase runs out of temp tablespace 30928. 00000 - "Connect by filter [message #650802 is a reply to message #650767]

Wed, 04 May 2016 02:26

Kanagaraj2007
Messages: 3
Registered: May 2016
Location: BANGALORE

Junior Member

Thanks Michel.

Report message to a moderator

Re: ORA-30928: Connect by filtering phase runs out of temp tablespace 30928. 00000 - "Connect by filter [message #650803 is a reply to message #650802]

Wed, 04 May 2016 02:29

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

So the final solution is?
Post it for future readers who will encounter the same error.

Report message to a moderator

Re: ORA-30928: Connect by filtering phase runs out of temp tablespace 30928. 00000 - "Connect by filter [message #650810 is a reply to message #650803]

Wed, 04 May 2016 04:11

Kanagaraj2007
Messages: 3
Registered: May 2016
Location: BANGALORE

Junior Member

After Increasing the Temp Space in oracle the Query worked Fine.

Report message to a moderator

Re: ORA-30928: Connect by filtering phase runs out of temp tablespace 30928. 00000 - "Connect by filter [message #650812 is a reply to message #650810]

Wed, 04 May 2016 04:46

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I'm not sure about that but if you are happy with it...

Report message to a moderator

Previous Topic:	How to suppress messages from FTP server?
Next Topic:	How can I read a variable runtime in a stored proc?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 22:30:23 CDT 2024