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

Thread: defragmenting datafile

  1. #1
    Join Date
    Jun 2002
    Location
    VA,USA
    Posts
    83

    defragmenting datafile

    Is it possible to defragment a datafile ??

    I've a datafile of 5 GB.. I checked dba_segments and found that it has only 880 MB occupied in that datafile .
    When i tried to resize the datafile to 1 GB , it throws an error "ORA-03297: file contains used data beyond requested RESIZE value"

    Can anybody tell me how can i reduce this datafile size.

    Regards
    Saravana Kumar
    saravana kumar

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,015
    Quote Originally Posted by ksarav
    Is it possible to defragment a datafile ??

    I've a datafile of 5 GB.. I checked dba_segments and found that it has only 880 MB occupied in that datafile .
    When i tried to resize the datafile to 1 GB , it throws an error "ORA-03297: file contains used data beyond requested RESIZE value"

    Can anybody tell me how can i reduce this datafile size.

    Regards
    Saravana Kumar
    All Oracle is telling you is that the high water mark is greater than 1gb. It does not mean that the datafile is fragmented. In fact if you are using Locally Managed Tablespaces, I would bet that you do not have any issue with fragmentation. Try resizing it to 2gb instead.
    this space intentionally left blank

  3. #3
    Join Date
    Jun 2002
    Location
    VA,USA
    Posts
    83
    Thats true. High water mark is more that 4 gb . I cant even reduce the file from 5 gb to 4 gb .

    But in dba_segments i can see that the file has ony 880 mb occupied space.
    Any idea how can i reduce the space to 1 GB .
    saravana kumar

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,015
    Quote Originally Posted by ksarav
    Thats true. High water mark is more that 4 gb . I cant even reduce the file from 5 gb to 4 gb .

    But in dba_segments i can see that the file has ony 880 mb occupied space.
    Any idea how can i reduce the space to 1 GB .
    What objects are in the tablespace? Can you move them to a different tablespace? Once you do that you will not have any problems shrinking the datafile.

    Why is it so important that you shrink this datafile?
    this space intentionally left blank

  5. #5
    Join Date
    Jun 2002
    Location
    VA,USA
    Posts
    83
    The tablespace has 6 files like this where i cant reduce the resize. I've more than 20 GB free space left in that tablespace. If i can know how to resize one datafile..i can resize the other files also ...
    saravana kumar

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    you have objects at the end of the tablespace - you will need to move them if you want to resize any datafiles.

    Wont gain anything though

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Quote Originally Posted by davey23uk
    Wont gain anything though
    Well, hard to say. It depends on what you mean by "gain". If you only have 1GB of data in a tablespace that once occupied 6gb of space, you are effectively backing up 5gb of empty space (using rman).
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

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