OraFAQ Forum: Server Administration » Calculating the size of the depending on the AUTOEXTENSIBALE

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)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Calculating the size of the depending on the AUTOEXTENSIBALE [message #679449]

Mon, 02 March 2020 00:22

revathitirun
Messages: 16
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

Report message to a moderator

Re: Calculating the size of the depending on the AUTOEXTENSIBALE [message #679450 is a reply to message #679449]

Mon, 02 March 2020 01:24

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

revathitirun
Messages: 16
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

Report message to a moderator

Re: Calculating the size of the depending on the AUTOEXTENSIBALE [message #679454 is a reply to message #679451]

Mon, 02 March 2020 09:57

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Calculating the size of the depending on the AUTOEXTENSIBALE [message #679455 is a reply to message #679449]

Mon, 02 March 2020 13:57

John Watson
Messages: 8960
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.

Report message to a moderator

Previous Topic:	USERS tablespace is filling fast
Next Topic:	How many transaction taken place in given time

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 28 06:50:21 CST 2024