Re: Why are temp extents not in TEMP?

From: <argosy22_at_my-deja.com>
Date: Wed, 13 Dec 2000 14:57:36 GMT
Message-ID: <9182ot$kfs$1@nnrp1.deja.com>

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/ Received on Wed Dec 13 2000 - 08:57:36 CST