Re: SQL+ aborts and exists
- From: Dion Cho <ukja.dion@xxxxxxxxx>
- To: gurenich@xxxxxxxxx
- Date: Thu, 11 Nov 2010 09:43:44 +0900
How about using tracing tool like strace (system call tracer) to find out
which API call crashed sql*plus?
================================
Dion Cho - Oracle Performance Storyteller
http://dioncho.wordpress.com (english)
http://ukja.tistory.com (korean)
http://sites.google.com/site/otpack (tpack)
================================
On Thu, Nov 11, 2010 at 8:10 AM, Masha Gurenich <gurenich@xxxxxxxxx> wrote:
> So, commenting out, removing, setting to OFF of the following: set
> serveroutput on didn't do a thing. Still crashes on the same spot.
>
> Any other ideas, people? Oracle Support have no idea (why am i not
> surprised..)
>
> Thanks beforehand,
> M
>
> On Mon, Nov 8, 2010 at 2:51 PM, Masha Gurenich <gurenich@xxxxxxxxx> wrote:
>
>> hehehe.
>> i will let you know how it goes tomorrow!
>> thank you for suggestions and please, pray to SQL+ god :D
>>
>> On Mon, Nov 8, 2010 at 2:39 PM, Goulet, Richard <
>> Richard.Goulet@xxxxxxxxxxx> wrote:
>>
>>> Humm, better send you some new wallboard!!
>>>
>>>
>>> Dick Goulet
>>> Senior Oracle DBA
>>>
>>>
>>> ------------------------------
>>> *From:* Masha Gurenich [mailto:gurenich@xxxxxxxxx]
>>> *Sent:* Monday, November 08, 2010 2:36 PM
>>> *To:* Goulet, Richard
>>> *Cc:* Oracle L
>>> *Subject:* Re: SQL+ aborts and exists
>>>
>>> are you kidding me? O.o
>>> i will bang myself on the wall if it works!!!! >:
>>>
>>> On Fri, Nov 5, 2010 at 3:01 PM, Goulet, Richard <
>>> Richard.Goulet@xxxxxxxxxxx> wrote:
>>>
>>>> OH Bother!! Back when I was migrating a number of databases from 9I
>>>> to 10GR2 the upgrade assistant would always fail. A look through the log
>>>> file didn't provide any insight either which is similar to your experience.
>>>> The solution recommended by OTS was to comment out the "set serveroutput
>>>> on"
>>>> statements in all of the scripts. Sure enough that fixed the problem.
>>>>
>>>>
>>>> Dick Goulet
>>>> Senior Oracle DBA
>>>>
>>>>
>>>> ------------------------------
>>>> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
>>>> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Masha Gurenich
>>>> *Sent:* Friday, November 05, 2010 12:44 PM
>>>> *To:* Oracle L
>>>> *Subject:* SQL+ aborts and exists
>>>>
>>>> Hi all,
>>>>
>>>> We hit an issue that has never came up before: SQL+ will abort and exit
>>>> in the middle of a sql script that calls 170+ another sql scripts. So let
>>>> me
>>>> explain a little bit of what's going on:
>>>>
>>>> 1) Release manager has to run about 170 sql scripts in order to complete
>>>> the release. All those scripts need to be run in a particular order (of
>>>> course). All those scripts need to be run by different users, so there is a
>>>> constant switching between usernames and prompts for the username, password
>>>> and dbname. The release manager got tired of sitting in front of the screen
>>>> and waiting for the username/pass prompt. he is willing to create some sort
>>>> of a super-bundle script that would include all those 170+ sql scripts with
>>>> the preceding credentials. And he got that script that technically looks
>>>> like this:
>>>>
>>>> vi super_bundle.sql
>>>> set SERVEROUTPUT ON
>>>> PROMPT
>>>> PROMPT Executing 1.sql
>>>> PROMPT
>>>> @@1.sql
>>>>
>>>> PROMPT
>>>> PROMPT Executing 2.sql
>>>> PROMPT
>>>> @@2.sql
>>>>
>>>> and so on 170+ times.
>>>>
>>>> 2) The problem that we are facing is that somewhere in the middle of
>>>> that super_bundle script SQL+ just dies. It terminates the session on say
>>>> script 98.sql as if there was EXIT command. If we were to look into the
>>>> script on which it fails and run it manually - it will run without any
>>>> problems. If we comment this 98.sql out - we will proceed a little bit
>>>> further along the road and will fail on say 104.sql.. Inside those scripts
>>>> are simple inserts, merges, creates etc. Nothing fancy. Sometimes, there is
>>>> a statement that will error out, for example, in 99.sql there is statement
>>>> that tries to create the view that already exists. If we comment out
>>>> 98.sql,
>>>> it will run 99.sql, will display the ORA- error about object exists, will
>>>> proceed to 100.sql and will abort on 104.sql So, ORA- errors are no not the
>>>> culprits (also, because we have WHENEVER SQLERROR CONTINUE)...
>>>>
>>>> 3) My colleague and I, we have a feeling that this has something to do
>>>> with the buffer. Reading about 10.2 release, we figured, that if we set SET
>>>> SERVEROUTPUT ON it will give us unlimited buffer size, same approach as
>>>> setting *DBMS_OUTPUT*.ENABLE (buffer_size => *NULL*); That didn't help.
>>>> We tried to disconnect/connect right before the 98.sql script - didn't
>>>> help.
>>>> We tried to clear screen - def didn't work. Unfortunately, we cannot change
>>>> the design of the release process to actually exit out of sqlplus and then
>>>> establish a new session.. I bet if we could do that, it will help us, but
>>>> we
>>>> have to work what we have. And being at 10gR2 I was hoping we do not have
>>>> those silly buffer limits anymore..
>>>>
>>>> If anybody had this problem before or have any thoughts on this, please
>>>> share!! Any help would be much appreciated!
>>>>
>>>> Thanks,
>>>> M
>>>>
>>>
>>>
>>
>
Other related posts:
