Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, venkatesh.

Asked: June 02, 2016 - 7:31 am UTC

Last updated: August 31, 2022 - 5:56 am UTC

Version: 11G

Viewed 100K+ times! This question is

You Asked

We requested oracle apex work space area 100MB?It exceeds the over 90% size? How to calculate Current file Utilization and current database size?

Requested Size: 100(in MB)

Current File Utilization: 6.49(in MB) What is this?

Current Database Size: 91.06(in MB) and what it this?


Any one assist regarding this issue?



and Chris said...

The size of the database is the space the files physically consume on disk. You can find this with:

select sum(bytes)/1024/1024 size_in_mb from dba_data_files;


But not all this space is necessarily allocated. There could be sections of these files that are not used.

You can find the total space that is used with:

select sum(bytes)/1024/1024 size_in_mb from dba_segments;


You can break this down by user by running:

select owner, sum(bytes)/1024/1024 Size_MB from dba_segments
group  by owner;


I'm not sure precisely how your figures were calculated. But at a guess:

You have 91.06 Mb of disk space allocated. But you've only consumed 6.49 Mb of this. So you still have 84.57 Mb of your allocation free.

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

venkatesh p, June 02, 2016 - 10:44 am UTC

Hi Chris Saxon ,
I am great full for your reply.your query help me a lot resolving my issue.

Thank you Chris.

data size check

Ganesh Kumar, May 02, 2017 - 11:41 am UTC

Team,

how to calculate table_size occupied for a particular data like below.

select * from emp where dept_id='IT';

thanks in advance.


Chris Saxon
May 02, 2017 - 2:41 pm UTC

What are you looking for? The total size of the table, or the size of the data the query returns?

If it's the latter, run the query to find out!

Perfect

Malc Hotson, November 03, 2017 - 11:59 am UTC

Thanks Chris!
Chris Saxon
November 03, 2017 - 12:15 pm UTC

Thanks Malc :)

Regarding how to check database size

sarbeswar parida, October 06, 2019 - 7:28 pm UTC

Above three individual queries actually helps to find out the
size of the database along with the owner also...thanks
Connor McDonald
October 07, 2019 - 12:46 am UTC

glad we could help

RMAN Size predicate

Mitesh, November 27, 2019 - 5:01 am UTC

How to predicate size of the RMAN backup before taking start backup process.
Chris Saxon
November 27, 2019 - 11:00 am UTC

It's complicated. MOS note 1274720.1 gives an upper limit for the size of the first full backup of a database. But as it notes, this depends on


- the device type used (DISK or SBT) determines whether or not UNUSED BLOCK COMPRESSION is used
- the number of dirty blocks found during the input scan (NULL compression)
- backup compression

Also

NOTE: The information in this article is a rough estimate and will change since block compression, undo optimization, and other factors may change between backups.

But you can calculate a general upper limit as:

Size of the data files - free space in them

For example:

SQL> select sum(bytes)/1024/1024 from v$datafile;
SUM(BYTES)/1024/1024
--------------------
990

SQL> select sum(bytes)/1024/1024 from dba_free_space;

SUM(BYTES)/1024/1024
--------------------
344.25


Giving an upper limit of 990-344.25=645.75 Mb

You can also control the size of a backup set, as described in the docs:

https://docs.oracle.com/en/database/oracle/oracle-database/19/bradv/backing-up-database-advanced.html#GUID-14E97C71-7205-44AD-A711-C5B0053EEA58

A reader, March 31, 2022 - 4:25 am UTC

in toad orcale, bytes is invalid identifier

How can i handle it?

Connor McDonald
March 31, 2022 - 10:11 am UTC

Not sure what you mean.

Show us your query

size in cdb_data_files vs v$pdbs

Darin, August 30, 2022 - 1:43 pm UTC

Can you clarify the difference in the numbers via querying from cdb_data_files vs v$pdbs? I am getting ~30% larger from v$pdbs.

select con_id, name, open_mode, total_size/1024/1024/1024 "PDB_SIZE_GB" 
  from v$pdbs
  where name not in ('PDB$SEED') 
  order by NAME;


vs

select con$name, sum(bytes)/1024/1024/1024 
  from cdb_data_files
  group by con$name
  order by con$name;


Connor McDonald
August 31, 2022 - 5:56 am UTC

This might help (from my system)

SQL> select total_size/1024/1024/1024
  2  from   v$pdbs
  3  where  name = 'PDB1';

TOTAL_SIZE/1024/1024/1024
-------------------------
               64.3657837

SQL> select sum(bytes)/1024/1024/1024
  2  from dba_data_files;

SUM(BYTES)/1024/1024/1024
-------------------------
               62.3521118

SQL> select sum(bytes)/1024/1024/1024
  2  from dba_temp_files;

SUM(BYTES)/1024/1024/1024
-------------------------
               2.01367188

SQL> select 62.3521118 + 2.01367188 from dual;

62.3521118+2.01367188
---------------------
           64.3657837

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.