This discussion is archived
5 Replies Latest reply: Nov 14, 2012 1:00 AM by Helios-GunesEROL RSS

Table Size

MLIPU Newbie
Currently Being Moderated
How to get the table size?
  • 1. Re: Table Size
    sb92075 Guru
    Currently Being Moderated
    970953 wrote:
    How to get the table size?
    which size; allocated or used?


    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: Table Size
    vlethakula Expert
    Currently Being Moderated
    select sum(bytes)/1024/1024 from dba_segments where segment_name='<tname>'
  • 3. Re: Table Size
    Mark D Powell Guru
    Currently Being Moderated
    user 970953, vlethakula has provided you with a query that will give you the table's allocated size.

    I will add a couple of other considerations since there are many reasons to want to know how much space a table is using such as if you need to duplciate it on another system. The table may have associated indexes which in turn have storage allocations. The table may have LOB columns and each log column will also have a storage segment and depending on why you are looking at the table size you may also have to include the LOB segments in your calculations.

    The query also does not identify how much space within the allocation is actually being used. You can estimate that by looking at information provided in the dba_tables view specifically using the blocks, avg_row_len, pct_free, avg_space, and num_rows columns.

    You can find information on the rdbms dictionary views in the Oracle version# Reference manual.

    A quick summary of the basic view can be found in the following article.

    How do I find information about a database object: table, index, constraint, view, etc… in Oracle ? http://www.jlcomp.demon.co.uk/faq/object_info.html


    HTH -- Mark D Powell --
  • 4. Re: Table Size
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Check MOS notes :
    How to get the actual size used of table in (KB) by using script instead of through EM? [ID 358645.1]
    Script: Computing Table Size [ID 70183.1]
  • 5. Re: Table Size
    Helios-GunesEROL Oracle ACE
    Currently Being Moderated
    Hi;
    How to get the table size?
    1. Please always mention your db and os version
    2. Please make search before making post. Search mechanisim available at top of right site ;)
    https://forums.oracle.com/forums/search.jspa?threadID=&q=table+size&objID=c18&dateRange=all&userID=&numResults=15
    3.Please dont forget to change thread status to answered if it possible when u belive your thread has been answered, it pretend to lose time of other forums user while they are searching open question which is not answered,thanks for understanding*

    Regard
    Helios

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points