Re: Stuck on insert into a global temporary table

In article <92srs3$3v$1_at_nnrp1.deja.com>,   roman_stangel_at_my-deja.com wrote:
> Hello,
>
> The symptoms of my problem are that execution of
> a packaged stored procedure gets (inexplicably)
> stuck on an insert into a global temporary
> table. A dynamic performance view v$sqlarea
> confirms that the session is executing the
> insert. When trying to kill such a session, I
> get a message that it can’t be killed and that it
> can only be marked for removal. Trying to
> shutdown the database in NORMAL or IMMEDIATE mode
> does not work either (waits indefinitely) and
> only SHUTDOWN ABORT mode is effective.
>
> I would love to hear from anyone who could shed
> some light on why the execution gets stuck on the
> insert statement. Here are some details:
>
> I am running Oracle 8.1.6.1.0 on RedHat 6.0.
>
> I checked for lock collisions but found none.
>
> I have found out that a certain critical mass of
> work is necessary to be performed in the
> procedure before the problem exhibits itself.
>
> Also, I found that it makes no difference if the
> insertion into the temporary table is done in
> one “insert from select” statement, or they are
> inserted one by one. This second option allowed
> me to find out that it gets stuck consistently
> after inserting a certain number of records.
> (The number records inserted before the procedure
> gets stuck is consistent unless certain execution
> conditions change, such as a change in temporary
> table space definition – see below.)
> ------------- removed -------------
>

I would guess that you are out of temporary datafile space.

Temporary tables are written only into temporary datafiles. You will not see locks.

One possibility is that your system is slow in generating more dataspace for the temporary datafiles, or that your temporary tablespace is not autoextenting.

You might want to list the information on your temporary tablespace -- size, extents, autoextend or not, etc.

--
Michael Krolewski
Rosetta Inpharmatics
mkrolewski_at_rii.com
              Usual disclaimers


Sent via Deja.com
http://www.deja.com/