Re: Getting current space of a table ...

From: Vitaliy Mogilevskiy <vit100gain_at_earthlink.net>
Date: Tue, 23 Mar 1999 15:32:15 -0800
Message-ID: <36F8247F.23BF3E88@earthlink.net>

Or you can use this script that is really easy to run:

---

rem
rem Script: c_unused.sql
rem Created by: Vitaliy Mogilevskiy
rem
rem This script used to get the space usage (High-water mark) rem for TABLE, INDEX or CLUSTER
rem The high-water mark for a table is the difference between the rem TOTAL_BLOCKS value and UNUSED_BYTES value returned by this procedure call. rem The UNUSED_BLOCKS value represents the number of blocks above rem the high-water mark; the TOTAL_BLOCKS value reflects the total rem number of blocks allocated to the table rem After executing this script you can reclaim space from a TABLE rem For example:
rem TOTAL_BLOCKS = 200
rem UNUSED_BLOCKS = 100
rem if the database BLOCK size = 4K, then 100 blocks--400K--could be reclaimed rem if you want to leave 20 blocks within the table as unused space above the rem high-water mark, you can alter the table, specifying that the database rem keep 20 blocks--80K:
rem SQL> alter table <TABLE_NAME> deallocate unused keep 80K; rem
rem

set term on
set serveroutput on
set feedback on
set echo off

declare

var1   number;
var2   number;
var3   number;
var4   number;
var5   number;
var6   number;
var7   number;

begin
dbms_space.unused_space(upper('&owner'),upper('&&object_name'),upper('&object_type'),

    VAR1,VAR2,VAR3,VAR4,VAR5,VAR6,VAR7);

 dbms_output.put_line('OBJECT_NAME               = &&object_name');
 dbms_output.put_line('--------------------------------------');
 dbms_output.put_line('TOTAL_BLOCKS              = '||VAR1);
 dbms_output.put_line('TOTAL_BYTES               = '||VAR2);
 dbms_output.put_line('UNUSED_BLOCKS             = '||VAR3);
 dbms_output.put_line('UNUSED_BYTES              = '||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);

end;
/
undefine object_name
undefine owner
undefine object_type

Hope it helps !

Vitaliy Mogilevskiy. Received on Tue Mar 23 1999 - 17:32:15 CST