Re: Stuck on insert into a global temporary table

In article <92t2kr$675$1_at_nnrp1.deja.com>,   Mike Krolewski <mkrolewski_at_rii.com> wrote:
> 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/
>

Michael,

Thank you for looking at this. I have double-checked my table space definitions and they seem fine to me:

CREATE TABLESPACE "IPWTEMPTS2" LOGGING
DATAFILE '/home/oracle/u05/oradata/ORCL/IPWTEMPTS2.dbf' SIZE 64M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE 32767M DEFAULT STORAGE ( INITIAL 256K NEXT 256K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 ) TEMPORARY Also, when I experience the problem the temporary table space is only less than 4% full.

Roman Stangel

Sent via Deja.com
http://www.deja.com/ Received on Wed Jan 03 2001 - 03:27:57 CST