NEED YOUR ADVISE ON THIS
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: NEED YOUR ADVISE ON THIS

  1. #1
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    372
    Hello to all the Gurus
    We are in the process of database reorg. We are running on Oracle 8.1.7 on Windows 2000
    advanced server.

    We currently have dictionary managed tablespaces and our USERS_DATA and INDEX tablespaces are heavily loaded.

    USERS_DATA TABLESPACE is 16gb with about 1200 tables(Large and small) with 6 datafiles.
    INDEX TABLESPACE is 21 gb with 8 datafiles.

    We pre created 4 different Locally managed tablespaces just for the USERS_DATA
    USERS_DATA1
    USERS_DATA2
    USERS_DATA3
    USERS_DATA3
    just to move the large and frequently used tables.
    and doing the same for the INDEX tables.
    We know that after Moving those tables we will REBUILD index for those tables moved as they will have different row id.

    Our question is really about MOVING the INDEX
    How do we handle that?

    Do we just rebuild index of the tables moved and just move those particular indexes in the INDEX tablespace?

    What's the best approach?
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  2. #2
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588

    Re: NEED YOUR ADVISE ON THIS

    Hi,

    I would suggest to rebuild only those indexes which get invalid.

    And may be after relocating everything ANALYZE the complete database overnight.

    Sameer

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    When you move a table, its underlying indexes will become invalid. You can rebuild them using:
    ALTER INDX xyz_pk REBUILD TABLESPACE ind_ts1
    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