ora_table_space_usage.sql

set verify off
set serveroutput on size 1000000

declare
  unformatted_blocks number;
  unformatted_bytes number;
  fs1_blocks number;
  fs1_bytes number;
  fs2_blocks number;
  fs2_bytes number;
  fs3_blocks number;
  fs3_bytes number;
  fs4_blocks number;
  fs4_bytes number;
  full_blocks number;
  full_bytes number;
begin
  dbms_space.space_usage(upper('&1'),upper('&2'),upper('&3'),unformatted_blocks,unformatted_bytes,fs1_blocks,fs1_bytes,fs2_blocks,fs2_bytes,fs3_blocks,fs3_bytes,fs4_blocks,fs4_bytes,full_blocks,full_bytes);
  dbms_output.put_line('Number of blocks that are unformatted: '||unformatted_blocks);
  dbms_output.put_line('Number of blocks having at least 0 to 25% free space: '||fs1_blocks);
  dbms_output.put_line('Number of blocks having at least 25 to 50% free space: '||fs2_blocks);
  dbms_output.put_line('Number of blocks having at least 50 to 75% free space: '||fs3_blocks);
  dbms_output.put_line('Number of blocks having at least 75 to 100% free space: '||fs4_blocks);
  dbms_output.put_line('Number of full blocks: '||full_blocks);
  dbms_output.put_line('Total number of bytes full in the segment: '||full_bytes);
end;
/
undefine 1
undefine 2
undefine 3

Rolar para cima