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
Upcoming Session for Oracle 19c New Features on Saturday 12th October 2019 @7:30 PM IST