The following columns are returned in the below query:
Owner schema.
Object name and type (TABLE, INDEX, etc.).
Name of the table this object is associated with. For E.g. indexes are associated with their parent tables.
Database space is 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 the sizes of all the indexes on that table.
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 (
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
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')
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'
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('&TABLE_NAME')
)
WHERE total_table_MB > 10
ORDER BY total_table_MB DESC, MB DESC
/
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
