Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Location
    hyderabad
    Posts
    12

    Unanswered: resize temp dbfile

    hi,

    this is regarding resizing a temporary dbfile. The size of my temporary datafile(TEMP01.dbf) is 4G.B. When I checked in dba_free_space view for the free space available in my temp file, it shows 3.5G.B is free. But when I tried to resize the temp dbfile from 4G.B to 2G.B using RESIZE command it says it cannot resize the file beyond the size of the data residing in it. When I quried dba_free_space heavy sorts/transactions were running in the database. Then why did it show 3.5 G.B free space. (This is the case same with SYSTEM.DBF). pls respond immedietly.

    with thanks

    kkravi.

  2. #2
    Join Date
    Oct 2003
    Posts
    3

    Re: resize temp dbfile

    Originally posted by kkravi
    hi,

    this is regarding resizing a temporary dbfile. The size of my temporary datafile(TEMP01.dbf) is 4G.B. When I checked in dba_free_space view for the free space available in my temp file, it shows 3.5G.B is free. But when I tried to resize the temp dbfile from 4G.B to 2G.B using RESIZE command it says it cannot resize the file beyond the size of the data residing in it. When I quried dba_free_space heavy sorts/transactions were running in the database. Then why did it show 3.5 G.B free space. (This is the case same with SYSTEM.DBF). pls respond immedietly.

    with thanks

    kkravi.
    Hi!

    The problem is that the data in the datafile is fragmented i.e. the last block of the datafile might be in use even if the total amount of free space is 3,5G.

    Resize of temp is best to performe whit no activity on the system and if possible after a restart of the dB so that the temp tbs is emty.

    This is not always possible so, try to create a new temporary tablespace and redirect all users to that tbs. Then you can resize the original temp tbs and redirect the users back.

    Good Luck!
    /Niklas

Posting Permissions

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