We often need to find out how big is that particular oracle table. Suppose you did a huge data load and want to know the allocated oracle table size.
We can use the below Query to check table size in oracle
For oracle table size in MB
select owner as "Schema" , segment_name as "Object Name" , segment_type as "Object Type" , round(bytes/1024/1024,2) as "Object Size (Mb)" , tablespace_name as "Tablespace" from dba_segments where segment_name=’<table_name>’ and owner='<Table owner>';
For oracle table size in GB
select owner as "Schema" , segment_name as "Object Name" , segment_type as "Object Type" , round(bytes/1024/1024/1024,2) as "Object Size (Gb)" , tablespace_name as "Tablespace" from dba_segments where segment_name=’<table_name>’ and owner='<Table owner>' and owner='<Table owner>';
If you don’t have access to dba_segments, then you can use user_segments also
For the size in MB
select segment_name as "Object Name" , segment_type as "Object Type" , round(bytes/1024/1024,2) as "Object Size (Mb)" , tablespace_name as "Tablespace" from user_segments where segment_name=’<table_name>’
For the size in GB
select segment_name as "Object Name" , segment_type as "Object Type" , round(bytes/1024/1024/1024,2) as "Object Size (Gb)" , tablespace_name as "Tablespace" from user_segments where segment_name=’<table_name>’;
we can also use this query to find the top ten biggest tables in a particular schema or tablespace
Top 10 big tables in Particular schema
select * from (select owner as "Schema" , segment_name as "Object Name" , segment_type as "Object Type" , round(bytes/1024/1024/1024,2) as "Object Size (Gb)" , tablespace_name as "Tablespace" from dba_segments where owner=’<schema>’ and segment_type='TABLE' order by 4 desc) where rownum < 11;
Top 10 big tables in Particular tablespace
select * from (select owner as "Schema" , segment_name as "Object Name" , segment_type as "Object Type" , round(bytes/1024/1024/1024,2) as "Object Size (Gb)" , tablespace_name as "Tablespace" from dba_segments where tablespace_name =’<tablespace name>' and segment_name='TABLE' order by 4 desc ) where rownum <11;
List tables bigger than 10G
select owner as "Schema" , segment_name as "Object Name" , segment_type as "Object Type" , round(bytes/1024/1024/1024,2) as "Object Size (Gb)" , tablespace_name as "Tablespace" from dba_segments where segment_type='TABLE' where bytes/1024/1024/1024 >10 order by 4 desc;
Here is the query if you want to know space associated with the indexes on the table also
with tables as ( select segment_name tname, to_char(bytes/1024/1024,'999,999.99') table_size from user_segments where segment_type = 'TABLE' and segment_name not like 'BIN%' ), indexes as ( select table_name, index_name, scbp, rn, (select to_char(bytes/1024/1024,'999,999.99') from user_segments where segment_name = INDEX_NAME and segment_type = 'INDEX') index_size from ( select table_name, index_name, substr( max(sys_connect_by_path( column_name, ', ' )), 3) scbp, row_number() over (partition by table_name order by index_name) rn from user_ind_columns start with column_position = 1 connect by prior table_name = table_name and prior index_name = index_name and prior column_position+1 = column_position group by table_name, index_name ) ) select decode( nvl(rn,1), 1, tables.tname ) tname, decode( nvl(rn,1), 1, tables.table_size ) table_size, rn "INDEX#", indexes.scbp, indexes.index_name, indexes.index_size from tables, indexes where tables.tname = indexes.table_name(+) and tables.tname = '&1' order by tables.tname, indexes.rn /
Sum of the size of table for a particular schema
select owner,sum(bytes)/1024/1024/1024 as "SIZE in GB" from dba_segments where owner='SCHEMA_NAME' and segment_type='TABLE' group by owner;
The above queries will provide the space allocated in the database. If you are interested in row space consumed by the table, you can use below query
select round((num_rows*avg_row_len/1024),2) used_space_bytes from dba_tables where table_name ='<table name>' and table_owner='<table owner';
This query should be run after the statistics are generated for the table.
If you are interested in the space usage of the table over a period of time or object growth trend, you can use the below query
select * from table(dbms_space.OBJECT_GROWTH_TREND('<table owner>','<table name>','TABLE')); Example SQL> select * from table(dbms_space.OBJECT_GROWTH_TREND('SCOTT','EMP_DATA','TABLE')); TIMEPOINT SPACE_USAGE SPACE_ALLOC QUALITY ---------------------- ---------- ------------ -------- 05-JUN-19 08.59.45.214446 AM 210979117 225829120 INTERPOLATED 06-JUN-19 08.59.45.214446 AM 210979117 225829120 INTERPOLATED 07-JUN-19 08.59.45.214446 AM 210979117 225829120 INTERPOLATED
How to calculate the Size for the table containing LOB segments
select from the BYTES column in DBA_SEGMENTS for the table shows the table segment but does not include LOB (CLOB or BLOB) segments sizes.
To calculate the total size for the table and the associated LOBS segments a sum of the following must occur:
the bytes for the table => from dba_segments
+
the bytes for the LOB segments => from dba_lobs and dba_segments where segment_type is LOBSEGMENT
+
the bytes for the LOB Index (Lob Locator) = from dba_indexes and dba_segments
We can use the below query to calculate the total size
ACCEPT SCHEMA PROMPT 'Table Owner: ' ACCEPT TABNAME PROMPT 'Table Name: ' SELECT (SELECT SUM(S.BYTES) FROM DBA_SEGMENTS S WHERE S.OWNER = UPPER('&SCHEMA') AND (S.SEGMENT_NAME = UPPER('&TABNAME'))) + (SELECT SUM(S.BYTES) FROM DBA_SEGMENTS S, DBA_LOBS L WHERE S.OWNER = UPPER('&SCHEMA') AND (L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER('&TABNAME') AND L.OWNER = UPPER('&SCHEMA'))) + (SELECT SUM(S.BYTES) FROM DBA_SEGMENTS S, DBA_INDEXES I WHERE S.OWNER = UPPER('&SCHEMA') AND (I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER('&TABNAME') AND INDEX_TYPE = 'LOB' AND I.OWNER = UPPER('&SCHEMA'))) "TOTAL TABLE SIZE" FROM DUAL;
Hope you like this article on how to check table size in the Oracle database. We have given various queries and you can use them according to the requirement. Please do provide the feedback
Related Articles
How to check tablespace in Oracle: Learn about How to Check Tablespace in Oracle, tablespace free space, tablespace growth information, tablespace size, associated data files, tablespace size
Alter Table in Oracle: Alter table in oracle is used to modify a column, drop and add constraints, change the data type of the table column, change the table storage parameters
create table in oracle: Tables are the basic unit of data storage in an Oracle Database. we cover how to use Oracle create table command to create a table with foreign key /primary key
alter table add column oracle: Useful insight into How to alter table add column oracle. Details about fast add column feature introduced in oracle 11g
asktom question
High Water Mark in Oracle: High water mark in the oracle database is used to show the blocks which have been ever used in the life cycle of the oracle table
ora-00942: Learn to troubleshoot ORA-00942 table or view does not exist in Oracle database.Easy ways to solve the error in less time
How to check datafile Size in Oracle
Recommended Courses
Here is the nice Udemy Course for Oracle SQL
Oracle-Sql-Step-by-step : This course covers basic sql, joins, Creating Tables and modifying its structure, Create View, Union, Union -all and much other stuff. A great course and must-have course for SQL starter
The Complete Oracle SQL Certification Course : This is a good course for anybody who wants to be Job ready for SQL developer skills. A nice explained course
Oracle SQL Developer: Essentials, Tips and Tricks : Oracle Sql developer tool is being used by many developers. This course gives us tricks and lessons on how to effectively use it and become a productive sql developer
Oracle SQL Performance Tuning Masterclass 2020 : Performance tuning is one of the critical and most sought skills. This is a good course to learn about it and start doing sql performance tuning
You forgot to change labels “Object Size (Mb)” to match calculated section such as GB. So now I have to tell client I messed up when you actually did or perhaps I did having faith in your page! Fix the labels to match the calculation please.
hi
Apologies for the typo mistake
This is corrected now
Thx for the feedback
I need to know the list of top 10 tables and their indexes and lobs put together and also need the list with tablespace wise.
TS1 3 objects and size 300GB
TS2 6 objects and size 200GB
TS3 1 object and size 400GB
hi
You can use below query by Sayan Malakshinov
with
seg as (
select
owner,segment_name
,segment_type
,tablespace_name
,sum(blocks) blocks
,sum(bytes) bytes
from dba_segments s
where segment_type not in (
‘TYPE2 UNDO’
,’ROLLBACK’
,’SYSTEM STATISTICS’
)
and segment_name not like ‘BIN$%’
and owner like ‘&owner_mask’
group by owner,segment_name,segment_type,tablespace_name
)
,segs as (
select
owner,segment_name
,case when segment_name like ‘DR$%$%’ then ‘CTX INDEX’ else segment_type end segment_type
,tablespace_name
,case
when segment_name like ‘DR$%$%’
then (select table_owner||’.’||table_name from dba_indexes i where i.owner=s.owner and i.index_name = substr(segment_name,4,length(segment_name)-5))
when segment_type in (‘TABLE’,’TABLE PARTITION’,’TABLE SUBPARTITION’)
then owner||’.’||segment_name
when segment_type in (‘INDEX’,’INDEX PARTITION’,’INDEX SUBPARTITION’)
then (select i.table_owner||’.’||i.table_name from dba_indexes i where i.owner=s.owner and i.index_name=s.segment_name)
when segment_type in (‘LOBSEGMENT’,’LOB PARTITION’,’LOB SUBPARTITION’)
then (select l.owner||’.’||l.TABLE_NAME from dba_lobs l where l.segment_name = s.segment_name and l.owner = s.owner)
when segment_type = ‘LOBINDEX’
then (select l.owner||’.’||l.TABLE_NAME from dba_lobs l where l.index_name = s.segment_name and l.owner = s.owner)
when segment_type = ‘NESTED TABLE’
then (select nt.owner||’.’||nt.parent_table_name from dba_nested_tables nt where nt.owner=s.owner and nt.table_name=s.segment_name)
when segment_type = ‘CLUSTER’
then (select min(owner||’.’||table_name) from dba_tables t where t.owner=s.owner and t.cluster_name=s.segment_name and rownum=1)
end table_name
,blocks
,bytes
from seg s
)
,so as (
select
segs.owner
,substr(segs.table_name,instr(segs.table_name,’.’)+1) TABLE_NAME
,sum(segs.bytes) total_bytes
,sum(segs.blocks) total_blocks
,sum(case when segs.segment_type in (‘TABLE’,’TABLE PARTITION’,’TABLE SUBPARTITION’,’NESTED TABLE’,’CLUSTER’) then segs.bytes end) tab_size
,sum(case when segs.segment_type in (‘INDEX’,’INDEX PARTITION’,’INDEX SUBPARTITION’,’CTX INDEX’) then segs.bytes end) ind_size
,sum(case when segs.segment_type in (‘CTX INDEX’) then segs.bytes end) ctx_size
,sum(case when segs.segment_type in (‘LOBSEGMENT’,’LOBINDEX’,’LOB PARTITION’,’LOB SUBPARTITION’) then segs.bytes end) lob_size
from segs
group by owner,table_name
)
,tops as (
select
dense_rank()over (order by total_bytes desc) rnk
,so.*
from so
)
select *
from tops
where rnk<=10 — top 50
/
List tables bigger than 10G
select
owner as “Schema”
, segment_name as “Object Name”
, segment_type as “Object Type”
, round(bytes/1024/1024/1024,2) as “Object Size (Gb)”
, tablespace_name as “Tablespace”
from dba_segments
where segment_type=’TABLE’ and owner=’T24′ and bytes/1024/1024/1024 >10 order by 4 desc;
thanks.i Fix the mistake