Re: ORA-30009

From: Tanel Poder <tanel_at_tanelpoder.com>
Date: Mon, 16 May 2016 17:46:30 -0500
Message-ID: <CAMHX9JL1vnuskVuCpnPet392zfH1SC7FHZ85VqLq-C5bo5Vnpg_at_mail.gmail.com>

You can generate rows with connect by safely too (without using too much memory). Create a cartesian product of some small connect by results like this:

SQL> select count(r)
  2 from (

  3             select rownum r
  4             from
  5                     (select rownum r from dual connect by rownum <=
1000) a,
  6                     (select rownum r from dual connect by rownum <=
1000) b,
  7                     (select rownum r from dual connect by rownum <=
1000) c
  8             where rownum <= 100000000
  9     )

 10 /

  COUNT(R)

---

 100000000

http://blog.tanelpoder.com/2008/06/08/generating-lots-of-rows-using-connect-by-safely/

The "ORA-30009: Not enough memory for CONNECT BY operation" check & error was introduced in Oracle 11g and it's independent from auto-PGA management. Before 11g you could consume all server memory (or hit an ORA-4030 with a single infinite connect by query).

--
Tanel
P.S. Gluent New World #03
<http://blog.tanelpoder.com/2016/05/16/gluent-new-world-03-real-time-stream-processing-in-modern-enterprises-with-gwen-shapira/>
webinar about Kafka & real-time streaming with Gwen Shapira on next
tuesday! :)



On Mon, May 16, 2016 at 2:02 PM, Rich J <rjoralist3_at_society.servebeer.com>
wrote:



> On 2016/05/16 13:53, Sayan Malakshinov wrote:

>

> I've fixed the queries, but xmltable is still fastest one. You can check

> it yourself:

> https://gist.github.com/xtender/d900ee3ef37b6c06bb9020b31d1d39dd

>

>

> My bad -- I forgot to mention that I had also changed the first query to

> produce the same results as the second group by changing the "select

> rownum" to "select count(*)", thereby eliminating all but one of the 10M

> rows from being returned.

>

> Rich

>

>


--
http://www.freelists.org/webpage/oracle-l

Received on Tue May 17 2016 - 00:46:30 CEST