Databaase Reorganisation using full export/import
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Databaase Reorganisation using full export/import

  1. #1
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Hi,

    WE just migrated our database from 8.0.5 to 8.1.7.0 using the migration utilities.

    We also migrated our context catridge in the database to intermedia at the same time.

    Now before we make the database live I want to reoranize the tables using export import because most of them have more than 10 extents. Infact some even have 100's.

    The system tables also have many extents.

    Doing a export/import fixes the problem.
    I know the steps require to do a full import but am not sure how exactly should i go about minimising the extents or fitting the objects to one extent.

    Will just a simple export import do or I need to do something else also to fix this problem.

    What should I do to take some proactive measures so that this does not happen again.

    Thanks

    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Time out.

    First of all, you don't need to re-organize your tables and indexes because you have "lots" of extents.

    Oracle 8i has many new features regarding moving objects. Check out ALTER TABLE MOVE and ALTER INDEX REBUILD from the Documentation.

    move: http://technet.oracle.com/docs/produ...2a.htm#2055687
    rebuild: http://technet.oracle.com/docs/produ...e6.htm#2050230

    [Edited by marist89 on 04-26-2002 at 03:36 PM]
    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
    Nov 2001
    Posts
    335
    I beleive in your previous postings u said you are using RAID5. That means wheather you have 1 extent or many , they will be spread out on a different drives . You will waste you time doing this export and import or in fact using any other thechniques. If you have 1 or 100 extents, WHO CARES! Even 1 extent is not contigious on a disks, besides how ofter (if ever) do you run compress utility on a disk ?

    Regards
    One, who thinks that the other one who thinks that know and does not know, does not know either!

  4. #4
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi Ronnie,

    However, if you want to overcome free space fragmentation then you should do Export/Drop schemas/Coalesce tablesapace and then Import.

    You should not care for no. of extents specially on 8i. Use locally managed tablespaces rather.

    Thanks,

    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  5. #5
    Join Date
    Mar 2002
    Posts
    301
    Hi,

    But bare in mind that, you cannot create a Locally Managed Tablespace for System as well as you cannot create any RBS on it.

    Vijay.
    Say No To Plastics

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by oravijay
    But bare in mind that, you cannot create a Locally Managed Tablespace for System as well as you cannot create any RBS on it.
    You are correct about SYSTEM TS not fitting into LMT, but there is no restrictions about having rollback segments reside in a localy managed tablespace.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Originally posted by BV1963
    I beleive in your previous postings u said you are using RAID5. That means wheather you have 1 extent or many , they will be spread out on a different drives . You will waste you time doing this export and import or in fact using any other thechniques. If you have 1 or 100 extents, WHO CARES! Even 1 extent is not contigious on a disks, besides how ofter (if ever) do you run compress utility on a disk ?

    Regards
    Can you please explain it a little more clearly.

    Also according to you guys I should do a full export, drop schemas , coalesce tablespaces and do the import. Is that all or something else.

    What should i do with my system tablespace being so fragmented.

    I can create a new Db with the same sid on some other machine, recreate the tablespaces and do the full import., that will fix the problem. But how do i make sure that system table fragmantation does not happen again. How do i determine my storage parameters to fix this.

    Thanks


    [Edited by ronnie on 04-28-2002 at 01:00 PM]
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  8. #8
    Join Date
    Aug 2000
    Posts
    236
    Ronnie:

    Like people already told you, it does not matter one whit if you have a single extent or thousands. But since you have migrated to 8.1.7 consider doing this.

    1. Take a full export.
    2. Create LMT's (Locally managed tablespaces) with uniform extent size depending on your data.
    3. Now import your data.

    Regards,
    Nizar

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