Tuesday, January 29, 2013

How FULL are the BLOCKS in my TABLE?


Table Block Space Usage:

set serveroutput on size 100000
declare
 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;
 begin
  dbms_space.space_usage (
   '&TABLEOWNER',        --object owner
   '&TABLENAME',         --object name
   'TABLE',              --object type TABLE, INDEX, or "TABLE PARTITION" 
   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
--'&PARTITIONNAME',
);
  dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
  dbms_output.put_line('FS1 Blocks   = '||v_fs1_blocks);
  dbms_output.put_line('FS2 Blocks   = '||v_fs2_blocks);
  dbms_output.put_line('FS3 Blocks   = '||v_fs3_blocks);
  dbms_output.put_line('FS4 Blocks   = '||v_fs4_blocks);
  dbms_output.put_line('Full Blocks  = '||v_full_blocks);
 end;
/

Sample Output:
Unformatted Blocks = 16
FS1 Blocks   = 42  <--- 0-25% full
FS2 Blocks   = 31  <-- 25-50% full
FS3 Blocks   = 35  <-- 50-75% full
FS4 Blocks   = 4651 <- 75-99% full
Full Blocks  = 99448
 
Shrinking options:
-- Enable row movement.
ALTER TABLE scott.emp ENABLE ROW MOVEMENT;
-- Recover space and amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE;
-- Recover space, but don't amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE COMPACT;
-- Recover space for the object and all dependant objects.
ALTER TABLE scott.emp SHRINK SPACE CASCADE;

No comments:

Post a Comment