

-- You may need to grant these to your_account. 
-- 
-- connect sys as sysdba 
-- grant select any tables, select any dictionary, analyze any to &your_account; 
-- grant execute on dbms_space to &your_account; 
-- connect &your_account/&your_account_password 
  
CREATE OR REPLACE procedure GEN_SEGMENT_SPACE_STATS 
      as
        index_does_not_exist EXCEPTION; 
        table_does_not_exist EXCEPTION; 
        PRAGMA EXCEPTION_INIT(index_does_not_exist, -1418); 
        PRAGMA EXCEPTION_INIT(table_does_not_exist, -942); 
  
        v_unformatted_blocks number; 
        v_unformatted_bytes  number; 
        v_fs1_blocks         number; 
        v_fs1_bytes          number; 
        v_fs2_blocks         number; 
        v_fs2_bytes          number; 
        v_fs3_blocks         number; 
        v_fs3_bytes          number; 
        v_fs4_blocks         number; 
        v_fs4_bytes          number; 
        v_full_blocks        number; 
        v_full_bytes         number; 
  
        v_total_blocks number; 
        v_total_bytes number; 
        v_unused_blocks number; 
        v_unused_bytes number; 
        v_last_used_extent_file_id number; 
        v_last_used_extent_block_id number; 
        v_last_used_block number; 
  
      begin
        for s in (select owner, segment_name, segment_type, seg.tablespace_name , segment_space_management 
                    from dba_tablespaces ts, dba_segments seg 
                   where ts.tablespace_name not in ('SYSTEM', 'SYSAUX') 
                     and ts.tablespace_name = seg.tablespace_name 
                     and segment_type in ('TABLE', 'INDEX','CLUSTER','LOB') 
                     and (owner, segment_name, segment_type, seg.tablespace_name ) in ( 
                          select owner, segment_name, segment_type, tablespace_name from segment_space_stats)) 
        loop 
          begin
  
            if s.segment_space_management = 'AUTO' then
               dbms_space.space_usage(s.owner, s.segment_name, s.segment_type, v_unformatted_blocks, v_unformatted_bytes, 
                                      v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes , v_fs3_blocks, v_fs3_bytes , 
                                      v_fs4_blocks, v_fs4_bytes , v_full_blocks, v_full_bytes , NULL); 
            end if; 
            dbms_space.unused_space(s.owner, s.segment_name, s.segment_type, v_total_blocks, v_total_bytes, 
                                    v_unused_blocks, v_unused_bytes, v_last_used_extent_file_id, 
                                    v_last_used_extent_block_id, v_last_used_block, NULL); 
            update SEGMENT_SPACE_STATS 
               set tablespace_name=s.tablespace_name, 
                   unformatted_blocks=v_unformatted_blocks, 
                   unformatted_bytes=v_unformatted_bytes, 
                   fs1_blocks =v_fs1_blocks, 
                   fs1_bytes  =v_fs1_bytes, 
                   fs2_blocks =v_fs2_blocks, 
                   fs2_bytes  =v_fs2_bytes, 
                   fs3_blocks =v_fs3_blocks, 
                   fs3_bytes  =v_fs3_bytes, 
                   fs4_blocks =v_fs4_blocks, 
                   fs4_bytes  =v_fs4_bytes, 
                   full_blocks=v_full_blocks, 
                   full_bytes =v_full_bytes, 
                   timestamp  =sysdate, 
                   total_blocks=v_total_blocks, 
                   total_bytes =v_total_bytes, 
                   unused_blocks=v_unused_blocks, 
                   unused_bytes =v_unused_bytes, 
                   last_used_extent_file_id =v_last_used_extent_file_id, 
                   last_used_extent_block_id=v_last_used_extent_block_id, 
                   last_used_block          =v_last_used_block 
             where owner=s.owner 
               and segment_name=s.segment_name 
               and segment_type=s.segment_type; 
          exception 
            when index_does_not_exist then
              null; -- ignore these errors 
            when table_does_not_exist then
              null; -- ignore these errors 
            when others then
              raise; 
          end; 
        end loop; 
        commit; 
  
        for s in (select owner, segment_name, segment_type, seg.tablespace_name, segment_space_management 
                    from dba_tablespaces ts, dba_segments seg 
                   where ts.tablespace_name not in ('SYSTEM', 'SYSAUX') 
                     and ts.tablespace_name = seg.tablespace_name 
                     and segment_type in ('TABLE', 'INDEX','CLUSTER','LOB') 
                   minus 
                  select owner, segment_name, segment_type, tablespace_name, segment_space_management 
                    from segment_space_stats) 
        loop 
          begin
            if s.segment_space_management = 'AUTO' then
               dbms_space.space_usage(s.owner, s.segment_name, s.segment_type, v_unformatted_blocks, v_unformatted_bytes, 
                                      v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes , v_fs3_blocks, v_fs3_bytes , 
                                      v_fs4_blocks, v_fs4_bytes , v_full_blocks, v_full_bytes , NULL); 
            end if; 
            dbms_space.unused_space(s.owner, s.segment_name, s.segment_type, v_total_blocks, v_total_bytes, v_unused_blocks, 
                                    v_unused_bytes, v_last_used_extent_file_id, v_last_used_extent_block_id, v_last_used_block, NULL); 
            insert into SEGMENT_SPACE_STATS 
            values(s.owner, s.segment_name, s.segment_type, s.tablespace_name, s.segment_space_management,v_unformatted_blocks, 
                   v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes, v_fs3_blocks, v_fs3_bytes, 
                   v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes, v_total_blocks, v_total_bytes, 
                   v_unused_blocks, v_unused_bytes, v_last_used_extent_file_id, v_last_used_extent_block_id, 
                   v_last_used_block,sysdate); 
          exception 
            when index_does_not_exist then
               null; -- ignore these errors 
            when table_does_not_exist then
               null; -- ignore these errors 
            when others then
               raise; 
          end; 
        end loop; 
  
        commit; 
  
        delete SEGMENT_SPACE_STATS where (owner, segment_name, segment_type, tablespace_name) not in
        (select owner, segment_name, segment_type, tablespace_name from dba_segments 
          where tablespace_name not in ('SYSTEM', 'SYSAUX') 
            and segment_type in ('TABLE', 'INDEX','CLUSTER','LOB')); 
        commit; 
      end; 
