Showing posts with label optimization. Show all posts
Showing posts with label optimization. Show all posts

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;