Downsizing Database
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Downsizing Database

  1. #1
    Join Date
    Jul 2001
    Posts
    181

    Cool

    I have inherited a historical database which is in excess of 150gb it has partitioned tables and indexes, however it is severly oversized and is using about 30GB. What is the best plan of action or options I have in order to downsize this database.

    Thanks in advance



  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Resize datafiles if possible.
    If resizing is not possible then Export/Import.
    Consider using LMT.

    Sanjay

  3. #3
    Join Date
    Jul 2001
    Posts
    181

    Downsizing database

    Resizing datafiles can be dangerous if there is any existing row chaining happening.

    Perhaps export/import but that could provce slow and laborious.

    Any other ideas guys?

  4. #4
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    I don't think resizing datafile will impact row chaining. How did you conclude that it will affect row chaining?
    If there is any row chaining, fix it first.

    Sanjay

  5. #5
    Join Date
    Jul 2001
    Posts
    181
    You are making the assumption that the space being used is at the beginning of the file. I must check DBA_EXTENTS to see where the extents in the file are located. Looking at BLOCK_ID and BLOCKS to see the starting block and the total number of blocks in the extent respectively.

    Resizing can be problematic..

  6. #6
    Join Date
    Jul 2000
    Location
    Amsterdam
    Posts
    234

    shrinking database

    Hi Netbar,

    If you are on 8i you can move tables (or table partitions) to new locally managed tablespaces and rebuild your indexes in new locally managed tablespaces. Drop your old tablespace when all contents are transfered.

    This will reorg your database.

    Tycho

  7. #7
    Join Date
    Jul 2001
    Posts
    181
    Hi Tycho,
    That sounds like a good solution. Is it something you've done yourself

  8. #8
    Join Date
    Jul 2001
    Posts
    181
    Also,

    Are you able to move partitioned tables and indexes

  9. #9
    Join Date
    Jul 2000
    Location
    Amsterdam
    Posts
    234
    Netbar,

    Yes I have moved tables and partitions and rebuild partitioned indexes.

    Point off caution could be that if you move a table (or table partition) the index(es) will be getting the status unusable.

    A strategy can be:
    - move the small tables first using a small initial extent size
    - rebuild the unusable indexes with a smal initial

    - move the medium size tables/partitions
    - rebuild the unusable indexes

    and so on.

    Don't start with rebuilding all your indexes and later move the tables because you have to rebuild them again.

    HTH,

    Tycho

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