/ 
  
var v_job_id number; 
begin
  -- schedule the job to run daily at 4AM... 
  dbms_job.submit(:v_job_id,'GEN_SEGMENT_SPACE_STATS;',to_date(trunc(sysdate+1)+4/24),'trunc(sysdate+1)+4/24'); 
  commit; 
end; 
/ 
  
exec dbms_job.run(:v_job_id);


REM run query on table datas 

TTITLE center underline "Top Ten Largest Empty Segments"
set linesize 140 pagesize 100 
col owner for a20 
col total_MB for 999999 
clear breaks 
clear computes 
break on report dup 
compute sum of total_MB on report 
select a.* 
  from (select owner, segment_name, segment_type, tablespace_name, total_bytes/1048576 total_MB 
          from segment_space_stats 
         where full_bytes = 0 
        order by total_bytes desc) a 
where rownum<11 
; 

/* 
                                                       Top Ten Largest Empty Segments 
                                                       ______________________________ 
  
OWNER                SEGMENT_NAME                     SEGMENT_TYPE                     TABLESPACE_NAME                  TOTAL_MB 
-------------------- -------------------------------- -------------------------------- -------------------------------- -------- 
PERFSTAT             STATS$SQL_PLAN                   TABLE                            PERFSTAT_DATA                           5 
PERFSTAT             STATS$SQL_PLAN_USAGE             TABLE                            PERFSTAT_DATA                           5 
PERFSTAT             STATS$SEG_STAT                   TABLE                            PERFSTAT_DATA                           3 
BP_PROD_JOBS         JB_SEARCH_RESULT_ALERT           TABLE                            BP_PROD_JOBS_DATA                       2 
PERFSTAT             STATS$BUFFERED_QUEUES            TABLE                            PERFSTAT_DATA                           1 
PERFSTAT             STATS$BUFFERED_QUEUES_PK         INDEX                            PERFSTAT_DATA                           1 
PERFSTAT             STATS$BUFFERED_SUBSCRIBERS       TABLE                            PERFSTAT_DATA                           1 
PERFSTAT             STATS$CR_BLOCK_SERVER_PK         INDEX                            PERFSTAT_DATA                           1 
PERFSTAT             STATS$CR_BLOCK_SERVER            TABLE                            PERFSTAT_DATA                           1 
PERFSTAT             STATS$BUFFERED_SUBSCRIBERS_PK    INDEX                            PERFSTAT_DATA                           1 
                                                                                                                        -------- 
sum                                                                                                                           21 
  
10 rows selected. 

*/
  
TTITLE center underline "Top Ten Largest Space Wasters"
set linesize 140 pagesize 20 
col owner for a20 
col segment_type for a12 
col total_MB for 999999 
col unformatted_MB for 999999 
col unused_MB for 999999 
col pct_unused for 999 heading "Pct Unused"
col estmtd_pssbl_svngs for 99999 heading "Estimated|Possible Savings|w Index Rebuild"
clear breaks 
clear computes 
break on report dup 
compute sum of total_MB on report 
compute sum of unused_MB on report 
  
select a.* 
  from ( 
        select owner, segment_name, segment_type, 
               total_bytes/1048576 total_MB, 
               unformatted_bytes/1048576 unformatted_MB, 
               unused_bytes/1048576 unused_MB, 
               trunc(unused_bytes / total_bytes * 100) pct_unused, 
               case when segment_type = 'INDEX' and total_blocks >= 256 and fs2_blocks >= 64 
                         then unused_blocks + fs2_blocks 
                    else 0 
               end * b.block_size/1048576 estmtd_pssbl_svngs 
          from segment_space_stats a, dba_tablespaces b 
         where unused_bytes <> 0 
           and a.tablespace_name = b.tablespace_name 
         order by unused_bytes desc) a 
where rownum<11 
; 

