Re: Why are temp extents not in TEMP?

From: Joe Maloney <jrpm_at_my-deja.com>
Date: Wed, 13 Dec 2000 15:20:51 GMT
Message-ID: <91844d$lr5$1@nnrp1.deja.com>

TJI: What I understand is happening is that an object, like an index, is first created in the temporary area, then copied to it's final destination. During that copy, it may be flagged as temporary, in case something goes wrong. When the operation completes, it is set to it's permanent name and allocation.

In article <9182ot$kfs$1_at_nnrp1.deja.com>, argosy22_at_my-deja.com wrote:
> Thanks,
>
> Is there any way to avoid this error?
>
> In article <916dl4$bnj$1_at_nnrp1.deja.com>,
> yong321_at_yahoo.com wrote:
> > This can happen when Oracle is building an index for the user before
> > the build is finished. It's normal.
> >
> > Yong Huang
> > yong321_at_yahoo.com
> >
> > In article <9169ev$85t$1_at_nnrp1.deja.com>,
> > argosy22_at_my-deja.com wrote:
> > > Hi all,
> > >
> > > We are using Oracle 8.1.7 on Linux.
> > >
> > > Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
> > > With the Partitioning option
> > > JServer Release 8.1.7.0.0 - Production
> > >
> > > I have been getting some strange errors today.
> > >
> > > I have a user: SOMEUSER that has a default tablespace
> > > of USERS02, and the temporary tablespace of TEMP.
> > >
> > > Select username,
> > > DEFAULT_TABLESPACE ,
> > > TEMPORARY_TABLESPACE
> > > from dba_users
> > >
> > > USERNAME DEFAULT_TABLESPACE
> > > ------------------------------ ------------------------------
> > > TEMPORARY_TABLESPACE
> > > ------------------------------
> > >
> > > SOMEUSER USERS02
> > > TEMP
> > >
> > > - so, you would expect that the temporary area would be TEMP.
> > >
> > > The user did a load using SqlLoader. We got the errors:
> > >
> > > ora-02356
> > > - db out of space
> > >
> > > ora-01630
> > > - maxextents (1990) reached in temp segment of tablespace USERS02
> > >
> > > ora-02478
> > > - merge into base segment would overflow maxextents
> > >
> > > - checking the extents:
> > >
> > > 1 Select substr(SEGMENT_NAME, 1, 25) segment_name,
> > > 2 sum(bytes) sum_of_bytes,
> > > 3 sum (blocks) sum_of_blocks,
> > > 4 count(1) extent_count
> > > 5 from dba_extents
> > > 6 group by segment_name
> > > 7* order by sum(bytes)
> > >
> > > ...
> > > 10.12610 112537600 27475 13734
> > > 7.19825 166076416 40546 19990
> > >
> > > select *
> > > from dba_extents
> > > where segment_name = '7.19825'
> > >
> > > OWNER ------------------------------ SEGMENT_NAME
> > >

> > > --- PARTITION_NAME SEGMENT_TYPE Tablespace EXTENT_ID
> > > ------------------------------ ------------------ ---------------

> > > FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO ---------- ---------
-
> > > ---------- ---------- ------------ 7.19825 TEMPORARY USERS02
15234
11
> > > 10102 8192 2 11
> > >
> > > NXVOS
> > > 7.19825
> > > TEMPORARY USERS02
> > > 15235
> > > 12 3862 8192 2 12
> > >
> > > - so the segment is temporary. But, it is found in the
> > > tablespace USERS02, not in TEMP! Why is this? Any ideas?
> > >
> > > How can I ensure that TEMP will be used?
> > >
> > > Thanks
> >
> > Sent via Deja.com
> > http://www.deja.com/
> >
>
> Sent via Deja.com
> http://www.deja.com/
>

--
Joseph R.P. Maloney, CCP,CSP,CDP
MPiR, Inc.
502-451-7404
some witty phrase goes here, I think.


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

Received on Wed Dec 13 2000 - 09:20:51 CST