This discussion is archived
1 2 3 5 Previous Next 73 Replies Latest reply: Mar 25, 2011 9:38 AM by ugurcan RSS

Reorganize tables

Jaffy Journeyer
Currently Being Moderated
I want to reorganize some tables to release some space, how can i find the tables that need to be reorganized. And i found something that whenever i reorganize the tables it won't release space instead the size of the table increases. Can we find the tables which will gain space by reorgainzzation?


Thanks

Jafar
  • 1. Re: Reorganize tables
    108476 Journeyer
    Currently Being Moderated
    Hi Jafar,
    how can i find the tables that need to be reorganized?
    Start with the OEM segment advisor:

    http://www.dba-oracle.com/t_segment_advisor_10g.htm

    You can also monitor for chained rows and fragmentation, often reorging into a larger blocksize of you have fragmented CLOB, BLOB datatypes:

    http://www.dba-oracle.com/t_identify_chained_rows.htm

    Some shops will reorg tables into sorted hash clusters for faster range scans:

    http://www.dba-oracle.com/t_sorted_hash_clusters.htm

    http://www.dba-oracle.com/t_table_row_resequencing.htm

    I have a video here, showing the process:

    http://www.dba-oracle.com/demo/oracle_hashing.htm

    Hope this helps. . .

    Don Burleson
    Oracle Press author
  • 2. Re: Reorganize tables
    591663 Newbie
    Currently Being Moderated
    FYI.

    Reorganization process requires space double of the original table's size.
  • 3. Re: Reorganize tables
    Jaffy Journeyer
    Currently Being Moderated
    Thanks.

    Jafar
  • 4. Re: Reorganize tables
    Jaffy Journeyer
    Currently Being Moderated
    Yeah i know, but after reorganization the table size is greater than a previous value.


    Jafar
  • 5. Re: Reorganize tables
    Jaffy Journeyer
    Currently Being Moderated
    I have a tables with chained and migrated rows. Is reorganization of a table would eliminate the chaining and migrating problem or is there any other methods to avoid this problem.

    Thanks

    Jafar
  • 6. Re: Reorganize tables
    108476 Journeyer
    Currently Being Moderated
    Hi Jafar,
    is there any other methods to avoid this problem.
    After a reorg, adjust PCTFREE to avoid subsequent chaining, by allowing room in the block for the rows to expand.

    Just be aware that if you have large objects (LOB, CLOB, BLOB), a reorg won't help if the blocksize is too small . . . .
  • 7. Re: Reorganize tables
    MohammedTaj Pro
    Currently Being Moderated
    Which method using for reorganization of tables?
    http://www.orafaq.com/node/1936
  • 8. Re: Reorganize tables
    Jaffy Journeyer
    Currently Being Moderated
    Currently PCTFREE is 10 for all tables, now how much do i need to increase to avoid row chaning and migration. Is there any limit. And my blocksize is 8192 bytes.

    Thanks

    Jafar
  • 9. Re: Reorganize tables
    601585 Oracle ACE
    Currently Being Moderated
    First of all, you need to differentiate row chaining and row migration.

    You have row chaining when your specific "row" is bigger than your block size.
    Because your row is too big, your row is splitted and stored to multiple blocks.
    Row chaining can't be solved by table recreation or move or reorganization whatever.
    The only way is to redesign your physical schema not to make row that big.

    You have row migration when your specific "row" got bigger by some updation
    and there is no room for increased size. In this case Oracle migrate that row to
    another block.
    Row migration can be solved by delete/insert or move or table recreation whatever.

    Refer to
    http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_4005.htm
  • 10. Re: Reorganize tables
    Maran Viswarayar Pro
    Currently Being Moderated
    Hi Jaffar.

    I have done reorganisation but most of us wont recommend the Reorganization of tables

    What is the percentage of rows that are chained / migrated...it should be less than 20 % of your number of rows if it is more than that then you can think about reorganisation

    There are 3 ways to do

    1.Export/Import
    2.Alter table Move
    3.CTAS method

    CTAS has it own draw backs which misses out so may values

    Alter table would be an ideal option but you need space for the table which will be doubled during the move and then you have truncate the old table
    Rebuild Index on that

    WHat is average size of the row in the table where you see Rowchaining/ migration
    or what is the size of the row when you have values fully occupied by the coulumn
    say varchar2(10) mean--insert with 10 characters and similiarily ofr all the cloumns and check the size of the row in KB ..If the size is more than 8KB it going to chain in mutiple blocks because your block size is 8KB


    Export /import is also an ideal method..But it all depends on your environment and other things

    Message was edited by:
    Maran Viswarayar
  • 11. Re: Reorganize tables
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Prompted by this thread, I've just posted a note on my blog about rebuilding tables. It's not always as straight-forward as some people hope.

    http://jonathanlewis.wordpress.com/2007/11/23/table-rebuilds/

    Your problem with tables being bigger after you rebuild them probably relates to the fact that some of your rows have grown to use up the PCTFREE that you left, and you've now copied these larger rows into new blocks, leaving the same PCTFREE - that will never be used because the copied rows aren't going to grow any more.


    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk
  • 12. Re: Reorganize tables
    108476 Journeyer
    Currently Being Moderated
    Hi Jafar,
    Is there any limit.
    No, you can set PCTFREE to only put one row per block, if you wanted!
  • 13. Re: Reorganize tables
    601585 Oracle ACE
    Currently Being Moderated
    Are you really planning to increase PCTFREE to avoid chained row and migrated row?
    No way.
    You'd better understand the concept of row chaining and row migration.
    You have row chaining when your specific "row" is bigger than your block size.
    Because your row is too big, your row is splitted and stored to multiple blocks.
    Row chaining can't be solved by table recreation or move or reorganization whatever.
    The only way is to redesign your physical schema not to make row that big.

    You have row migration when your specific "row" got bigger by some updation
    and there is no room for increased size. In this case Oracle migrate that row to
    another block.
    Row migration can be solved by delete/insert or move or table recreation whatever.
    If you increase PCTFREE, you might hit more chained rows.
    If you decrease PCTFREE, you might hit more migrated rows.
    So, if you have both of chained and migrated rows, adjusting PCTFREE is just a paradox. Row chaining and row migration look like brother, but they have no similarity.
    Each one has its own reason and its own workarounds.
  • 14. Re: Reorganize tables
    108476 Journeyer
    Currently Being Moderated
    This is not helpful to the OP, it's link spam.
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk
    Also, it's deceptive. . . .

    When somebody puts a link in their sig line, it's supposed to reveal their experience. Please correct me if I'm wrong, but I don't see any evidence that you have ever worked as a DBA.
    It's not always as straight-forward as some people hope.
    Yes, it is straight-forward. Oracle has offered table reorg utilities for over a decade, and they are well-proven and reliable. Oracle now officially recommends tables (and indexes!) reorgs within the OEM segment advisor.

    I've done reorgs successfully hundreds of times in mission-critical production systems. Proven value, no issues, very straightforward.

    You have NEVER done a real production table reorg, have you? Not once, not ever, right? Where do you get-off advising a working DBA about something that you have never done?

    I cannot find anything on your sig line spam links that indicates ANY job experience as a DBA. You have never paid a nickel to do ANY DBA work, right? In fact, I can't find anything that speaks to your degrees in software engineering, revelant college education, job experience, nothing.

    I figure that you choose to remain a mystery for a very good reason. . . .

    Either put-up some real-world evidence of job experience, or stop pretending to be an experienced DBA . . . .

    Message was edited by:
    burleson
1 2 3 5 Previous Next