Wasted

NOTE: If you are completely reloading data warehouse tables in an Oracle database then you should truncate the tables first instead of deleting the rows because just deleting the rows doesn't adjust the high water mark and your table can just keep expanding and expanding.

Here are some suggestions for finding out if a table is wasting space:

The following script came from http://www.dbasupport.com/oracle/scripts/ and it will show you the allocated, used and unused space for a particular object.

set echo off
set serveroutput on
set verify off
accept v_user prompt 'Schema: '
accept v_object prompt 'Object: '
accept v_object_type prompt 'Object Type: '
declare
VAR1 number;
VAR2 number;
VAR3 number;
VAR4 number;
VAR5 number;
VAR6 number;
VAR7 number;
VAR8 number;
vtable varchar2(30);

begin
dbms_space.unused_space(upper('&v_user'),upper('&v_object'),upper('&v_object_type'),VAR1,VAR2,VAR3,VAR4,V

AR5,VAR6,VAR7);
VAR2 := (VAR2)/1024/1024;
VAR4 := (VAR4)/1024/1024;
VAR8 := (VAR2-VAR4);
dbms_output.put_line('OBJECT_NAME = ' upper('&v_object'));
dbms_output.put_line('------------------------------');
dbms_output.put_line('TOTAL_BLOCKS = 'VAR1);
dbms_output.put_line('TOTAL_MBYTES = 'VAR2);
dbms_output.put_line('UNUSED_BLOCKS = 'VAR3);
dbms_output.put_line('UNUSED_MBYTES = 'VAR4);
dbms_output.put_line('LAST_USED_EXTENT_FILE_ID = 'VAR5);
dbms_output.put_line('LAST_USED_EXTENT_BLOCK_ID = 'VAR6);
dbms_output.put_line('LAST_USED_BLOCK = 'VAR7);
dbms_output.put_line('TOTAL_USED_MBYTES = 'VAR8);
end;
/
set verify on
set echo on

+++

If you have enough space in the current tablespace, you can clear up the wasted space like this:

alter table table_name move;

This will invalidate the indexes and you will have to rebuild them like this:

alter index index_name rebuild;

I'll meet you further on up the road.

Jim