Home » RDBMS Server » Server Administration » Calculating the size of the depending on the AUTOEXTENSIBALE (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
Calculating the size of the depending on the AUTOEXTENSIBALE [message #679449] Mon, 02 March 2020 00:22 Go to next message
revathitirun
Messages: 14
Registered: May 2011
Junior Member

Hi All ,

I need to calculate the my database total, used and frees size. I am using the below query :



SELECT Reserved_Space_gb,
  Reserved_Space_gb - Free_Space_gb Used_Space_gb,
  Free_Space_gb
FROM
  (SELECT
    (SELECT ROUND(SUM(bytes/(1014*1024*1024)),2)  FROM dba_data_files) Reserved_Space_gb,
    (SELECT ROUND(SUM(bytes/(1024*1024*1024)),2) FROM dba_free_space) Free_Space_gb
  FROM dual
  );

But as per my DBA Calculation was different. They are depending on the autoextensible parameter of table space.
Could any body please explain the significance autoextensible parametric to calculate the size of the database


Thanks
Revathi
Re: Calculating the size of the depending on the AUTOEXTENSIBALE [message #679450 is a reply to message #679449] Mon, 02 March 2020 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 67294
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

First:
  (SELECT
    (SELECT ROUND(SUM(bytes/(1014*1024*1024)),2)  FROM dba_data_files) Reserved_Space_gb,
    (SELECT ROUND(SUM(bytes/(1024*1024*1024)),2) FROM dba_free_space) Free_Space_gb
  FROM dual
  );
is just
    (SELECT ROUND(SUM(bytes/(1014*1024*1024)),2) Reserved_Space_gb FROM dba_data_files),
    (SELECT ROUND(SUM(bytes/(1024*1024*1024)),2) Free_Space_gb FROM dba_free_space)
no need of this dual stuff.

AUTOEXTENSIBLE just means that the file may extend, at OS level, if space is needed; your query gives the allocated space of and used and free space in the database for the current size of the files.

[Updated on: Mon, 02 March 2020 02:43]

Report message to a moderator

Re: Calculating the size of the depending on the AUTOEXTENSIBALE [message #679451 is a reply to message #679450] Mon, 02 March 2020 04:54 Go to previous messageGo to next message
revathitirun
Messages: 14
Registered: May 2011
Junior Member
Thanks for your explanation Michel

In order to derive the used space we went with DUAL query model.
So we can make use the BYTES always to calculate the allocated and free space available in the database irrespective of the AUTO EXTENSIBLE Parameter


Thanks once again
Revathi.T
Re: Calculating the size of the depending on the AUTOEXTENSIBALE [message #679454 is a reply to message #679451] Mon, 02 March 2020 09:57 Go to previous messageGo to next message
Michel Cadot
Messages: 67294
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
In order to derive the used space we went with DUAL query model.

You don't need it, just replace as I suggested and you will see.
In general, never use SELECT cursor in SELECT clause unless you are an expert in SQL.

Quote:
So we can make use the BYTES always to calculate the allocated and free space available in the database irrespective of the AUTO EXTENSIBLE Parameter

It depends on what you need or want or has been asked to you.

Re: Calculating the size of the depending on the AUTOEXTENSIBALE [message #679455 is a reply to message #679449] Mon, 02 March 2020 13:57 Go to previous message
John Watson
Messages: 8319
Registered: January 2010
Location: Global Village
Senior Member
dba_tablespace_usage_metrics may help you to reconcile the results of your query with what your colleagues are telling you.
Previous Topic: USERS tablespace is filling fast
Next Topic: How many transaction taken place in given time
Goto Forum:
  


Current Time: Mon Aug 10 07:01:57 CDT 2020