Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

How to find out (if possible via Enterprise Manager), the total in Mb that a certain table is using?

share|improve this question

3 Answers 3

up vote 5 down vote accepted

Via OEM 10g,

  1. Connect to the database as usual
  2. Click on the Schema tab
  3. On the Tables link within Database Objects
  4. Enter the schema name and object name (optional) and click Go
  5. Use the radio button to select the table you want to look at and click on Edit (Don't click on the table name link)
  6. Click on the Segments tab (and wait...)
  7. You will see the size of the table data and the indexes used.

OK, that technically answered your question. But a better way is:

  1. Logon using SQLPLUS
  2. Run the script from Vincent.

I like to save the script as t.sql as a quick reference

COLUMN size_mb      FORMAT '999,999,990.0'
COLUMN num_rows     FORMAT '999,999,990'
COLUMN fmt_short    FORMAT A24


COLUMN owner        FORMAT A16
COLUMN table_name   LIKE fmt_short
COLUMN tablespace_name  LIKE fmt_short

SET LINESIZE 200
SET AUTOTRACE OFF

COMPUTE SUM OF size_mb ON REPORT
BREAK ON REPORT

SELECT 
    lower( owner )      AS owner
    ,lower(table_name)  AS table_name
    ,tablespace_name
    ,num_rows
    ,blocks*8/1024      AS size_mb
    ,pct_free
    ,compression 
    ,logging
FROM    all_tables 
WHERE   owner           LIKE UPPER('&1')
OR  owner           = USER
ORDER BY 1,2;

CLEAR COMPUTES
CLEAR BREAKS
share|improve this answer

The space used by a table is the space used by all its extents :

SQL> SELECT SUM(bytes), SUM(bytes)/1024/1024 Mo
  2    FROM dba_extents
  3   WHERE owner = :owner
  4     AND segment_name = :table_name;

SUM(BYTES)         MO
---------- ----------
3066429440   2924,375
share|improve this answer
    
Yep, this works. Thanks –  b.roth Apr 15 '10 at 11:09
    
Not sure this will work, because it doesnt take into account indexes does it? –  Codek May 10 '12 at 12:20
    
@Codek: correct, this is only the space used by the table itself. You can calculate the space used by the indexes by the same method however (since indexes are segments and have extents). –  Vincent May 10 '12 at 12:40
    
indeed - it's just trickier, seeing as the "name" of an index can be anything! –  Codek May 10 '12 at 15:22

Are you sure that the original approach using the all_tables view includes LOB extents? I would think not. Here is one that I am finding useful:

with da as (
 SELECT owner, segment_name, SUM(bytes)/1024/1024 size_mb
   FROM dba_extents
   group by rollup(owner, segment_name)
) select owner, segment_name, size_mb, round(size_mb/total_mb*100)
  from da 
    cross join (
      select size_mb as total_mb 
      from da t where owner is null and segment_name is null
    )
order by size_mb desc

It shows me what's using the most space.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.