REM
REM display tables or indexes on very top n bocks
REM

set linesize 80
set pagesize 40

SET ECHO OFF
ttitle -
  center  'Segment Extent Summary'  skip 2

col ownr format a8      heading 'Owner'        justify c
col type format a8      heading 'Type'         justify c trunc
col name format a20     heading 'Segment Name' justify c
col exid format     990 heading 'Extent#'      justify c
col fiid format    9990 heading 'File#'        justify c
col blid format 999,990 heading 'Block#'       justify c
col blks format 999,990 heading 'Blocks'       justify c

select
  owner         ownr,
  segment_name  name,
  segment_type  type,
  extent_id     exid,
  file_id     fiid,
  block_id      blid,
  blocks        blks
from
  dba_extents
where file_id = 1
and (block_id + &top_blocks) >(
        select max(block_id) from dba_extents where file_id=1)
order by block_id
/

REM
REM produce reorganize sql commands of top tables
REM want they are be moved to lower free space
select
  'alter '||segment_type||' '|| owner||'.'||segment_name
  ||decode(segment_type,'TABLE',' move;',
                          'INDEX', 'rebuild;','unknown')
from
  dba_extents
where file_id = 1
and (block_id + &top_blocks) >(
select max(block_id) from dba_extents where file_id=1)
order by block_id



rem
rem display map of datafiles
rem

set serveroutput on size 20000
set linesize 80

declare
  cursor c_map(p_file_id number) is
  (select file_id,block_id,blocks,'X' used
   from dba_extents where file_id=p_file_id
   union
   select file_id,block_id,blocks,'=' used
   from dba_free_space where file_id=p_file_id
   ) order by 1,2,3;
cursor c_file is
select file_name,file_id,blocks/400  bsize from dba_data_files;
j number :=1;
begin
   for r_file in c_file
   loop
       dbms_output.put_line('Map of '||r_file.file_name);
       for r_map in c_map(r_file.file_id)
       loop
           for i  in 1..r_map.blocks/r_file.bsize
           loop
            dbms_output.put(R_MAP.USED);
            if j>=80 then
        j :=1;dbms_output.new_line;
            else
               j := j+1;
            end if;
           end loop;
       end loop;
       dbms_output.new_line;j:=1;
   end loop;
end;
/

rem
rem display detail map of special datafile
rem


set serveroutput on size 999999
set linesize 80
rem ACCEPT p_file_id NUMBER PROMPT 'Pleas input File Number:'


declare
  cursor c_map is
  (select file_id,block_id,blocks,'X' used
   from dba_extents where file_id=&p_file_id
   union
   select file_id,block_id,blocks,'=' used
   from dba_free_space where file_id=&p_file_id
   ) order by 1,2,3;
j number :=1;
bsize number;
begin
  select blocks/(80*80)  into bsize from dba_data_files where file_id=&p_file_id;
  --that is display in 80 lines with linesize of 80  
       for r_map in c_map
       loop
           for i  in 1..r_map.blocks/bsize
           loop
            dbms_output.put(R_MAP.USED);
            if j>=80 then
        j :=1;dbms_output.new_line;
            else
               j := j+1;
            end if;
           end loop;
       end loop;
end;
/