Understanding Oracle database schema size is crucial for performance tuning, capacity planning, and effective resource management when managing Oracle databases. As an experienced Oracle DBA, I’ll guide you through various methods to calculate schema sizes using SQL queries in my VirtualBox and provide some context on why these metrics are important.
SQL Queries to Determine Schema Size:
Table of Contents
1. Particular Oracle database Schema Size:
- Schema Size in Megabytes(MB):
SET LINESIZE 80
SET PAGESIZE 55
COLUMN owner FORMAT A20
COLUMN SCHEMA_SIZE_MB FORMAT 9999999.99
SELECT owner, SUM(bytes)/1024/1024 AS SCHEMA_SIZE_MB FROM dba_segments WHERE owner = UPPER('&1') GROUP BY owner;
You can use the below query if you want the size in GB.
- Schema Size in Gigabytes(GB):
SET LINESIZE 80
SET PAGESIZE 55
COLUMN owner FORMAT A20
COLUMN SCHEMA_SIZE_GB FORMAT 9999999.99
SELECT owner, SUM(bytes)/1024/1024/1024 AS SCHEMA_SIZE_GB FROM dba_segments WHERE owner = UPPER('&1') GROUP BY owner;
2. Oracle database schema size for all schemas:
- All the schema sizes in Megabytes(MB):
SET LINESIZE 80
SET PAGESIZE 50
COLUMN owner FORMAT A20
COLUMN SCHEMA_SIZE_MB FORMAT 9999.99
SELECT owner, SUM(bytes/1024/1024) AS SCHEMA_SIZE_MB FROM dba_segments GROUP BY owner ORDER BY SUM(bytes/1024/1024) DESC;
- All the schema sizes in Gigabytes(GB):
SET LINESIZE 80
SET PAGESIZE 50
COLUMN owner FORMAT A20
COLUMN SCHEMA_SIZE_GB FORMAT 9999.99
SELECT owner, SUM(bytes/1024/1024/1024) SCHEMA_SIZE_GB FROM dba_segments GROUP BY owner ORDER BY SUM(bytes/1024/1024/1024) DESC;
3. Check the schema size by each tablespace:
Size of Each Tablespace in Megabytes(MB):
SET LINESIZE 80
SET PAGESIZE 50
COLUMN owner FORMAT A20
COLUMN tablespace_name FORMAT A20
COLUMN TOTAL_SIZE_MB FORMAT 9999999.99
SELECT owner,tablespace_name,Sum(bytes)/1024/1024 AS TOTAL_SIZE_MB FROM dba_segments WHERE owner =upper('&1') group by owner,tablespace_name;
If you want to find the size in GB, then run the below query:
Size of Each Tablespace in Gigabytes(GB):
SET LINESIZE 80
SET PAGESIZE 50
COLUMN owner FORMAT A20
COLUMN tablespace_name FORMAT A20
COLUMN TOTAL_SIZE_GB FORMAT 9999999.99
SELECT owner,tablespace_name,Sum(bytes)/1024/1024/1024 AS TOTAL_SIZE_GB FROM dba_segments WHERE owner =upper('&1') group by owner,tablespace_name;
I hope you found this article helpful. Feel free to reach out if you have any questions. If you want to find the table size in oracle database, you can follow my another article Oracle database table size.