Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2001
    Posts
    7

    Unanswered: Can't downsize the datafile?

    Guys

    I've got an oracle 8.0.5 and an instance on it.
    The default tablespace for my application is users,
    which is composed with two datafiles, say file#1 and file#2.
    File#1 is 3G bytes and file#2 is 512M.

    I used DBA studio (8.1.7) to monitor usage of users tablespace
    and found that the usage of file#1 was almost 100%
    while that of file#2 was no more than 10%.

    So, I dropped couples of schemes from users tablespace.
    The usage of file#1 then was down to less than 30%.
    After that, I tried to resize file#1 down to 2G and I failed to do it.
    The "OEM" said that, “ORA-3297: file contains x blocks of data beyond requested RESIZE value”

    Can anybody tell me how I should do?

    Thanks a lot.


    Newpaul



  2. #2
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200
    This is a common problem. As the message indicates, there are tables taking up space beyond the new limit you specified in your 'rezize' statement.

    The only way to shrink the datafile is to first move the tables out of the tablespace. Upon re-import or re-creation, they will use the first available free data blocks in the tablespace allowing the datafile to be resized.

  3. #3
    Join Date
    Nov 2001
    Posts
    7
    Dear sir

    Thanks for reply.

    I used to resize some other tablespace, which had low usages.
    It worked. BTW, there were tables on it.
    So I thought it might work too for other tablespaces by reducing their usages to a certain low level and then I could resize it.
    Unfortunately, it's not like what I've expected.

    I feel confused with your kind suggestions and myself experiences.
    There must still have things I don't figure out.
    Can you explain that for me?


    Regards,

    Newpaul





  4. #4
    Join Date
    Jul 2001
    Posts
    92
    hi,
    I believe the datafile has "HIGHWATERMARK" like a table.
    Though there is freespace in the datafile , you cannot draw back your freespace, just like the freespace in the tables.
    zhu chao
    www.cnoug.org

  5. #5
    Join Date
    Oct 2002
    Posts
    2

    Re: Can't downsize the datafile?

    Originally posted by newpaul
    Guys

    I've got an oracle 8.0.5 and an instance on it.
    The default tablespace for my application is users,
    which is composed with two datafiles, say file#1 and file#2.
    File#1 is 3G bytes and file#2 is 512M.

    I used DBA studio (8.1.7) to monitor usage of users tablespace
    and found that the usage of file#1 was almost 100%
    while that of file#2 was no more than 10%.

    So, I dropped couples of schemes from users tablespace.
    The usage of file#1 then was down to less than 30%.
    After that, I tried to resize file#1 down to 2G and I failed to do it.
    The "OEM" said that, “ORA-3297: file contains x blocks of data beyond requested RESIZE value”

    Can anybody tell me how I should do?

    Thanks a lot.


    Newpaul



  6. #6
    Join Date
    Oct 2002
    Posts
    2
    newpaul,

    Even though you had dropped a couple of schemas it proved that there are objects existing beyond the resize limit. One quick fix is to balance the load by creating a couple of users tbs and distribute the objects based on the access pattern and this should take care of your problem.

    Balaji K

  7. #7
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    You have to find out the size to which any datafile in the tablespace can shrink. Use This Script to find out this information.

    Once you know the size then do that w/o getting the "ORA-3297: file contains x blocks of data beyond requested RESIZE value" error messages.

    Hope that helps,

    Hope that helps,

    clio_usa
    OCP - DBA

    .

Posting Permissions

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