Greetings

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE    9.2.0.6.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production


Please see the following suggestion we got from our Vender and Oracle  regarding Table Re-org in LMTS,
Want to verify this with the experts here:


Before we move the table to the keep pool, we saw the table is fragmented. When
we query BIGTABLE_XYZ  info in the dba_tables,
the num_rows * avg_row_len * 4k (block size) is only 1/6 of the blocks taken by the table.
We did the "alter table .. move" statements and found that the table takes much less blocks after
that.

1) is that the correct way to determine if the table is fragmented?

Oracle Reply:

1. Yes, that is a fair way. Other ways are -
- Use the following script :
Note.1019716.6 Script to Report Table Fragmentation
- collect stats using analyze and check avg_space column in dba_tables. Unfortunately this column d
oesnt get populated if we use dbms_stats instead of analyze, so you are forced to use analyze.



Some info about the table:

Free Blocks.............................
Total Blocks............................12288
Total Bytes.............................50331648
Total MBytes............................48
Unused Blocks...........................704
Unused Bytes............................2883584
Last Used Ext FileId....................4
Last Used Ext BlockId...................246800
Last Used Block.........................320


TABLE_NAME             NUM_ROWS     BLOCKS EMPTY_BLOCKS    NFB AVG_ROW_LEN
-------------------- ---------- ---------- ------------ ------ -----------
BIGTABLE_XYZ        1026304      11390            0      0          14

Thie table above was last analyzed a week back.

Right now the number of rows is :  122420

so   122420*14*4K=6855520/6 = 1142533.3  and 1026304*14*4=57473024/6=9578837.33

Can some body throw more light on this ?
--
Regards & Thanks
BN