How to find Table Size in Oracle

September 13, 2019

Below are the important query to check table size of partition and non partitioned tables in Oracle database. You can easily get the table size from dba_segments view. When large volume of data comes into the table, it’s size grows automatically.

QUERY 1: Check table size from user_segments. When you are connected to your own schema/user. 


select segment_name,sum(bytes)/1024/1024/1024 GB from user_segments where segment_type='TABLE' and segment_name=upper('&TABLE_NAME') group by segment_name;

QUERY 2: Check table size from dba_segments if you are connected using sysdba.

 select segment_name,sum(bytes)/1024/1024/1024 GB from dba_segments where segment_type='TABLE' and segment_name=upper('&TABLE_NAME') group by segment_name;

QUERY 3: To check the size of partition table in Oracle.

select PARTITION_NAME,sum(bytes)/1024/1024/1024 GB from dba_segments where SEGMENT_NAME=upper('&TABLE_NAME') and PARTITION_NAME='P01' group by PARTITION_NAME;

QUERY 4: To check table owner:

select owner from dba_segments where segment_name= upper('&TABLE_NAME')  and segment_type='TABLE';

You can also check tablespace name of the table which it belongs to and many more details by combining different columns of dba_segments.

SQL> desc dba_segments;
  Name                                      Null?    Type
 
 OWNER                                              VARCHAR2(30)
  SEGMENT_NAME                                       VARCHAR2(81)
  PARTITION_NAME                                     VARCHAR2(30)
  SEGMENT_TYPE                                       VARCHAR2(18)
  SEGMENT_SUBTYPE                                    VARCHAR2(10)
  TABLESPACE_NAME                                    VARCHAR2(30)
  HEADER_FILE                                        NUMBER
  HEADER_BLOCK                                       NUMBER
  BYTES                                              NUMBER
  BLOCKS                                             NUMBER
  EXTENTS                                            NUMBER
  INITIAL_EXTENT                                     NUMBER
  NEXT_EXTENT                                        NUMBER
  MIN_EXTENTS                                        NUMBER
  MAX_EXTENTS                                        NUMBER
  MAX_SIZE                                           NUMBER
  RETENTION                                          VARCHAR2(7)
  MINRETENTION                                       NUMBER
  PCT_INCREASE                                       NUMBER
  FREELISTS                                          NUMBER
  FREELIST_GROUPS                                    NUMBER
  RELATIVE_FNO                                       NUMBER
  BUFFER_POOL                                        VARCHAR2(7)
  FLASH_CACHE                                        VARCHAR2(7)
  CELL_FLASH_CACHE                                   VARCHAR2(7)

Must Read: