Mega Code Archive

 

Categories / Oracle PLSQL / Table

 

Measure the fragmentation of free space in all of the tablespaces in a database

SQL> select   2        Tablespace_Name,   3        SQRT(MAX(Blocks)/SUM(Blocks))*   4        (100/SQRT(SQRT(COUNT(Blocks)))) Fsfi   5  from DBA_FREE_SPACE   6  group by   7        Tablespace_Name   8  order by 1   9 SQL> spool fsfi.lis SQL> /                                                                TABLESPACE_NAME                    FSFI ------------------------------  ------- DATA_1                             8.30 SYSAUX                            12.07 SYSTEM                            59.31 UNDO                              64.89 USERS                            100.00 SQL> spool off SQL>