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

Updated: Feb 9

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 PARTITION’, ‘TABLE SUBPARTITION’) UNION ALL — Indexes SELECT i.owner, i.index_name AS object_name, ‘INDEX’ AS object_type, i.table_name, s.bytes, s.tablespace_name, s.extents, s.initial_extent FROM dba_indexes i, dba_segments s WHERE s.segment_name = i.index_name AND s.owner = i.owner AND s.segment_type IN (‘INDEX’, ‘INDEX PARTITION’, ‘INDEX SUBPARTITION’) — LOB Segments UNION ALL SELECT l.owner, l.column_name AS object_name, ‘LOB_COLUMN’ AS object_type, l.table_name, s.bytes, s.tablespace_name, s.extents, s.initial_extent FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.segment_name AND s.owner = l.owner AND s.segment_type = ‘LOBSEGMENT’ — LOB Indexes UNION ALL SELECT l.owner, l.column_name AS object_name, ‘LOB_INDEX’ AS object_type, l.table_name, s.bytes, s.tablespace_name, s.extents, s.initial_extent FROM dba_lobs l, dba_segments s WHERE s.segment_name = l.index_name AND s.owner = l.owner AND s.segment_type = ‘LOBINDEX’ ) WHERE owner in UPPER(‘&schema_name’) ) WHERE total_table_MB > 10 ORDER BY total_table_MB DESC, MB DESC /

Join our Telegram group to get free session information and new opportunity.

If interested to learn free join the group

https://t.me/dbahelp

Upcoming Session for Oracle 19c New Features on Saturday 12th October 2019 @7:30 PM IST

99 views

ADDRESS :

LOCATION :

701, Linden, Rosalie Complex Khadakpada, Kalyan (W) Mumbai, Maharashtra 421301)

CONTACT US :

+919322846017  &   +919867493019

EMAIL: 

QUICK LINKS :

SUBSCRIBE HERE :

Don’t Miss a Lecture. Subscribe Today. 

telegram-512.png

© 2019 DBA Trainings. Proudly Managed By Pearl Organisation

  • Grey LinkedIn Icon
  • Grey YouTube Icon
  • Grey Twitter Icon
  • Grey Facebook Icon