1 2 3 Previous Next 73 Replies Latest reply: Mar 25, 2011 11:38 AM by ugurcan RSS

    Reorganize tables

    Jaffy
      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
          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
            FYI.

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

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


                Jafar
                • 5. Re: Reorganize tables
                  Jaffy
                  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
                    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
                      Mohammed Taj
                      Which method using for reorganization of tables?
                      http://www.orafaq.com/node/1936
                      • 8. Re: Reorganize tables
                        Jaffy
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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 Previous Next