how to find the size of table and no of records

[Former Member]

how to find the size of table and no of records

[Former Member · Accepted solution]

Hi Shaik,

Finding size of the tables :

You can find the size of the tables from DB02.

For finding the number of rows in tables: Goto transaction SE16...enter the name of the table and click enter.

Then, in the next screen make the field "MAx. no of hits" blank. Next click on the "NUMBER OF ENTRIES" button at the top.

This will display the number of rows in that table.

Hope this solves your query.

Let me know if you need anything else.

Thanks

Kishore

[fidel_vales · Accepted solution]

Hi,

note to your answer.

Using SE16 to count the number of records is not a good idea as SE16 is client dependent, so you will be counting only the rows on the current client.

[Former Member · Accepted solution]

Here are two alternatives using the oracle table statistics, i am using table USR02 as example:

- directly from SQLPlus

SQL> select table_name, num_rows, blocks/128 "SIZE_MB"
     from dba_tables where table_name = 'USR02';

TABLE_NAME                       NUM_ROWS    SIZE_MB
------------------------------ ---------- ----------
USR02                               14728   4.890625

- or with transaction ST05 -> Enter SQL statement -> "select * from usr02" -> Explain -> click on the table name and you get a popup with the information

Table   USR02                                                                                
Last statistics date                  06.08.2008
Analyze Method                Sample 14,728 Rows
Number of rows                            14,728
Number of blocks allocated                   626

Be aware this information might not be 100% accurate, because for large tables only a sample will be analyzed and the stats are not refreshed every day.

Best regards

Michael

[muralivilla]

Hi All,

I see the solution is already given for this thread, but im just making it easy for the next other people who comes here looking for help.

For no. of entries:

> Go to SE16 -> enter the table name. selection criteria will appear

> press "ctrl+F7" or you can find the button ("No. of entries") above.

For size of Table:

> Go to DB02 -> click the arrow head of "Space"

> click the arrow head of "segment"

> double click detailed analysis. a selection screen will appear

> Enter the table you need in the "Segement" selection box.

> now it will show all the detail and you can find Size over there.

Have a nice day ahead.

Thank you,

Murali

[shouvik]

Hi Arif,

Go to transaction SE38 and execute the program "RSSPACECHECK"  or "RSTABLESIZE"

It is possible to exclude tables of delivery classes 'L' and 'A' from the selection.

For more info. see note 118823 - CC-ADMIN: Size of a client.

Hope this helps. Enjoy.

Best Regards,

Shouvik

[Former Member]

Shaik,

You can also do it through Oracle.

For table size

select bytes from user_segments where segment_name = 'table_name';

For no. of records.

select count(*) from table_name;

[Former Member]

Hi Sudhir,

small correction from my side. Because there are more and more tables with LOBS, you better use:

  SELECT s.segment_name segment_name,
         SUM(s.bytes/1024/1024) tab_size,
         SUM((SELECT SUM(b.bytes/1024/1024)
                FROM dba_segments b
               WHERE b.owner = '<dbs_ora_schema>'
                 AND b.segment_type = 'LOBSEGMENT'
                 AND b.segment_name = l.segment_name)) lob_size
    FROM dba_segments s LEFT JOIN dba_lobs l
      ON s.owner = l.owner
     AND s.segment_name = l.table_name
   WHERE s.owner = '<dbs_ora_schema>'
     AND s.segment_type LIKE 'TABLE%'
     AND s.segment_name = '<table name>'
GROUP BY s.segment_name

Regards

Ralph