7 Replies Latest reply on Jul 23, 2010 9:37 AM by 783522

    How to get a list of most fragmented tables in Oracle?

    783522
      Is there an SQL on how to get a list of most fragmented tables in Oracle DBMS?
        • 1. Re: How to get a list of most fragmented tables in Oracle?
          Anand...
          HI,

          Mention the database version and tell us why do you feel tables are fragmented.

          [http://jonathanlewis.wordpress.com/2010/07/19/fragmentation-3/]


          Anand
          1 person found this helpful
          • 2. Re: How to get a list of most fragmented tables in Oracle?
            783522
            We are using Oracle 10g
            • 3. Re: How to get a list of most fragmented tables in Oracle?
              ZahKar
              Update table's statistics and try this, you should see highly fragmented tables at the top with high wasted_space.
              select table_name,round((blocks*8),2) "size (kb)" , 
                                          round((num_rows*avg_row_len/1024),2) "actual_data (kb)",
                                          (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "wasted_space (kb)"
              from dba_tables
              where (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
              order by 4 desc
              I have modified the query a little bit because the ORDER BY won't work if we concat ||'kb' with the column because that makes the output a character data type columns.
              And have added a WHERE condition to only see those tables where total size is greater then actual data size.

              Edited by: zahid79 on Jul 23, 2010 1:40 PM
              • 4. Re: How to get a list of most fragmented tables in Oracle?
                rajeysh
                refer the link:
                Table Fragmentation
                http://www.orafaq.com/node/1936
                1 person found this helpful
                • 5. Re: How to get a list of most fragmented tables in Oracle?
                  783522
                  Thanks! I would just like to ask you, what do the negative values mean in wasted space?

                  Is there an easy way to improve defragmentation state?


                  __


                  TABLE NAME     SIZE     ACTUAL DATA     WASTER SPACE
                  TREE     0     0     0
                  GC_S     3744     4651.9     -907.9
                  TRAIL     104     113.04     -9.04
                  ASSOCIATION_RULES     272     353     -81
                  ATTRIBUTES     1728     2528.12     -800.12
                  AUDITACTION     128     208.48     -80.48
                  DV     18608     36266.47     -17658.47
                  S134     728     903.08     -175.08
                  A178     344     518.75     -174.75
                  S129     728     896.48     -168.48
                  AGS_NODES     2864     4510.33     -1646.33
                  S149     472     633.79     -161.79
                  S127     728     871.62     -143.62
                  tu     2232     3619.76     -1387.76
                  PCd_DATA     3112     4371.75     -1259.75
                  • 6. Re: How to get a list of most fragmented tables in Oracle?
                    ZahKar
                    Were the table statistics updated when you ran the query?

                    I have slightly changed the query above, try that now.

                    Though there a few ways to defragment tables, but the quickest ones are:
                    1. alter table <<table_name>> move + rebuild all indexes on the table indexes
                    2. create table <<new_tabele>> as select * from <<old_table>>
                        DROP table <<old_table>> purge;
                        rename <<new_table>> to <<old_table>>
                        OR
                        create table <<new_tabele>> as select * from <<old_table>>
                        TRUNCATE table <<old_table>>;
                        INSERT INTO <<old_table>> SELECT * FROM <<new_table>>
                        DROP TABLE <<new_table>> PURGE; 
                    1 person found this helpful
                    • 7. Re: How to get a list of most fragmented tables in Oracle?
                      783522
                      You helped me a lot!

                      Many thanks!
                      D