How to get size of all tables in an Oracle database schema

in  technology  

If you ever wanted 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 (INDEX, TABLE, 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 this object is in.
  • 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.
-- Find the size of all tables in an Oracle schema
-- Script by Sergey Stadnik, http://ozmoroz.com
-- Licensed under CC BY with attribution required
-- Based on Stackoverflow.com discussion
-- http://stackoverflow.com/questions/264914/how-do-i-calculate-tables-size-in-oracle
DEFINE schema_name = 'replace_with_your_schema_name'
SELECT * FROM (
SELECT
owner, object_name, object_type, table_name, ROUND(bytes)/1024/1024 AS meg,
tablespace_name, extents, initial_extent,
ROUND(Sum(bytes/1024/1024) OVER (PARTITION BY table_name)) AS total_table_meg
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_meg > 10
ORDER BY total_table_meg DESC, meg DESC
/
view raw schema_table_size.sql hosted with ❤ by GitHub

This script is based on the Stackoverflow.com discussion.