database fragmentation
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 35

Thread: database fragmentation

  1. #1
    Join Date
    Jun 2000
    Posts
    315
    We have a database on VAX/VMS which is for exp/imp once a month. Now it's very fragmented. How many ways do I have to defragment the database? The version of oracle is 7.1.6.

    Thanks!

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Why do you think it's fragmented? If you export/import once a month, I doubt it is fragmented.
    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."

  3. #3
    Join Date
    Jun 2000
    Posts
    315
    When we import data now, we ofter get Ora-01652. And I checked the free space for those tablespaces, there are plenty of free space there. Some of the up to 3 to 4 gig. Another reason I thought it's fragmented it because everytime after import data, we delete the data and wait until next import. The delete won't release the space.

    Thanks for the response. Tell me more what you think!


  4. #4
    Join Date
    Nov 2001
    Location
    Planet Earth
    Posts
    116
    i think that is the bad database administration way.

  5. #5
    Join Date
    Jun 2000
    Posts
    315
    Jeff:

    What do you think now?

    Anybody have more concrete, constructive opinions? I appreciate it!

    Thanks!

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by lgaorcl
    The delete won't release the space.
    So? The space will be reused next time you import.
    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."

  7. #7
    Join Date
    Jun 2000
    Posts
    315
    But why do we get ora-01652 so often?

  8. #8
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Hi,

    * First export the whole user-schema with COMPRESS=Y
    * Then build an INDEXFILE with the imp-command
    * This file is a DDL-FILE you can see now the initital/next extent of the tables.

    * Then try to group the tables size in SMALL MEDIUM LARGE XLARGE
    * For every sizecategory you specify a storage
    * You could build for each size-group an own Tablespace.
    * Edit the INDEXFILE and adopt the NEXT and INITIAL ( should be the same ) and the TABLESPACE-CLAUSE and PCTINCREASE should always be 0, depending on the size-category
    * DROP all tables
    * CREATE them thru the INDEXFILE
    * import your objects with IGNORE=Y

    So there should be new Tablespaces which all have Objects with same storage depending on size-category.
    A Reorganization should for the existings object never more be neccessary, even with Oracle 7

    Orca




  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by lgaorcl
    But why do we get ora-01652 so often?
    Because you don't have enough contiguous space. This could be due to your INITIAL or NEXT values being too large. If you're terribly concerned about this error and you don't want to add the needed space, you should drop the tables before you import, coalesce the free space, and import.
    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."

  10. #10
    Join Date
    Aug 2001
    Posts
    184
    you should look into lmt
    OCP DBA 8i
    ocpwannabe@yahoo.com
    -----------------------------
    When in doubt, pick 'C'.

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