Re: This one got me - server stucked

pagesflames_at_usa.net (Dusan Bolek) wrote in message news:<1e8276d6.0206110549.ea6d6e3_at_posting.google.com>...
> My testing server 8.1.7.1.1 EE on WIN NT is making a great fun from
> me. I've sent a 4MB script with DDL commands. After processing about
> 70% of this script server has got stucked. SQL*Plus window looks like
> processing script, but in spool file, no more lines with completed
> commands are created.
> After one hour I tried to learn what happened. The only sensible
> information I've got from v$session_wait where for this session is
> state WAITING and reason is: SQL*Net message from client.
> That's very confusing because I'm running this script directly on
> server, so no network issues can play a role. Also listener is
> running, and I can with no problem connect to database and run all
> commands from other SQL*Plus.
> Alert log, listener log, sqlnet.log didn't show any message about
> problem. Same is with event log in NT. There is a lot of swap space,
> disk space, tablespace space, rollback segment can grow as they like.
> Database is not in ARCHIVELOG. Output of script (from spool file)
> doesn't show any errors, which can has something common with this
> behaviour of server.
> Things like wait states, sql_area etc. looks good, but sqls are frozen
> and the last one is still the same.
> To be honest, I must admit, that this is a new thing to me and looks
> like the new (and very good) try of Oracle server to entertain me. :-)
>
> --
> _________________________________________
>
> Dusan Bolek, Ing.
> Oracle team leader
>
> Note: pagesflames_at_usa.net has been cancelled due to changes (maybe we
> can call it an overture to bankruptcy) on that server. I'm still using
> this email to prevent SPAM. Maybe one day I will change it and have a
> proper mail even for news, but right now I can be reached by this
> email.

What about latches and locks?
Also, did you check v$resource_limit to see if the rdbms ran out of anything?

The 'sqlnet waiting on client' makes it seem like either your front-end session got disconnected from the backend user process or Oracle is waiting on you to hit enter. Could one of the commands be missing an ending ';' or '/'? Or are there any &variables that could be causing a wiat. It seems to me I have seen developers create scripts that suppress output but want an input.

Curious. Have you tried to rerun this?

• Mark D Powell ---