Temp segment

Some confusing on temp segment, could anyone clarify for me,

1. If a temp segment is using by one transaction, is it possible another
transaction will try to use the same tmp segment?

2. Is there anything about contention of temp segment?

3. If a single temp segment occupied almost most the space of the tempoary
tablespace. Is it possible that another transaction can not proceed due to
lack of space to have another temp segment.

Ans:

1. Not if the tablespace the segment is residing in is a permanent one. A new
temporary segment will be created. If the tablespace is a non-permanent
temporary tablespace (ie it has been dedicated to temporary segments only),
temporary segments will function like rollback segments: a second segment is
allocated in the already existing segment

2. AFAIK no

3. O definitely!

Question 2 and Ans:
>During my preparation for OCP, I have the following question:
>1. What does oracle do if the user's default temporary tablespace is full in
>a sort operation?

abort the operation and return an error

>2. Any dynamic view I can use to monitor the temporary segment?

select segment_name, extents, bytes / 1024 K
from dba_segments
where segment_type = 'TEMPORARY' will do the trick

>3. I think temporary segment and sort segment the same thing, are they ?

Not necessarily
Temporary segments are also used in analyzing operations, the creation of an
index etc.

Hosted by www.Geocities.ws