-
Hello,
Just wanted to know what is the use of temporary table space?
Last night I uploaded a 50 Meg file and this morning when I checked I noticed that my temp, system and dbname tablespaces are used upto 98%?
I understand that my dbname tablespace needs to be resized to something bigger but why have my system and temp datafiles reached to 98%? Shouldnt temp usually be empty when no processing is going on? And what do I do with the system datafile? Do I need to increase the size of that datafile(tablespace) too, since it is occupying 98% of the allocated space?
Thanks.
-
Quote:
Originally posted by trogen
Hello,
Just wanted to know what is the use of temporary table space?
Last night I uploaded a 50 Meg file and this morning when I checked I noticed that my temp, system and dbname tablespaces are used upto 98%?
I understand that my dbname tablespace needs to be resized to something bigger but why have my system and temp datafiles reached to 98%? Shouldnt temp usually be empty when no processing is going on? And what do I do with the system datafile? Do I need to increase the size of that datafile(tablespace) too, since it is occupying 98% of the allocated space?
Thanks.
Normally temporary tablespace is use for sorting. If oracle cannot perform the sorting in memory he uses the temporary segment. With regards to your system tablespace, the default tablespace for any user created is the system tablespace, you should specify your default tablespace when creating a user. Temp tablespace retains the largest allocation it handles to prevent allocating and deallocating space.
Ed
-
But I created the User in the dbname tablespace, not system?
Why is it then occupying 98% of allocated space?
Is that normal?
Also shouldnt my temp dataspace be more or less empty when there is absolutely no processing going on?
And is it okay if temp tablespace has used up 98% of its allocated space?
Or should be doing something to make sure my system tablespace has more space as well as my temp tablespace.
thanks.
-
Hi,
Your system tablespace should not grow unless you have enabled auditing. As far as the temp tablespace is concerned it should drop back to zero once the processing is over. Check dba-segments to see if there are any temp segments which are not dropped by oracle. If you find some then restarting the database will clean it up.
Also check the default and temporary tablespace settings for the user to make sure that nothing is getting created in system tablespace.
Baliga
-
OK, How can I check for all these settings?
If possible, could you show me some sample sql statements by which I could check these settings?
Thanks.
Also,
I know for sure that the user is not making anything in the system tablespace because while creating it I did a
Default tablespace blah
Temporary tablespace temp;
and I also did a
Revoke UNLIMITED TABLESPACE system privilege after login as
sys or system.
-
Follow this thread for some more info
-
IIRC, sort-segment space allocated in the temporary tablespace is not freed until the database is restarted.
This means that if there were some heavy sorts involved in, for example, building indexes as part of a data load, that the sort segment will not be freed up once the processing is complete. A 'bounce' of the database might sort the temp. tablespace out.
