Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2004
    Posts
    1

    Question Unanswered: Defrag an oracle table

    Hello everybody !

    I'm a novice in Oracle Administration and I have this question : How can defrag an oracle table ?

    My Oracle version is 8.1.6 and my OS is AIX.

    Thank you.

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    I am not quite sure, cause i am no dba.... i think its Trunc

    Regards

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    By defrag'ing ... I'm guessing that you are talking about a table that was initially created with a small initial extent, and the next extent size was not too large either ... The table has now extended into many segments ... True ???

    Is so, you can use export / import to compress the extents ...

    I don't remember whether or not MOVE was an option in 8.1.6 or not ... Move is a SQL command that allows you to move a table from 1 tablespace to another, but will invalidate your indexes (must be rebuilt).

    Another method is .... create table t_tablename storage (initial ... next ...)
    as select * From original_tablename.
    Drop table original_tablename;
    rename t_tablename to original_tablename;

    Again, you will need to rebuild indexes, triggers, constraints, etc ...

    HTH
    Gregg

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >How can defrag an oracle table?
    Why do you think that any benefit will be obtained from rebuilding this or any other table?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2004
    Posts
    2
    I agree with Greg's recommendation.

    But just a confirmation, MOVE was introduced from Version 9.x. So this is not a option for your 8.1.6 version.

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Just a minor point but alter table move is in 8i. If you need to do a reorg then alter table move followed by alter index rebuild (as the indexes become invalid) is usually the quickest and most convenient way of doing a reorg.

    Alan

  7. #7
    Join Date
    Jun 2004
    Posts
    2
    Thanks Allen for rectifying me.

    I found this doc in Meatlink on using move in 8i.

    http://metalink.oracle.com/metalink/...&p_id=147356.1

    Thanks Again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •