Jump to content
ORA-LO

query Find TABLE fragmentation problem

Recommended Posts

Hi All,

one question:

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

SELECT owner,
       table_name,
       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?

Thx!!

Lorenzo 

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.

Lorenzo

 

 

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

×