Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Why are temp extents not in TEMP?

Re: Why are temp extents not in TEMP?

From: <yong321_at_yahoo.com>
Date: Tue, 12 Dec 2000 23:51:01 GMT
Message-ID: <916dl4$bnj$1@nnrp1.deja.com>

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/ Received on Tue Dec 12 2000 - 17:51:01 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US