CODEWITHSUNDEEP
databaseoracle-database
Kanini
Kanini

Reputation: 1993

Find Size of a Database in Oracle

I have a database named "My_Enterprise_Data". I need to find the size that it occupies on the disk.

How do I find it out?

Is the query, SELECT sum(bytes)/1024/1024 AS "Size in MB" FROM user_segments run against the My_Enterprise_Data correct?

Upvotes: 21

Views: 111451

Answers (4)

santosh tiwary
santosh tiwary

Reputation: 708

Great... dba_segments gives the Oracle database size

To find the actual space occupied by the database.

Select sum(bytes)/1024/1024/1024 from dba_segments;

Upvotes: 0

Ashrumochan Senapati
Ashrumochan Senapati

Reputation: 71

An oracle database consists of data files, redo log files, control files, temporary files. The size of the database actually means the total size of all these files.

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

Upvotes: 7

Thakkar Amit
Thakkar Amit

Reputation: 31

SELECT a.data_size + b.temp_size + c.redo_size + d.controlfile_size 
"total_size in GB" 
FROM (SELECT SUM (bytes) / 1024 / 1024/1024 data_size FROM dba_data_files) a, 
(SELECT NVL (SUM (bytes), 0) / 1024 / 1024/1024 temp_size 
FROM dba_temp_files) b, 
(SELECT SUM (bytes) / 1024 / 1024/1024 redo_size FROM sys.v_$log) c, 
(SELECT SUM (BLOCK_SIZE * FILE_SIZE_BLKS) / 1024 / 1024/1024 
controlfile_size 
FROM v$controlfile) d;

Upvotes: 3

Neal Donnan
Neal Donnan

Reputation: 1733

The following will show you the data files used by oracle:

select TABLESPACE_NAME "Tablspace",  
 FILE_NAME "Filename",  
 BYTES/1024/1024 "Size MB", 
 MAXBYTES/1024/1024 "Maximum Size MB", 
 AUTOEXTENSIBLE "Autoextensible"
from SYS.DBA_DATA_FILES

You can then look for the tablespace used by the My_Enterprise_Data schema

Upvotes: 26

Related Questions