DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: perform a reorganization of the data

  1. #1
    Join Date
    Dec 2001
    Posts
    96

    perform a reorganization of the data

    Hi,

    I have to perform perform a reorganization of the data on few tables and indexes. Is export /import of those tables and indexes a good option?

    Awaiting inputs

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334

    Re: perform a reorganization of the data

    Originally posted by aj_usa
    Hi,

    I have to perform perform a reorganization of the data on few tables and indexes. Is export /import of those tables and indexes a good option?

    Awaiting inputs
    might be quicker to do ctas then drop and rename the old table

    why are you re-orging anyway?

  3. #3
    Join Date
    Dec 2001
    Posts
    96
    To verify performance gain. Btw, what is ctas ?

  4. #4
    Join Date
    Dec 2001
    Posts
    96
    oops, i am sorry, i got it, create table as select

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    First - are you sure this is ncessary?

    Look at ALTER TABLE . . MOVE followed by ALTER INDEX . . . REBUILD as an option. The trouble, as I see it, with CTAS drop & rename, is that you have to recreate manually all the indexes & constraints - more chance of getting it wrong.
    Last edited by DaPi; 12-04-2004 at 03:14 PM.

  6. #6
    Join Date
    Dec 2001
    Posts
    96
    To avoid complications, dont you think, export/import grants,constraints,rows,indexes is somewhat better ?
    export table, drop the table, then import it.

    pls advice.

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    =======
    To avoid complications, dont you think, export/import grants,constraints,rows,indexes is somewhat better ?
    export table, drop the table, then import it.

    ========

    There are many things go wrong if you do exp-drop-imp.
    For example the export dump file may be corrupted.
    If indexes are crated by other user (not owner ), they will not be imported/recrated.

    Why do you want to re-org?
    What do you find out with the existing tables/indexes?
    Do you see too many extents for an object?
    Was there any large delete happened on a table?
    What is the oracle release do you use?

    Tamil

  8. #8
    Join Date
    Dec 2001
    Posts
    96
    Hi Tamil,

    yes, We have archived and deleted 50% + of the data in some of these tables and would like to shrink their size to verify any performance gain.

    So, advice ...

  9. #9
    Join Date
    May 2001
    Posts
    736
    There are numerous topics are there in manuals how to reorganize your data.
    Regarding shrinking ur tables delete is not option but use the truncate.Still u didn't satisfy then use the command mentioned Mr.Dapi and create the tables and indexes with new storage parameters.

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Moving the tables and rebuilding indexes is a much more robust option. I would never risk an export/import on this.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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