QUERY TO GET SIZE OF ALL TABLES IN AN ORACLE DATABASE SCHEMA

Updated: Feb 9, 2020

As a DBA sometimes you need to know how what’s taking space in an Oracle database, or how large is the table you’re working on, here’s a script which answers these questions. It shows the size of all the database objects large than 10 Mb in a particular database schema.

The following columns are returned:

Owner schema.Object name and type (TABLE, INDEX, etc.).Name of the table this object is associated with. E.g. indexes are associated with their parent tables. Database space occupied by the object in megabytes.Tablespace where an object is stored.The number of extents allocated for the object.Size of the initial extent in bytes.Total database size occupied by the parent table. E.g. for indexes it will be the size of the parent * table plus sizes of all the indexes on that table.

DEFINE schema_name = ‘RAJEEV’ ;

(Ex:- Here Rajeev is my schema name you can use your schema which you want to check size)

Query

SELECT * FROM ( SELECT owner, object_name, object_type, table_name, ROUND(bytes)/1024/1024 AS MB, tablespace_name, extents, initial_extent, ROUND(Sum(bytes/1024/1024) OVER (PARTITION BY table_name)) AS total_table_MB FROM ( — Tables SELECT owner, segment_name AS object_name, ‘TABLE’ AS object_type, segment_name AS table_name, bytes, tablespace_name, extents, initial_extent FROM dba_segments WHERE segment_type IN (‘TABLE’, ‘TABLE PART