In my previous articles i have already given multiple SQL queries which are important in real life environment. In this article i would like to give the information about How to get size of all tables in Oracle database?. There are so many times where we need to get the information about the size f tables in oracle. There are most of the times where we require to take information about the multiple tables. The situation of query slowness will occur and we require to check which table is taking how much of memory space.
How to get size of all tables in oracle database? – Example
We require to take information about the tables and its size. Basically the tables which are taking more than 5 MB we are looking for. Those tables are huge tables and which may or might impact the performance of the query. You require to fetch the data at schema level.
Schema name : Amit_Schema
First step is to define the schema.
DEFINE schema_name = ‘Amit_Schema’;
We are returning the following columns,
1.Owner schema : The schema name of owner.
2.Object name and type : The name of the object whether it is Table or index.
3.Name of Parent object like tables or indexes associated with the parent tables.
4.Space : It is nothing but the database space which is occupied with the parent table most probably in MB
5.Tablespace : This is nothing but the information of tablespace in which the object is stored.
6.Extents numbers : This is nothing but the column which which give us information about the number of extents allocated for the object.
7.Initial extent : This is nothing but the column which gives us information about the size of initial extent in bytes.
8.Total size of parent table : This column will give the information about the size.
Query :
SELECT * FROM (
SELECT
owner, object_name, object_type, table_name, ROUND(bytes)/1024/1024 AS Megabytes,
tablespace_name, extents, initial_extent,
ROUND(Sum(bytes/1024/1024) OVER (PARTITION BY table_name)) AS Tables_in_MB
FROM (
— We can start this with Table size
SELECT owner, segment_name AS Name_of_Object, ‘TABLE’ AS Type_of_object,
segment_name AS name_of_table, bytes,
tablespace_name, extents, initial_extent
FROM dba_segments
WHERE segment_type IN (‘TABLE’, ‘TABLE PARTITION’, ‘TABLE SUBPARTITION’) —We are selecting the table,table partition and table
——subpartition
UNION ALL
—– We require to check for 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’)
—Then for LOB
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’
— Get data of 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 > 5
ORDER BY total_table_MB DESC, MB DESC
/
The above query is useful to get the information about the table size. You can fragment the query in to pieces if we require to get information about the specified data objects.