Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, venkatesh.

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

Last updated: August 20, 2024 - 9:41 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

  (13 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

database size and utilization

Alan, April 29, 2024 - 3:20 pm UTC

my take on this

select
 df.tablespace_name
 ,df_bytes
 ,used_bytes
 from
 (select case when tablespace_name in ('UNDO','SYSTEM','SYSAUX','PERFSTAT') or tablespace_name like 'UNDO%'
   then tablespace_name else 'Data' end tablespace_name
  ,trunc(sum(bytes)/1024/1024/1024,2) df_bytes
  from dba_data_files
  group by case when tablespace_name in ('UNDO','SYSTEM','SYSAUX','PERFSTAT') or tablespace_name like 'UNDO%'
    then tablespace_name else 'Data' end) df
 ,(select case when tablespace_name in ('UNDO','SYSTEM','SYSAUX','PERFSTAT') or tablespace_name like 'UNDO%'
   then tablespace_name else 'Data' end tablespace_name
  ,trunc(sum(bytes)/1024/1024/1024,1)  usd_bytes
  from dba_segments
  group by case when tablespace_name in ('UNDO','SYSTEM','SYSAUX','PERFSTAT') or tablespace_name like 'UNDO%'
    then tablespace_name else 'Data' end ) ud
  where ud.tablespace_name(+) = df.tablespace_name
union
 select  'TEMP'
  , trunc(sum(TABLESPACE_SIZE)/1024/1024/1024,2) TEMP_BYTES
  , trunc(sum(TABLESPACE_SIZE)/1024/1024/1024 - sum(free_SPACE)/1024/1024/1024,2) TMP_BYTES
  from dba_temp_free_space T

Connor McDonald
April 30, 2024 - 4:47 am UTC

So close :-)

SQL> select
  2   df.tablespace_name
  3   ,df_bytes
  4   ,used_bytes
  5   from
  6   (select case when tablespace_name in ('UNDO','SYSTEM','SYSAUX','PERFSTAT') or tablespace_name like 'UNDO%'
  7     then tablespace_name else 'Data' end tablespace_name
  8    ,trunc(sum(bytes)/1024/1024/1024,2) df_bytes
  9    from dba_data_files
 10    group by case when tablespace_name in ('UNDO','SYSTEM','SYSAUX','PERFSTAT') or tablespace_name like 'UNDO%'
 11      then tablespace_name else 'Data' end) df
 12   ,(select case when tablespace_name in ('UNDO','SYSTEM','SYSAUX','PERFSTAT') or tablespace_name like 'UNDO%'
 13     then tablespace_name else 'Data' end tablespace_name
 14    ,trunc(sum(bytes)/1024/1024/1024,1)  usd_bytes
 15    from dba_segments
 16    group by case when tablespace_name in ('UNDO','SYSTEM','SYSAUX','PERFSTAT') or tablespace_name like 'UNDO%'
 17      then tablespace_name else 'Data' end ) ud
 18    where ud.tablespace_name(+) = df.tablespace_name
 19  union
 20   select  'TEMP'
 21    , trunc(sum(TABLESPACE_SIZE)/1024/1024/1024,2) TEMP_BYTES
 22    , trunc(sum(TABLESPACE_SIZE)/1024/1024/1024 - sum(free_SPACE)/1024/1024/1024,2) TMP_BYTES
 23    from dba_temp_free_space T
 24  /
 ,used_bytes
  *
ERROR at line 4:
ORA-00904: "USED_BYTES": invalid identifier


oops

Alan, April 30, 2024 - 8:10 am UTC

try this
line 14
,trunc(sum(bytes)/1024/1024/1024,1) used_bytes


Chris Saxon
April 30, 2024 - 2:53 pm UTC

That's it :)

Shouldn't that be from USER_BYTES

John, May 02, 2024 - 4:30 pm UTC

Shouldn't the USED_BYTES be USER_BYTES? There is a column for DBA_DATA_FILES that is USER_BYTES, which, I believe, is how many bytes are being used.
Chris Saxon
May 03, 2024 - 12:16 pm UTC

There was a typo in the column aliases in the subquery

How about....

John, May 02, 2024 - 4:45 pm UTC

with tbs as (select (select sum(bytes)/1024/1024 from dba_data_files where TABLESPACE_NAME='SYSAUX') mbytes,
(select sum(bytes)/1024/1024 from dba_free_space where tablespace_name='SYSAUX') free_mbytes from dual)
select mbytes,free_mbytes,(mbytes-free_mbytes) used_mbytes from tbs
/

Chris Saxon
May 03, 2024 - 12:19 pm UTC

Why the subqueries instead of joining the views?

How is space affected with RMAN DUPLICATE

Leslie, May 03, 2024 - 6:46 pm UTC

When duplicating will you need the same amount of space that is allocating in the source db server on the target db server? Or will you need the used space?
Connor McDonald
May 07, 2024 - 3:38 am UTC

It will need the same total space because we are copying entire datafiles

Checking the math

Angela Gard, August 19, 2024 - 10:09 pm UTC

am I mistaken in thinking that:
trunc(sum(bytes)/1024/1024/1024,2) df_bytes is going to return GB?
Chris Saxon
August 20, 2024 - 9:41 am UTC

Yes, that converts bytes to GB.
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.