• 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 » how to check db size in oracle

how to check db size in oracle

August 7, 2022 by techgoeasy Leave a Comment

We often need to find the database size for space allocation and future growth, Let’s see how to check db size in oracle for Both the Non-CDB and CDB databases

Table of Contents

  • How to check db size in oracle for Non-CDB Database
  • How to check db size in oracle for CDB Database
    • How to find the db size of Root Container
    • How to find the db size of the PDB
    • How to find the size of the whole CDB Database

How to check db size in oracle for Non-CDB Database

Here is the query which can be used to find the database size

select 'Total Allocated Size : ' ,sum(bytes/1024/1024/2014) , ' Gigabytes' from sys.dba_data_files
/

This includes both the SYSTEM, SYSAUX and UNDO tablespace, if we just want the transaction data size, then we can use the below query

select 'Total Allocated Size : ' ,sum(bytes/1024/1024/1024) , ' Gigabytes' from sys.dba_data_files where tablespace_name not in
('UNDOTBS01','UNDOTBS02','SYSTEM','SYSAUX');

Now Database also includes Temp files, Redo logs files and Control files, So DB size including these

select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from
dual;

How to check db size in oracle for CDB Database

Now a CDB Database contains many PDBs. So we need to calculate space for each of these pdbs and Container database

How to find the db size of Root Container

ALTER SESSION SET CONTAINER = CDB$ROOT;
select 'Total Allocated Size : ' ,sum(bytes/1024/1024/1024) , ' Gigabytes' from sys.dba_data_files ;

How to find the db size of the PDB

select total_size/1024/1024/1024 "PDB_SIZE_GB" from v$pdbs where name='&1';

The above also includes the temp file also. You can use the below query also if you are in the PDB

ALTER SESSION SET CONTAINER = &pdbname; 
select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) from dual ;

How to find the size of the whole CDB Database

You can use the below query

select sum(bytes)/1024/1024/1024 from cdb_data_files

If you want to include temp files, redo and Control file also, you can use the below query

select
( select sum(bytes)/1024/1024/1024 from cdb_data_files) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) + ( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from cdb_temp_files ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from
dual;

I hope this post on how to check db size in oracle helps in your daily activities

oracle create tablespace: This article on how to create tablespace in oracle, various characteristics associated with it and different create tablespace statements
ORA-01652: ORA-01652 error usually because when the tablespace does not have free space in Permanent and Temporary tablespace in oracle database. Check out how to resolve it
shrink datafile in Oracle: Check out how to shrink the datafile and reclaim space on the filesystem. How to resolve ORA-03297
Oracle database administration tutorial: This lists all the Oracle DBA-related stuff. Very helpful for administration
how to change default tablespace in oracle: Default tablespace is the tablespace where the objects are created when no tablespace name is specified by users. Find out how to check default tablespace
How to check temp tablespace in Oracle: This article is about temp tablespace in Oracle, resize the tempfile, drop the temp file, find the temp usage by Session
alter tablespace add datafile: Check out this post on How to add a datafile to tablespace in Oracle, add tempfile to the temporary tablespace, how to add datafile in ASM
How to check datafile Size in Oracle
https://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces002.htm

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 generate sql monitor report in Oracle
  • How to find the long running (longops session) in Oracle
  • How to find concurrent request id from Sid or spid
  • how to check recycle bin in oracle: Purge, enable/disable
  • how to check fra size in oracle: Location, usage, change size

Copyright © 2023 : TechGoEasy

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