Cut and paste this into a script file ... change the OWNER and execute. It
will ask for the table_name ...
It shows space used by the table as well as the allocated space for the
table (ie, table size and space actually
used data)

HTH
Gregg

set serveroutput on ECHO OFF
/*****************************************************/
/* Find the highwater mark in a table */
/* ALTER TABLE dquon DEALLOCATE UNUSED KEEP 20K; */
/* gb - PSAConsulting */
/* */
/* @C:\DBA\Oracle9\Space_mgmt\Table_Free_Space.sql */
/* */
/*****************************************************/
prompt
prompt
declare
v_user varchar2(50) := 'SYSADM';
v_object_name varchar2(50) := '&TABLE';
v_object_type varchar2(15) := 'TABLE';
v_total_blocks number;
v_total_bytes number;
v_unused_blocks number;
v_unused_bytes number;
v_last_used_extent_file_id number;
v_last_used_extent_block_id number;
v_last_used_block number;
v_partition_name varchar2(50);
v_initial number;
v_next number;
v_used_blocks number;
v_number_extents number;
v_num_rows number;
v_avg_row_len number;
v_tablespace_name varchar2(30);
v_PCT_FREE number;
v_PCT_USED number;
v_INI_TRANS number;
v_FREELISTS number;
v_last_analyzed date;
begin
V_OBJECT_NAME := UPPER(V_OBJECT_NAME);
IF v_object_type = 'TABLE'
THEN
SELECT initial_extent,next_extent,num_rows,avg_row_len, tablespace_name,
pct_free,pct_used,ini_trans,freelists,last_analyzed
INTO v_initial, v_next, v_num_rows, v_avg_row_len, v_tablespace_name,
v_pct_free,v_pct_used,v_ini_trans,v_freelists,v_last_analyzed
FROM all_tables
WHERE owner = v_user
AND table_name = v_object_name;
ELSE
SELECT initial_extent,next_extent, tablespace_name
INTO v_initial, v_next, v_tablespace_name
FROM all_indexes
WHERE owner = v_user
AND index_name = v_object_name;
END IF;
SELECT count(*)
INTO v_number_extents
FROM dba_extents
WHERE owner = v_user
AND segment_name = v_object_name;
DBMS_SPACE.UNUSED_SPACE(

v_user,v_object_name,v_object_type,v_total_blocks,v_total_bytes,v_unused_blo
cks

,v_unused_bytes,v_last_used_extent_file_id,v_last_used_extent_block_id,v_las
t_used_block,v_partition_name);
dbms_output.put_line('. ');

dbms_output.put_line('######################################################
######## ');
dbms_output.put_line('Object Name: '||v_object_name||' -
'||v_object_type||' Tablespace: '||v_tablespace_name);

dbms_output.put_line('######################################################
######## ');
dbms_output.put_line('. ');
dbms_output.put_line('Number of Extents: '||v_number_extents||' Initial:
'||v_initial||' Next: '||v_next||' Pct_Free: '||v_pct_free||' Pct_Used:
'||v_pct_used);
dbms_output.put_line(' INI_Trans: '||v_ini_trans||' Freelists:
'||v_freelists||' Last Analyzed: '||v_last_analyzed);
dbms_output.put_line('......Approx Rows: '||v_num_rows||' Avg Row Len:
'||v_avg_row_len);
dbms_output.put_line('Blocks in segment:
'||v_total_blocks);
dbms_output.put_line('Bytes in segment(Size):
'||v_total_bytes);
dbms_output.put_line('MegaBytes in segment:
'||v_total_bytes/1048576);
dbms_output.put_line('Blocks not used:
'||v_unused_blocks);
dbms_output.put_line('Bytes not used:
'||v_unused_bytes);
dbms_output.put_line('MegaBytes not used:
'||v_unused_bytes/1048576);
dbms_output.put_line('File ID - last extent with data(Block):
'||v_last_used_extent_file_id);
dbms_output.put_line('File ID - last extent with data(Byte):
'||v_last_used_extent_block_id);
dbms_output.put_line('Last block with data:
'||v_last_used_block);
end;
/
SET ECHO ON

----- Original Message -----
From: "Combes Tom VBAHINES "
<oracledba-ezmlmshield-x41743854.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Wednesday, May 30, 2007 3:48 PM
Subject: RE: Determine Disk Space used by tables


>
> Analyze the table(s) with COMPUTE (dbms_stats.gather_table_stats)
> and check the BLOCKS and EMPTY_BLOCKS columns in
> DBA,ALL, or USER_TABLES.
>
> For the indexes,
> use "ANALYZE INDEX ... VALIDATE STRUCTURE;"
> and check USED_SPACE in INDEX_STATS.
>
> -----Original Message-----
> From: Edouard Berard
> [mailto:oracledba-ezmlmshield-x69918426.[Email address protected]
> Sent: Wednesday, May 30, 2007 2:05 PM
> To: LazyDBA Discussion
> Subject: Determine Disk Space used by tables
>
> Hello,
>
> Is it possible to determine the amount of diskspace used at a table
> level?
>
> I thought i would give it a go by determining my block size value 32768
> ( select * from v$parameter where name = 'db_block_size';
>
> then determining the blocks per table...
>
> Here is one of my tables...
> Number of rows:407358629
> Number of blocks:1000561
> blocks * db_block_size
>
> 1000561| 32768|32786382848
>
>
>
>
> Am I even close?
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
> dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>


Oracle LazyDBA home page