/*  
                                                        Top Ten Largest Space Wasters 
                                                        _____________________________ 
  
                                                                                                                       Estimated 
                                                                                                                Possible Savings 
OWNER                SEGMENT_NAME                     SEGMENT_TYPE TOTAL_MB UNFORMATTED_MB UNUSED_MB Pct Unused  w Index Rebuild 
-------------------- -------------------------------- ------------ -------- -------------- --------- ---------- ---------------- 
BP_PROD_JOBS         JB_SEARCH_RESULT_IDX             INDEX            2229              4        56          2             2179 
BP_PROD_MVIEWS       USER_INFO_BASIC                  TABLE            4412              0        48          1                0 
BP_PROD_JOBS         JB_SEARCH_RESULT                 TABLE            1216              6        40          3                0 
BP_PROD_JOBS         JB_SEARCH_RESULT_USJ_IDX         INDEX            2109              4        32          1             2041 
BP_PROD_MVIEWS       USER_INFO                        TABLE            6039              0        31          0                0 
BP_PROD_GW_SUPPORT   GWS_ACCESS_LOG_SUM_PK            INDEX            1761              1        16          0              323 
BP_PROD_JOBS         JB_MONSTER_XMIT_LOG              TABLE            3108              0         8          0                0 
BP_PROD_JOBS         DR$JM_SEARCH_DNRM_XML_IDX_2$I    TABLE             144              0         8          5                0 
BP_PROD_JOBS         DR$JM_SEARCH_DNRM_XML_IDX_1$I    TABLE             144              0         8          5                0 
BP_PROD_GW_SUPPORT   GWS_DLY_LOG_20070423_UK          INDEX              88              0         6          6                7 
                                                                   --------                --------- 
sum                                                                   21250                      252 
  
10 rows selected. 

*/

TTITLE center underline "Top Ten Indexes with non-full blocks"
clear breaks 
clear computes 
set linesize 145 pagesize 100 
col owner for a20 
col fs1_blocks for 9999999 heading "< 25%|blocks free"
col fs2_blocks for 9999999 heading "25-50%|blocks free"
col fs3_blocks for 9999999 heading "50-75%|blocks free"
col fs4_blocks for 9999999 heading "75-100%|blocks free"
col full_blocks for 9999999 heading "blocks|full"
col total_blocks for 9999999 heading "total|blocks"
col estmtd_pssbl_svngs for 99999 heading "Estimated|Possible Savings|w Index Rebuild"
col block_size noprint 
break on report dup 
compute sum of estmtd_pssbl_svngs on report 
select a.* 
  from ( 
        select owner, segment_name, 
               fs1_blocks , 
               fs2_blocks , 
               fs3_blocks , 
               fs4_blocks , 
               full_blocks, total_blocks, 
               case when segment_type = 'INDEX' and total_blocks >= 256 and fs2_blocks >= 64 
                         then unused_blocks + fs2_blocks 
                    else 0 
               end * b.block_size/1048576 estmtd_pssbl_svngs, 
               b.block_size 
          from segment_space_stats a, dba_tablespaces b 
         where segment_type = 'INDEX'
           and a.tablespace_name = b.tablespace_name 
         order by fs4_blocks desc, fs3_blocks desc, fs2_blocks desc, fs1_blocks desc) a 
where rownum<11 
; 

/*  
                                                       Top Ten Indexes with non-full blocks 
                                                       ____________________________________ 
  
                                                                                                                               Estimated 
                                                            < 25%      25-50%      50-75%     75-100%   blocks    total Possible Savings 
OWNER                SEGMENT_NAME                     blocks free blocks free blocks free blocks free     full   blocks  w Index Rebuild 
-------------------- -------------------------------- ----------- ----------- ----------- ----------- -------- -------- ---------------- 
BP_PROD_JOBS         JB_SEARCH_RESULT_IDX                       0      271793           0           0     5035   285312             2179 
BP_PROD_JOBS         JB_SEARCH_RESULT_USJ_IDX                   0      257204           0           0     7402   269952             2041 
BP_PROD_JOBS         JB_SEARCH_RESULT_SCORE_IDX                 0      122435           0           0     4443   127488              957 
BP_PROD_GW_SUPPORT   GWS_ACCESS_LOG_SUM_PK                      0       39342           0           0   183215   225408              323 
PERFSTAT             STATS$SQL_SUMMARY_PK                       0        2015           0           0     9354    12288               22 
AVAIL                STAT_TABLE                                 0        1556           0           0    20269    22528               16 
BP_PROD_INVITE       REL_RELATIONSHIP_QUERY_IDX                 0         489           0           0   155335   156544                4 
PERFSTAT             STATS$EVENT_HISTOGRAM_PK                   0         245           0           0     6299     6656                2 
BP_PROD_JOBS         JB_ACTIVITY_LOG_IDX                        0         245           0           0    49501    50816                8 
BP_PROD_MVIEWS       USER_INFO_BASIC_LWR_USRNAM_IDX             0         229           0           0    91468    92160                2 
                                                                                                                        ---------------- 
sum                                                                                                                                 5554 
  
10 rows selected.

*/