Jump to content

query Find TABLE fragmentation problem

Recommended Posts

Hi All,

one question:

with this query i can check if my table need REORG:

SELECT owner,
       ROUND ( (blocks * 32), 2) || 'kb' "Fragmented size", 
       ROUND ( (num_rows * avg_row_len / 1024), 2) || 'kb' "Actual size",
            ROUND ( (blocks * 32), 2) 
          - ROUND ( (num_rows * avg_row_len / 1024), 2) ||'kb',
           (  (  ROUND ( (blocks * 32), 2) 
               - ROUND ( (num_rows * avg_row_len / 1024), 2))
            / ROUND ( (blocks * 32), 2)) 
         * 100
          "reclaimable space % "
  FROM dba_tables
 WHERE table_name = '&table_Name' AND OWNER LIKE '&schema_name'

But on SOME table the query result is totally WRONG because the field "Actual size" is greater than "Fragmented size"; is thats because the table i check is COMPRESS or anything else?

how can i resolve this problem?



Share this post

Link to post
Share on other sites

Thank you for the answer, but maybe i'm not explaining my problem right.

I've got  tables with a weekly massive delete and update rows which cause fragmentation.

i try to build a report which after this  statement tell me the status of this table and advise me to make a REORG (ALTER TABLE .... MOVE ....).

this table are partitioned and subpartitioned and compressed; how can i check the fragmentation of this table?

i use the query in the first post but in my opinion something is wrong because the original size is less than  "num_rows * avg_row_len" . ; where is my mistake?

Thanks for all.




Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now