9 Replies Latest reply: Aug 18, 2009 7:37 AM by arpit sinha RSS

    How to get the size of the table

    Apoorv
      Hi All,

      How to get the size of the table in Oracle 10g?
      Is there any script which needs to be run?

      Regards,
      Apoorv
        • 1. Re: How to get the size of the table
          Aman....
          Well, this has been asked not sure how many times.
          See and do the same for your table also,
          SQL> select bytes/1024/1024 from dba_extents where segment_name='EMP';
          
          BYTES/1024/1024
          ---------------
                    .0625
          HTH
          Aman....
          • 2. Re: How to get the size of the table
            arpit sinha
            Hi Apoorv,

            you can get the size of a table by running the below mentioned query

            select sum(BYTES/1024/1024) as TOTAL_GIG from user_segments where SEGMENT_NAME='&TABLE_NAME';

            and give the name of your table...


            A R P I T S I N H A
            • 3. Re: How to get the size of the table
              Nicolas.Gasparotto
              Aman,
              bytes/1024/1024
              Without using sum(), it is assumed only one extent in table, which is very very rare.

              Arpit,
              sum(BYTES/1024/1024) as TOTAL_GIG
              Assuming GIG means Gb (Gigabits), that is wrong, you calculated in Mb (Megabits).

              Nicolas.
              • 4. Re: How to get the size of the table
                Aman....
                N. Gasparotto wrote:
                Aman,
                bytes/1024/1024
                Without using sum(), it is assumed only one extent in table, which is very very rare.
                Yes Nicolas, you are correct, apologies for missing it.

                regards
                Aman....
                • 5. Re: How to get the size of the table
                  Apoorv
                  Hi All,

                  Sorry but somehow the table user_segments is not populated in my case. But we have another table SYS.ALL_TABLES whose structure is given below. Would I be able to calculate the table size based on the columns given below:

                  ColumnName     Data Type
                       
                  OWNER     VARCHAR2 (30 Byte)
                  TABLE_NAME     VARCHAR2 (30 Byte)
                  TABLESPACE_NAME     VARCHAR2 (30 Byte)
                  CLUSTER_NAME     VARCHAR2 (30 Byte)
                  IOT_NAME     VARCHAR2 (30 Byte)
                  STATUS     VARCHAR2 (8 Byte)
                  PCT_FREE     NUMBER
                  PCT_USED     NUMBER
                  INI_TRANS     NUMBER
                  MAX_TRANS     NUMBER
                  INITIAL_EXTENT     NUMBER
                  NEXT_EXTENT     NUMBER
                  MIN_EXTENTS     NUMBER
                  MAX_EXTENTS     NUMBER
                  PCT_INCREASE     NUMBER
                  FREELISTS     NUMBER
                  FREELIST_GROUPS     NUMBER
                  LOGGING     VARCHAR2 (3 Byte)
                  BACKED_UP     VARCHAR2 (1 Byte)
                  NUM_ROWS     NUMBER
                  BLOCKS     NUMBER
                  EMPTY_BLOCKS     NUMBER
                  AVG_SPACE     NUMBER
                  CHAIN_CNT     NUMBER
                  AVG_ROW_LEN     NUMBER
                  AVG_SPACE_FREELIST_BLOCKS     NUMBER
                  NUM_FREELIST_BLOCKS     NUMBER
                  DEGREE     VARCHAR2 (10 Byte)
                  INSTANCES     VARCHAR2 (10 Byte)
                  CACHE     VARCHAR2 (5 Byte)
                  TABLE_LOCK     VARCHAR2 (8 Byte)
                  SAMPLE_SIZE     NUMBER
                  LAST_ANALYZED     DATE
                  PARTITIONED     VARCHAR2 (3 Byte)
                  IOT_TYPE     VARCHAR2 (12 Byte)
                  TEMPORARY     VARCHAR2 (1 Byte)
                  SECONDARY     VARCHAR2 (1 Byte)
                  NESTED     VARCHAR2 (3 Byte)
                  BUFFER_POOL     VARCHAR2 (7 Byte)
                  ROW_MOVEMENT     VARCHAR2 (8 Byte)
                  GLOBAL_STATS     VARCHAR2 (3 Byte)
                  USER_STATS     VARCHAR2 (3 Byte)
                  DURATION     VARCHAR2 (15 Byte)
                  SKIP_CORRUPT     VARCHAR2 (8 Byte)
                  MONITORING     VARCHAR2 (3 Byte)
                  CLUSTER_OWNER     VARCHAR2 (30 Byte)
                  DEPENDENCIES     VARCHAR2 (8 Byte)
                  COMPRESSION     VARCHAR2 (8 Byte)
                  DROPPED     VARCHAR2 (3 Byte)
                  • 6. Re: How to get the size of the table
                    Nicolas.Gasparotto
                    USER_xxx is showing the tables you are the owner of.
                    ALL_xxx is showing the tables you are allowed to query against
                    DBA_xxx is showing all the tables of the database.
                    Make you choice depending who is the owner of the table you are interested about, which user you are connected with and what grant do you have.
                    Moreover, are you sure it is a table ? What kind of table ?

                    Nicolas.
                    • 7. Re: How to get the size of the table
                      Apoorv
                      Hi,

                      But how to calculate table size based on the available columns in ALL_TABLES. If I select the owner of the table. Is there anyother SQL which helps to calculate the same.
                      • 8. Re: How to get the size of the table
                        Nicolas.Gasparotto
                        If I select the owner of the table.
                        Did my explanation not good enough ? Why do not you want to work with proper views as suggested above (i.e. ALL_SEGMENTS) ?

                        Well, go through the doc for that table description, and choose the good column (+hint : you have to know the blocksize+) :
                        http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_2105.htm#REFRN20286
                        Be aware, you'll need to gather statistics in front.

                        Nicolas.
                        • 9. Re: How to get the size of the table
                          arpit sinha
                          Hi Nicolas
                          N. Gasparotto wrote:
                          Arpit,
                          sum(BYTES/1024/1024) as TOTAL_GIG
                          Assuming GIG means Gb (Gigabits), that is wrong, you calculated in Mb (Megabits).

                          Nicolas.
                          Sorry will remember it further....

                          A R P I T S I N H A