Question:

I need a quick way of finding out the number of bytes a tables is using.
The table may have grown over time with the next extent being constantly
changed for bulk loads. (i.e can't rely on using the initial extent, next
extent, and no. of extents thrown to calculate the size)
I've tried using dba_extents which takes a long time where there are lots of
objects. This view is generally slow anyway.
Here is an example of the sql I am currently running.  I will also need to
do a similar thing for indexes.

select   sum(bytes)
   from   dba_extents
 where  owner                   =   'TABLE_OWNER'
     and  segment_name =   'TABLE_NAME'
     and  segment_type    =  ' TABLE'

Is there a better way??

Regards
Jim Katsos

Answer 1:

You can use package dbms_space.unused_space to get this kind of information
for different objects in the database (tables, indices, partitions, etc.).
Here is an example:

set serveroutput on
set verify off
define OWNER = '&1'
define TABNAME = '&2'
declare
var1 number;
var2 number;
var3 number;
var4 number;
var5 number;
var6 number;
var7 number;
begin
dbms_space.unused_space(upper('&OWNER'),upper('&TABNAME'),'TABLE',
     var1,var2,var3,var4,var5,var6,var7);
dbms_output.put_line('Table_Name       = '||'&TABNAME');
dbms_output.put_line('Total Bytes      = '||var2);
dbms_output.put_line('Unused Bytes     = '||var4);
dbms_output.put_line('Used Bytes       = '||to_char(var2-var4));

end;
/

Answer 2:

What about the view DBA_SEGMENTS ?
There is a column BYTEs which is the sum of number of extents.
Best is always to use where clause with owner

f.e. select bytes from dba_segments where owner='SYS' and
segment_name='TAB$'

result =>   74618880   means 74 MB