Re: Can't insert into partition

Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, August 06, 2003 6:39 PM We have a situation where a process can't insert into a partition of a partitioned table. The process just keeps running. - A stored procedure executes a SQL insert statement (listing

RE: Can't insert into partition

Wolfgang Thank you so much! You spotted something that we had overlooked! The dot/comma was indeed the problem. And thanks to you and everyone else for the help that helped narrow the problem down to this point. It seems that as you pointed out, the hint had a syntax error all along, but CBO

RE: Can't insert into partition

? Henry -Original Message- DENNIS WILLIAMS Sent: Wednesday, August 06, 2003 11:40 AM To: Multiple recipients of list ORACLE-L We have a situation where a process can't insert into a partition of a partitioned table. The process just keeps running. - A stored procedure executes a SQL insert

RE: Can't insert into partition

Dennis, I am not pretty sure but you can try to increase degree of your table/index to 1 .. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 07 Aug 2003 14:19:23 -0800 Thanks Wolfgang! And thanks to the others who have helped

RE: Can't insert into partition

--- - From: DENNIS WILLIAMS [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wed, 06 Aug 2003 07:39:36 We have a situation where a process can't insert into a partition of a partitioned table. The process just keeps running. - A stored procedure executes

RE: Can't insert into partition

But then it's not the same sql anymore and the access plan can be wildly different. You need to use bind variables in your sqlplus session as well. Unfortunately, even then it is not guaranteed that you'll get the same plan as you get in the plsql proc. At 06:44 AM 8/7/2003 -0800, you wrote:

Re: Can't insert into partition

? Henry -Original Message- DENNIS WILLIAMS Sent: Wednesday, August 06, 2003 11:40 AM To: Multiple recipients of list ORACLE-L We have a situation where a process can't insert into a partition of a partitioned table. The process just keeps running. - A stored procedure executes a SQL

RE: Can't insert into partition

You are welcome. Happens a lot. You see what should be there rather than what IS there and wonder why it's not working as designed. Someone else, uninvolved, comes along, takes one look at the thing, points out the error and leaves you (me) feeling like an idiot. At 12:54 PM 8/8/2003 -0800,

RE: Can't insert into partition

To: Multiple recipients of list ORACLE-L We have a situation where a process can't insert into a partition of a partitioned table. The process just keeps running. - A stored procedure executes a SQL insert statement (listing below). - It normally completes in 30 minutes, but now just runs for hours

RE: Can't insert into partition

Wolfgang - Yes, you are correct, it is using bind variables. To run the SQL standalone, we manually change these to literal variables. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, August 07, 2003 12:19 AM To: Multiple

RE: Can't insert into partition

Thanks Wolfgang! And thanks to the others who have helped us unravel this problem. Your suggestion put us on the right track. I started running a SQL trace/tkprof, and lo and behold, when the stored procedure submits the SQL, CBO does everything as NESTED LOOPS. The next question is how to induce

RE: Can't insert into partition

Is the sql you posted the exact sql as it is executed in the PLSQL procedure, i.e. is the procedure using literals such as 2004 in the predicates for sourcefiscalyear, or is it really using a bindvariable? At 02:29 PM 8/6/2003 -0800, you wrote: Henry - Thanks. I feel like I'm getting an

RE: Can't insert into partition

a situation where a process can't insert into a partition of a partitioned table. The process just keeps running. - A stored procedure executes a SQL insert statement (listing below). - It normally completes in 30 minutes, but now just runs for hours. - Oracle 8.1.6 on Dec/Compaq/HP Alpha

RE: Can't insert into partition

No, you can put a hint in inner sql and subselects. Some hints you NEED to put on a subselect to make any sense. Is that sql verbatim? The hint has a syntax error. There is a dot rather than a comma after ps which - pooof - may turn the princely hint into an ugly toad (no pun intended) comment.

RE: Can't insert into partition

Dennis, Could you plese post the v$session_wait. Do you have a 10046 trace? Henry -Original Message- DENNIS WILLIAMS Sent: Wednesday, August 06, 2003 11:40 AM To: Multiple recipients of list ORACLE-L We have a situation where a process can't insert into a partition of a partitioned

RE: Can't insert into partition

ORACLE-L We have a situation where a process can't insert into a partition of a partitioned table. The process just keeps running. - A stored procedure executes a SQL insert statement (listing below). - It normally completes in 30 minutes, but now just runs for hours. - Oracle 8.1.6 on Dec/Compaq/HP

RE: Can't insert into partition

plese post the v$session_wait. Do you have a 10046 trace? Henry -Original Message- DENNIS WILLIAMS Sent: Wednesday, August 06, 2003 11:40 AM To: Multiple recipients of list ORACLE-L We have a situation where a process can't insert into a partition of a partitioned table. The process