Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2001

    Unanswered: Shrink tablespace

    I want to shrink the tablespace by using "alter database datafile ...... resize" command, but I got the following error:

    ORA-03297 file contains used data beyond requested RESIZE value

    I check with the view dba_free_space and find that more than 4GB free space by using the command below:

    select sum(bytes) from dba_free_space where tablespace_name=<TS name>

    My question is: althought I get a lots of free space, how come I can shrick that tablespace?

    Thx for your help.

  2. #2
    Join Date
    Nov 2005
    Gex - France
    Hello Matthewlau,

    To shrink a tablespace, Oracle try to release free space at the end of the data file(s).

    During the tablespace life, you may have created some objects and then dropped them lots a time. The result is a tablespace fragmentation with maybe some part of your current data stored near the end of the data file(s).

    In this case, doing nothing you cannot free more than the last free blocs of the data files.

    To free more, you must first reorg (defragment) your tablespace. You can do that using Oracle Enterprise Manager, or you can do it manually by moving (rebuild) all objects in another tablespace and then move them back in their original tablespace.

Posting Permissions

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