Running 10g RAC (10.1.0.3) on Redhat ES 3.0.

First of all, I'm new to the Oracle world so if I am way off track, please
correct me.
My understanding of a temp tablespace and its function is that a temp extent
is not "deallocated" once allocated for sorting ops and will remained
allocated for the life of the instance. However, once allocated, they can be
reused by new sessions. Is that correct? How can one tell what segments are
available for reuse? I have used "select * from v$sort_usage;" and it
returns o rows. If an application connects to the database, and is running a
dynamic sql loop statement, won't that continually expand the temp
tablespace file size until it reaches the physical storage limit or max size
configed limit, only to return an error of not being able to extend the
table? I am needing a little more clarificaton as I am debating an issue
with the database vs. the app such that the file layout is not incorrect
with regards to space requirements, but the application will chew up all the
space no matter what the storage limit is. Of course, shutting down and
restarting the instance will flush the extents? Please advise on that.

Also, if the connection with the loop sql statement is closed. Won't the
allocated segments in the temp tablespace be reused if the application
reconnects and starts the same sql loop statement again? Is there a way to
check if that is happening or if the subsequent connection is only adding to
the previously used segments. The main issue is a constantly growing temp
tablespace file. What is the best way to definitively see what is happening
with the temp tablespace when the app connects, disconnects, then
reconnects. Does it reuse segments, does it keep adding/creating/extending?
Any help is greatly appreciated. TIA.

S G

SG wrote:

> Running 10g RAC (10.1.0.3) on Redhat ES 3.0.
>
> First of all, I'm new to the Oracle world so if I am way off track, please
> correct me.
> My understanding of a temp tablespace and its function is that a temp extent
> is not "deallocated" once allocated for sorting ops and will remained
> allocated for the life of the instance. However, once allocated, they can be
> reused by new sessions. Is that correct? How can one tell what segments are
> available for reuse? I have used "select * from v$sort_usage;" and it
> returns o rows. If an application connects to the database, and is running a
> dynamic sql loop statement, won't that continually expand the temp
> tablespace file size until it reaches the physical storage limit or max size
> configed limit, only to return an error of not being able to extend the
> table? I am needing a little more clarificaton as I am debating an issue
> with the database vs. the app such that the file layout is not incorrect
> with regards to space requirements, but the application will chew up all the
> space no matter what the storage limit is. Of course, shutting down and
> restarting the instance will flush the extents? Please advise on that.
>
> Also, if the connection with the loop sql statement is closed. Won't the
> allocated segments in the temp tablespace be reused if the application
> reconnects and starts the same sql loop statement again? Is there a way to
> check if that is happening or if the subsequent connection is only adding to
> the previously used segments. The main issue is a constantly growing temp
> tablespace file. What is the best way to definitively see what is happening
> with the temp tablespace when the app connects, disconnects, then
> reconnects. Does it reuse segments, does it keep adding/creating/extending?
> Any help is greatly appreciated. TIA.
>
> S G

Actually one pretty much ignores the temp tablespace unless and until
there is a specific reason, generally beginning with ORA-, to do
otherwise.

The architecture and concepts are all documented at
http://tahiti.oracle.com.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

use tablespace quotas.

the relevant view is dba_ts_quotas.

Far better for bad sql to fail on a quota exceeded than having no free
space on the filesystem (or mount point).

e.g:
SQL> alter user <app_user> quota 1024M on temp;

I can recall in 7.3 (before I was a DBA) a user had a permanent
tablespace set as hit temp tablespace ... his query failed when the
file extended to the OS limit (was a 4KB blocksize). you're in for some
serious maintenance to reclaim that space.

-bdbafh

<bdbafh@gmail.com> wrote in message news:1109977367.225418.207430@o13g2000cwo.googlegr oups.com...
> use tablespace quotas.
>
> the relevant view is dba_ts_quotas.
>
> Far better for bad sql to fail on a quota exceeded than having no free
> space on the filesystem (or mount point).
>
> e.g:
> SQL> alter user <app_user> quota 1024M on temp;
>
> I can recall in 7.3 (before I was a DBA) a user had a permanent
> tablespace set as hit temp tablespace ... his query failed when the
> file extended to the OS limit (was a 4KB blocksize). you're in for some
> serious maintenance to reclaim that space.
>
> -bdbafh
>

I don't understand what you are suggesting here?
If temp is a temporary tablespace then your quota command will
be ignored. You cannot assign quota to users on temporary tablespaces.

Anurag

SG wrote:
> Running 10g RAC (10.1.0.3) on Redhat ES 3.0.
>
> First of all, I'm new to the Oracle world so if I am way off track,
please
> correct me.
> My understanding of a temp tablespace and its function is that a temp
extent
> is not "deallocated" once allocated for sorting ops and will remained

> allocated for the life of the instance. However, once allocated, they
can be
> reused by new sessions. Is that correct? How can one tell what
segments are
> available for reuse? I have used "select * from v$sort_usage;" and it

> returns o rows. If an application connects to the database, and is
running a
> dynamic sql loop statement, won't that continually expand the temp

If you have support,
http://metalink.oracle.com/metalink/...p_id=1039341.6
and the links at the bottom of it are pretty informative, even if they
don't say much about 10. I would strongly recommend signing up for
metalink if you haven't already.

http://www.dbaoracle.net/readme-cdos.htm

jg
--
@home.com is bogus.
https://businessfilings.ss.ca.gov/fr...y number=NULL

Thanks for the help/info. I got a better understanding now.

Sean


"Joel Garry" <joel-garry@home.com> wrote in message
news:1109979269.808153.75490@o13g2000cwo.googlegro ups.com...
>
> SG wrote:
>> Running 10g RAC (10.1.0.3) on Redhat ES 3.0.
>>
>> First of all, I'm new to the Oracle world so if I am way off track,
> please
>> correct me.
>> My understanding of a temp tablespace and its function is that a temp
> extent
>> is not "deallocated" once allocated for sorting ops and will remained
>
>> allocated for the life of the instance. However, once allocated, they
> can be
>> reused by new sessions. Is that correct? How can one tell what
> segments are
>> available for reuse? I have used "select * from v$sort_usage;" and it
>
>> returns o rows. If an application connects to the database, and is
> running a
>> dynamic sql loop statement, won't that continually expand the temp
>
> If you have support,
> http://metalink.oracle.com/metalink/...p_id=1039341.6
> and the links at the bottom of it are pretty informative, even if they
> don't say much about 10. I would strongly recommend signing up for
> metalink if you haven't already.
>
> http://www.dbaoracle.net/readme-cdos.htm
>
> jg
> --
> @home.com is bogus.
> https://businessfilings.ss.ca.gov/fr...y number=NULL
>