Re: Help with ORA-01000 Error
From: joel garry <joel-garry_at_home.com>
Date: Mon, 17 Jun 2013 09:19:12 -0700 (PDT)
Message-ID: <a0bacea0-d045-40fb-9cff-183f1504c258_at_ow4g2000pbc.googlegroups.com>
On Jun 17, 8: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...
>
> I read some docs on how to optimize this but found no logical solution...
>
> If I dont have access to Oracle instance parameters the only way looks to be
> splitting that long script in smaller ones and open and close the connection
> each time... Ugly but should work...
>
> We use dotnet 2.0 with OleDB (cannot move to Oracle Manged providers right
> now...).
>
> Any idea/comment highly appreciated.
>
> TIA
> ASC
It's likely either you are using something that needs to be updated, or are not closing cursors in a manner that your environment considers rational. See
http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/883bc30b-9ea9-40c9-90fb-85ce408eecc9/
Date: Mon, 17 Jun 2013 09:19:12 -0700 (PDT)
Message-ID: <a0bacea0-d045-40fb-9cff-183f1504c258_at_ow4g2000pbc.googlegroups.com>
On Jun 17, 8: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...
>
> I read some docs on how to optimize this but found no logical solution...
>
> If I dont have access to Oracle instance parameters the only way looks to be
> splitting that long script in smaller ones and open and close the connection
> each time... Ugly but should work...
>
> We use dotnet 2.0 with OleDB (cannot move to Oracle Manged providers right
> now...).
>
> Any idea/comment highly appreciated.
>
> TIA
> ASC
It's likely either you are using something that needs to be updated, or are not closing cursors in a manner that your environment considers rational. See
http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/883bc30b-9ea9-40c9-90fb-85ce408eecc9/
jg
-- _at_home.com is bogus. http://www.forbes.com/sites/oracle/2013/06/17/complexity-barrier-makes-it-matter-more-than-ever/Received on Mon Jun 17 2013 - 18:19:12 CEST