In this Oracle tutorial, we will learn how to check database size in oracle 21c. We are working on the latest version of oracle i.e. 21c but the steps shared will work on other versions as well. Also, we will cover these topics.
- Database size in oracle 21c
- DB size in oracle 11g
- Full database size in oracle 21c
- Database size in oracle in GB
- Database size in oracle 12c
- Find database size in oracle SQL developer
- Logical database size in oracle 21c
- Increase database size in oracle 21c
- Increase database column size 21c
- Database table size in oracle 21c
Database size in oracle 21c
Oracle database is the combination of multiple objects, files, tablespaces, users, etc. Each one of them plays an important role in storing and organizing the data in such a way then it can be presented in minimum time as and when required.
The database values are recorded in bytes (smallest unit of memory after nibble) and these bytes can be converted into kilobytes (kb), Megabytes (MB), Gigabytes (GB), and so on.
Below table shows the memory unit chart as per it 1024 bytes = 1kb, 1024 kb = 1MB, and so on.
| 1 KB | 1024 Bytes |
| 1 MB | 1024 KB |
| 1 GB | 1024 MB |
| 1 TB | 1024 GB |
| 1 PB | 1024 TB |
Dividing the value by 1024 once will give database size in kb, twice will give value in megabytes (MB), and so on.
All of the databases are divided into three parts:
- Data files: It holds the information of all the files, object, tablespaces, users etc.
- Temp Files: It keeps a record of all the temporary files in oracle database.
- Log files: Each activity on oracle database is recorded in the form of logs and these logs are stored in the log files.
In the upcoming sections, we will learn how to write queries using these objects to fetch the size of the database in oracle 21c.
Also, check: Oracle check database status
DB size in oracle 11g
Oracle database 11g was once one of the popular databases but now we have new versions like 12c, 19c, and 21c (latest). Most of the companies have upgraded from 11g to other versions.
We assume that since you are reading this section that means you are looking for a way to calculate the database size so we are going to share the steps for calculating database size in oracle 11g.
Using the below script you can calculate the size of the oracle database 11g:
-- view total file and bytes
SELECT *
FROM DBA_DATA_FILES;
-- add all the bytes and display size of database
SELECT SUM(BYTES) AS "Size in Byte"
FROM DBA_DATA_FILES;
-- display size of database in KB
SELECT SUM(BYTES)/1024 AS "Size in KB"
FROM DBA_DATA_FILES;
-- display size of database in MB
SELECT SUM(BYTES)/1024/1024 AS "Size in MB"
FROM DBA_DATA_FILES;
In the above script, dba_data_files holds the information in the bytes and using the sum aggregate function we have added all the bytes together. Later we have divided the value by 1024 to get the values in kb and MB.
Read: Connect to oracle database
Full Database Size in Oracle 21c
In oracle, full database size can be calculated by checking the size of the database folder available inside the oradata folder.
All the databases are present inside the oradata folder. In our case, the default orcl folder is present which holds the information of Data files, temp files, and log files.
The below image shows the total size and path of the orcl database in oracle 21c:
In our oracle query, we have to combine the results of data, temp, and log files to get the full database size in oracle.
There are multiple ways to combine the result of oracle queries but the most efficient and preferred way is using the union all clause in oracle.
Example Script:
In the below script, we have calculated the full database size in oracle by combining temp files, data files, and log files. Union All clause allows us to combine the results of multiple queries.
SELECT SUM(BYTES)/1024/1024/1024 AS "Total Size"
FROM (SELECT BYTES FROM V$LOG
UNION ALL
SELECT BYTES FROM V$DATAFILE
UNION ALL
SELECT BYTES FROM V$TEMPFILE);Output:
In the below output, the total size is 4 GB and .53 megabytes. This information is slightly different from the above image because the database keeps growing.
Read: Oracle get database name
Database Size in Oracle in GB
Database size is calculated in bytes in the oracle database but you convert the value of bytes in gigabytes (GB) by dividing the bytes 3 times by 1024.
Divide the oracle database size value in bytes by 1024 x 1024 x1024 to get the oracle database size in Gigabytes (GB).
In our previous section – Full Database Size in Oracle we have demonstrated an example where the size of the database is calculated in GB.
Read: Oracle Database vs MySQL Database
Database size in oracle 12c
Before the release of Oracle 19c, oracle version 12c was one of the popular oracle database version that was widely used by individuals and organizations due to its clustering feature.
Most of the organizations have upgraded to 19c but there are companies who still find oracle version 12c compatible with their requirements.
Even Docker hub provides 12c has the latest version for docker containers. They have not yet launched the docker file for oracle 19c and above.
The commands shared so far in this blog will work for oracle 12c as well. Use the below script to see the size of the database in different categories.
-- data files
SELECT SUM(BYTES)/1024/1024 AS "DATA FILE SIZE IN MB"
FROM V$DATAFILE;
-- temp files
SELECT SUM(BYTES)/1024/1024 AS "TEMPERORY SIZE IN MB"
FROM V$TEMPFILE;
-- log files
SELECT SUM(BYTES)/1024/1024 AS "LOGS IN MB"
FROM V$LOG;In the below output, the yellow text is the script and the red marked area is the output. The data file is consuming 3575 MB, the temporary (sorry for the spelling mistake) file is consuming 484 MB and the Log file is using 600 MB. Adding all of these will give the total database size in oracle.
So, in this way, we can find out the database size in oracle 12c.
Read our blog – How to Install Oracle on Docker.
Find database size in oracle SQL developer
SQL Developer is a free tool provided by oracle to run huge SQL queries. It provides both graphical and a command-line interface to perform SQL operations.
SQL developer tool allows you to connect with multiple accounts and PDBs all at once so make sure you are checking the size of the right database in oracle.
In the below image we have executed the program in the above section – Full Database Size in Oracle on the SQL developer tool.
Logical database size in oracle 21c
Oracle Database 21c is the combination of physical and logical storage. Physical storage includes data files and os block whereas logical storage includes tablespace, segment, extent, and oracle data block.
All the space occupied by tables and indexes in the oracle database comes under the logical database. And it is stored inside the dba_segments tables.
Use the below script to view all the information stored inside the dba_segments table.
SELECT *
FROM DBA_SEGMENTS;There is a column with the name Bytes which holds the information of the size of each row in the dba_segments table. Adding up all the bytes row will give the size of the logical database in oracle 21c.
SELECT SUM(BYTES)/1024/1024 AS "SIZE IN MB"
FROM DBA_SEGMENTS;In the below output of the above script, the size of the Logical database is 2010.5 MB. You can convert this value into GB by dividing it by 1024.
Read: Connect to Oracle Database using Python
Increase database size in oracle 21c
In oracle database Express Edition (XE) user data cannot exceed 12GB. Since this iversion of oracle is free so it has some limitations. Exceeding this limit will throw an error: ORA-12592.
Here is an official document and Licensing information from oracle which states the limitations of the oracle database 21c.
You can upgrade to Oracle Database 21c Standard or Enterprise Edition in order to increase the size of the oracle database officially.
Oracle database 21c standard edition provides a maximum database size of 2 Petabytes (PB) whereas Enterprise edition provides 4294 Petabytes (PB).
The workaround for this problem is the database size can be increased by adding more datafiles or tablespace to an existing database. We tried and it worked for us but we are not sure. We followed the steps shared for 10g on the official documentation.
Increase database column size 21c
There are mainly 2 problems that are related to Increasing database column size keywords in oracle database 21c.
First is you have created a table now you want to increase the table data type. for example, earlier you have set the value of the first name column as varchar(10) but now you have realized that users have a long first name.
This problem can be solved using Alter Table statement in oracle. In the below syntax fill the information for the table name, col_name, and data_type.
ALTER TABLE <table_name>
MODIFY <col_name> <data_type>;Here is an example, to demonstrate increased database column size in oracle 21c.
-- create new table
CREATE TABLE EMPLOYEE(
EMP_ID INTEGER,
EMP_NAME VARCHAR2(10)
);
-- view table structure
DESC EMPLOYEE;
-- increase database column size
ALTER TABLE EMPLOYEE
MODIFY EMP_NAME VARCHAR2(100);
-- view increased size
DESC EMPLOYEE;In the below output of the above script, the varchar(10) value has been modified to varchar(100) which means the user will be able to fill more characters.
The second problem is related to SQL PLUS if you want to show the output in a single line or an organized manner then you can control it by increasing the column size in the oracle database.
In the below syntax, replace col_name with the name of the column whose size you want to increase.
column <col_name> format a10;Read: How to create a database in Oracle 19c
Database table size in oracle 21c
A database table is present inside the dba_segments and user_segments. You can use either of them to fetch the size of a database table in oracle 21c.
Tables are displayed under the column Segment Name in oracle database21c. Use the below script to display all the table names with their size in Megabytes (MB).
SELECT SEGMENT_NAME, BYTES/1024/1024 AS "MB"
FROM USER_SEGMENTS;If you wish to view the size of a particular table use where clause in oracle database 21c. In the example, we have created a table and then checked the size of that table.
-- create table
CREATE TABLE EMPLOYEE(
EMP_ID INTEGER,
EMP_NAME VARCHAR2(10)
);
-- view the size of table
SELECT SEGMENT_NAME, BYTES/1024/1024 AS "MB"
FROM DBA_SEGMENTS
WHERE segment_name='EMPLOYEE';The below image is the output of the above code, yellow text the script, and the red marked area is the result of the script.
You may also like to read the following Oracle tutorials.
- Oracle Change Database Name
- How to Check Oracle Database Version
- How to Get List all Tables in Oracle
- Connect Excel to Oracle Database
In this tutorial, we have learned how to check database size in oracle. Also, we have covered these topics.
- Database size in oracle
- DB size in oracle 11g
- Full database size in oracle
- Database size in oracle in gb
- Database size in oracle 12c
- Find database size in oracle SQL developer
- Logical database size in oracle
- Increase database size in oracle
- Increase database column size
- Database table size in oracle
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.