Why are temp extents not in TEMP?

From: <argosy22_at_my-deja.com>
Date: Tue, 12 Dec 2000 22:39:29 GMT
Message-ID: <9169ev$85t$1@nnrp1.deja.com>

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 - 16:39:29 CST