• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Query to check table size in Oracle database

Query to check table size in Oracle database

January 1, 2019 by techgoeasy 4 Comments


We often need to find out how big is that particular oracle table. Suppose you did huge data load and want to know the allocated oracle table size.

We can use 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 dont have access to dba_segments ,then you can use user_segments also

For 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 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 table in particular table or particular schema

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_name='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 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 provided the space allocated in the database.If you 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 is generated for the table.

If you 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 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 Oracle database. We have given various queries and you can use it 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 datafiles ,tablespace size
Alter Table in Oracle : Alter table in oracle is used to modify column , drop and add constraints ,change datatype 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 covers how to use Oracle create table command to create 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 oracle database is used to show the blocks  which has been ever used in the life cycle of the oracle table
ora-00942: table or view does not exist : Learn to trouble shoot ORA-00942 table or view does not exist in Oracle database.Easy ways to solve the error in less time

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 many other stuff. A great course and must have course for SQL starter
The Complete Oracle SQL Certification Course : This is good course for any body 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 developer. This course gives us tricks and lesson on how to effectively use it and become productive sql developer
Oracle SQL Performance Tuning Masterclass 2020 : Performance tuning is one of the critical and most sought skill. This is good course to learn about it and start doing sql performance tuning


Filed Under: Oracle, Oracle Database

Reader Interactions

Comments

  1. Why should I says

    August 25, 2020 at 3:03 pm

    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.

    Reply
    • techgoeasy says

      August 26, 2020 at 5:54 pm

      hi

      Apologies for the typo mistake

      This is corrected now

      Thx for the feedback

      Reply
  2. Satish says

    September 16, 2020 at 9:16 pm

    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

    Reply
    • techgoeasy says

      September 21, 2020 at 7:04 am

      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
      /

      Reply

Leave a Reply Cancel reply

Primary Sidebar

Top Posts & Pages

  • Oracle Indexes and types of indexes in oracle with example
  • Top 30 Most Useful Concurrent Manager Queries
  • Top 46 Oracle ASM Interview Questions
  • Oracle dba interview questions and answers
  • 40 question you must know about R12.2



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • Multi Language Support in 11i/R12
  • EBS PDB service name disappear from listener in 19c
  • how to download oracle e-business suite 12.2 software
  • Step by step upgrade process to R12.2 Upgrade part -2(Main Upgrade Driver for R12.2.0)
  • Step by step upgrade process for R12.2 Upgrade Part -1

Copyright © 2021 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us