DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: whats the use of temp01.dbf datafile?

  1. #1
    Join Date
    Jul 2000
    Posts
    70

    Question

    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.

  2. #2
    Join Date
    Jan 2001
    Location
    St. Louis. MO
    Posts
    151
    [QUOTE][i]Originally posted by trogen [/i]
    [B]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.
    [/B][/QUOTE]

    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

  3. #3
    Join Date
    Jul 2000
    Posts
    70
    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.

  4. #4
    Join Date
    Jan 2001
    Posts
    126
    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

  5. #5
    Join Date
    Jul 2000
    Posts
    70
    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.


  6. #6
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843

    Follow this thread for some more info

    [url]http://www.dbasupport.com/forums/showthread.php?threadid=6852[/url]
    Reddy,Sam

  7. #7
    Join Date
    Feb 2001
    Posts
    123
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width