A schema in Oracle consists of tables, index, Log segments, Lob Index, table partition and index partition. The schema size in Oracle depends on the size of all these objects. Let’s check how to find the size of the schema in Oracle
How to find the size of schema in Oracle
The below query will provide the size of Schema. It includes all the segment types in the schema
select sum(bytes)/1024/1024/1024 as "SIZE in GB of schema" from dba_segments where owner=upper('&schema_name');
How to find the segment_type for a particular schema
select segment_type,count(*) from dba_segments where owner=upper('&schema_name') group by segment_type;
This will show the type of segments existing in the schema and the count of each of them
Query to find the sum of the size of the table for a particular schema
select sum(bytes)/1024/1024/1024 as "SIZE in GB of Tables" from dba_segments where owner='&schema_name' and segment_type='TABLE';
The Sum of the size of the index in it
select sum(bytes)/1024/1024/1024 as "SIZE in GB of Index" from dba_segments where owner='&schema_name' and segment_type='INDEX';
The sum of the size of the Lobindex in it
select sum(bytes)/1024/1024/1024 as "SIZE in GB of lobIndex" from dba_segments where owner='&schema_name' and segment_type='LOBINDEX';
Total size of the Lobsegments in a schema
select sum(bytes)/1024/1024/1024 as "SIZE in GB of lobsegments" from dba_segments where owner='&schema_name' and segment_type='LOBSEGMENT';
The sum of the size of the Table Partition for a specific schema
select sum(bytes)/1024/1024/1024 as "SIZE in GB of table Partition" from dba_segments where owner='&schema_name' and segment_type='TABLE PARTITION';
The sum of the size of the Index Partition for a specific schema
select sum(bytes)/1024/1024/1024 as "SIZE in GB of table Partition" from dba_segments where owner='&schema_name' and segment_type='INDEX PARTITION';
I hope this helps in day-to-day administration. Please do provide feedback to improve
Related Articles
How to check Tablespace in Oracle: Learn about How to Check Tablespace in Oracle, tablespace free space, tablespace growth information, tablespace size, associated data files, tablespace size
Alter Table in Oracle: Alter table in Oracle is used to modify a column, drop and add constraints, change the data type of the table column, and change the table storage parameters
Create table in oracle: Tables are the basic unit of data storage in an Oracle Database. we cover how to use the Oracle create table command to create a table with foreign key /primary key
alter table add column oracle: Useful insight into How to alter table add column oracle. Details about the fast add column feature introduced in Oracle 11g
How to check datafile Size in Oracle
asktom question
High Water Mark in Oracle: The High water mark(HWM) in the Oracle database is used to show the blocks that have been ever used in the life cycle of the Oracle table
ora-00942: Learn to troubleshoot the ORA-00942 table or view does not exist in Oracle database. Easy ways to solve the error in less time