Here's a script I wrote to tell me the size allocated to the table -vs- the
freespace in the table...Change the owner
and save off the file ... execute it with @ .....

or to just find the size ...
sql> select sum(bytes)/1048576 from dba_extents where segment_name =
'your_table_name_here';


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

HTH
Gregg
----- Original Message -----
From: "Erica Raymond "
<oracledba-ezmlmshield-x43993552.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Thursday, February 01, 2007 9:37 AM
Subject: How do I find the size of a table


> Hi All,
>
> How do I find the size of a table in the database?? I have an 8k block DB
(i.e. 8192 )
>
>
> Thanks,
>
> ER
>
>
> ---------------------------------------------------------------------
> 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