碎片

来源：互联网发布：淘宝柏润姿祛斑编辑：程序博客网时间：2024/05/01 18:11

SELECT TABLE_NAME,
       (BLOCKS * 8192 / 1024 / 1024) -
       (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB"
FROM USER_TABLES
where (BLOCKS * 8192 / 1024 / 1024) -
       (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024)>10

--高水位移动导致空间
SELECT
'Segment Advice --------------------------'|| chr(10) ||
'TABLESPACE_NAME : ' || tablespace_name || chr(10) ||
'SEGMENT_OWNER : ' || segment_owner || chr(10) ||
'SEGMENT_NAME : ' || segment_name || chr(10) ||
'ALLOCATED_SPACE : ' || allocated_space || chr(10) ||
'RECLAIMABLE_SPACE: ' || round(reclaimable_space/1024/1024,2) || chr(10) ||
'RECOMMENDATIONS : ' || round(reclaimable_space/1024/1024,2) || chr(10) ||
'SOLUTION 1 : ' || c1 || chr(10) ||
'SOLUTION 2 : ' || c2 || chr(10) ||
'SOLUTION 3 : ' || c3 Advice
FROM
TABLE(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'))

select tablespace_name,sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)))) FSFI
           from dba_free_space
           group by tablespace_name
           order by tablespace_name;

30%

0 0