Re: Help with ORA-01000 Error
From: John Hurley <johnthehurley_at_gmail.com>
Date: Mon, 17 Jun 2013 15:33:33 -0700 (PDT)
Message-ID: <a40cd088-7f6f-4b7e-89d5-b58c7ca610d5_at_o10g2000yqo.googlegroups.com>
On Jun 17, 11:29 am, "StrangeCat_at_Work" <stra..._at_cat.com> wrote:
> Hi everyone,
>
> we have to convert a fairly big application suite to use Oracle instead of
> SQL Server but we do not have much experience on the latter.
>
> After testing our app for some time we optimized all queries to use Oracle
> and now everything runs as expected. After all, apart being very big, its
> just a lot of INSERTS, UPDATES and DELETES, nothing complicated...
>
> We encountered problems with a small utility we use to build the schema and
> to load some "semi-static" data in tables.
>
> Creating the schema (tables, constraints, indexes, sequences and some very
> simple triggers) forced us to raise the maximum open cursors to 1500 on our
> test box, this solved the problem. I read that the default for max open
> cursors is quite low so I thought that on the client production server I
> wouldnt have had this problem, and i was right.
>
> The problem arised trying to insert all the data via a script that would
> launch around 1500 INSERTS, this wouldnt run and return the ORA-01000
> Error...
Date: Mon, 17 Jun 2013 15:33:33 -0700 (PDT)
Message-ID: <a40cd088-7f6f-4b7e-89d5-b58c7ca610d5_at_o10g2000yqo.googlegroups.com>
On Jun 17, 11:29 am, "StrangeCat_at_Work" <stra..._at_cat.com> wrote:
> Hi everyone,
>
> we have to convert a fairly big application suite to use Oracle instead of
> SQL Server but we do not have much experience on the latter.
>
> After testing our app for some time we optimized all queries to use Oracle
> and now everything runs as expected. After all, apart being very big, its
> just a lot of INSERTS, UPDATES and DELETES, nothing complicated...
>
> We encountered problems with a small utility we use to build the schema and
> to load some "semi-static" data in tables.
>
> Creating the schema (tables, constraints, indexes, sequences and some very
> simple triggers) forced us to raise the maximum open cursors to 1500 on our
> test box, this solved the problem. I read that the default for max open
> cursors is quite low so I thought that on the client production server I
> wouldnt have had this problem, and i was right.
>
> The problem arised trying to insert all the data via a script that would
> launch around 1500 INSERTS, this wouldnt run and return the ORA-01000
> Error...
Yes default for open cursors is typically too low and should be set to something reasonable ( 750 / 900 fairly common ). Often no big issue going somewhat higher than that.
If this is running in a script of some kind doing an "alter session set cursor_sharing=FORCE" ( or an after logon database trigger to do that for these scripts ) could be an acceptable workaround.
Creating an external table and doing "1 insert command" processing against the external table is also worth looking at.
Something may be wrong with connection management Received on Tue Jun 18 2013 - 00:33:33 CEST