• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Database » size of schema in oracle

size of schema in oracle

October 17, 2022 by techgoeasy Leave a Comment

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 schema in oracle

Table of Contents

  • How to find the segment_type for a particular schema
  • Sum of the size of the table for a particular schema
  • Sum of the size of the index in it
  • Sum of the size of the Lobindex in it
  • Sum of the size of the Lobsegments in it
  • Sum of the size of the Table Partition for a specific schema
  • Sum of the size of the Index Partition for a specific schema
  • How to find the size of schema in Oracle

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 count of each of them

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';

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';

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';

Sum of the size of the Lobsegments in it

select sum(bytes)/1024/1024/1024 as "SIZE in GB of lobsegments" from dba_segments where owner='&schema_name' and segment_type='LOBSEGMENT';

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';

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';

How to find the size of schema in Oracle

select sum(bytes)/1024/1024/1024 as “SIZE in GB of schema” from dba_segments where owner=upper(‘&schema_name’);

The above will include the size of all the segment type in Schema

I hope this helps in day-to-day administration. Please do provide the 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 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 fast add column feature introduced in oracle 11g
How to check datafile Size in Oracle
asktom question
High Water Mark in Oracle: High water mark in the oracle database is used to show the blocks  which 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

Filed Under: Oracle, Oracle Database

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • How to enable 10053 trace in Oracle
  • How to find the bind variable of the sql id
  • How to list parameter set at session level in Oracle
  • How to generate tkprof in EBS in 19c
  • Oracle tkprof utility

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us