Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2004
    Posts
    2

    Unanswered: what makes temp01.dbf so big?

    we got a big temp01.dbf (about 2GB). Want to find out what makes it so big? And also, is there any way to drop and recreate the table on schedule? Any idea would be great appreciated.

    Tien-Chih Wang

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >what makes it so big?
    The DBA is responsible for this.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    lol, not if you had auto-extend on.

    are you talking used space or space allocation?

    what % of the 2Gig is used space?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >not if you had auto-extend on.
    But the DBA would have been the one who had turned on auto-extend.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by anacedent
    >not if you had auto-extend on.
    But the DBA would have been the one who had turned on auto-extend.
    I stand corrected.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    We have met the enemy, and they is us!

    Which is better, auto-extend or no auto-extend?
    Is this a case of you can pay me now or you can pay me later?
    TANSTAAFL!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Aug 2004
    Posts
    2
    I'm talking about used space, not allocated.
    What kind sql query would make effect on temp01.dbf?

  8. #8
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    tienchih,

    If you examine your users' details you will most likely find that their temporary tablespace is set to 'TEMP'; TEMP01.DBF is the file using this tablespace. What this means is that your users are all using temp01.dbf for any & all temporary storage needs - don't worry about this, it's how Oracle works. And although the tablespace may show as being almost full, Oracle will fill it up then just go back to the start & re-use any temporary space that is no longer needed (it doesn't get released automatically, so although you're 'using' 2Gb this is not strictly true - it just means that you've 'used' 2Gb).

    As for your temp01.dbf being big, the one we have here is 20Gb!!! The size depends on the number of users you have, how 'busy' they are, what they're doing, what queries are running & what they're doing, what the DBA is doing etc etc etc...
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    As for auto-extend vs no-extend, I have never been a fan of autoextend. If you have a run-away process that is filling up a temp space or table, you can fill up your disks very fast. I have scheduled processes that monitor all my tablespaces and when one gets 90% full, I am notified and I extend the datafiles. If it is going up to fast, I can check out why and fix the problem.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  10. #10
    Join Date
    Jul 2003
    Posts
    2,296
    nothing wrong with auto-extend if everything is already configured

    anyways, temp tablespace is used for sorting/ordering/aggregation.
    I imagine your application is sending in a lot of queries that do these
    types of things.

    honestly, disk space is cheap. 2Gig is not big at all when you think about it.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  11. #11
    Join Date
    Sep 2004
    Posts
    5
    We do use a lot of sorting/ordering/aggregation.
    By the way, If I don't wanna make the temp01.dbf getting bigger, would it help to set more index? or even worse?

    Thank you.

    Tien-Chih Wang
    Last edited by tienchihwang; 09-21-04 at 21:42.

  12. #12
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by The_Duck
    nothing wrong with auto-extend if everything is already configured

    anyways, temp tablespace is used for sorting/ordering/aggregation.
    I imagine your application is sending in a lot of queries that do these
    types of things.

    honestly, disk space is cheap. 2Gig is not big at all when you think about it.
    I would never have auto_extend turned on for a temp datafile. If you don't believe me, then have auto_extend on and issue the following select

    select a.object_name, b.object_name,c.object_name,d.object_name,e.object _name
    from dba_objects a,dba_objects b, dba_objects c, dba_objects d dba_objects e;

    This cartesian join will cause your datafile to expand and fill your disk. If all your selects are pre-tested and no ad-hoc queries are allowed, fine. Otherwise let it blow out with a can't get extent error if it is a run away.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  13. #13
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354

    A compromise...

    Maybe we could compromise... auto-extending but with a stopping point before the drive fills up... that's what the MAXSIZE parameter is for.

    ALTER DATABASE
    DATAFILE '/u02/oradata/orcl/temp01.dbf' AUTOEXTEND ON
    MAXSIZE 2048M;


    JoeB

  14. #14
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by joebednarz
    Maybe we could compromise... auto-extending but with a stopping point before the drive fills up... that's what the MAXSIZE parameter is for.

    ALTER DATABASE
    DATAFILE '/u02/oradata/orcl/temp01.dbf' AUTOEXTEND ON
    MAXSIZE 2048M;


    JoeB
    A great compromise and that is my final comment on this topic 8-)
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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