Home » RDBMS Server » Server Administration » Help : Free Space and Allocated space growth difference
Help : Free Space and Allocated space growth difference [message #242530] Sun, 03 June 2007 11:51 Go to next message
orasaket
Messages: 70
Registered: November 2006
Member
Hi,

I am using Oracle9i Enterprise Edition Release 9.2.0.6.0 on RHEL (Red Hat Linux)

We have executed some patch on the database.
And we want to know what was the database growth of the database during the patch.

I have created 2 tables
size_before_patch and size_after_patch and i am comparing the sizes.

However, the the difference in allocated sizes is 16 GB however the difference in Free space i am getting as 11 GB.

can anybody please explain me why the difference is not the same.

create table size_before_patch as
select d.tablespace_name, d.file_id, d.file_name, d.bytes/1024/1024 allocated, 
nvl(f.bytes,0)/1024/1024 free 
from (select tablespace_name, file_id, file_name, sum(bytes) bytes 
from dba_data_files
group by tablespace_name, file_id, file_name) d, 
(select tablespace_name, file_id, sum(bytes) bytes 
from dba_free_space 
group by tablespace_name, file_id) f 
where d.tablespace_name = f.tablespace_name(+) and d.file_id = f.file_id(+) 
order by tablespace_name, file_name;


Similarly i created table size_after_patch after executing the patch

select sum(allocated)/1024,sum(free)/1024 from size_before_patch
SUM(ALLOCATED)/1024	SUM(FREE)/1024
[B]491[/B].254791259766	[B]61[/B].3338012695313


select sum(allocated)/1024,sum(free)/1024 from size_after_patch
SUM(ALLOCATED)/1024	SUM(FREE)/1024
[B]507[/B].254791259766	[B]50[/B].6898803710938



Thanks and Regards,
OraSaket
Re: Help : Free Space and Allocated space growth difference [message #242534 is a reply to message #242530] Sun, 03 June 2007 12:20 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
>can anybody please explain me why the difference is not the same.
Because 16 does not equal 11.
Can you explain why the 2 values HAVE to be (or should be) the same?
My explanation is that allocated space grew by 16GB & 5GB was used during the upgrade.
Re: Help : Free Space and Allocated space growth difference [message #242587 is a reply to message #242530] Mon, 04 June 2007 04:01 Go to previous messageGo to next message
orasaket
Messages: 70
Registered: November 2006
Member
Hi,

Apologies..
But i am really unable to understand it.

What i am trying to understand is how much space was used during the patch.

And for this what i am assuming is that, if allocated space is growing by 16 GB that much space should be reduced from the Free Space.

Please help me in understanding this.

Thanks and Regards,
OraSaket
Re: Help : Free Space and Allocated space growth difference [message #242612 is a reply to message #242587] Mon, 04 June 2007 06:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It seems a quite simple problem.
Used space before: 491.254791259766-61.3338012695313 (1)
Used space after: 507.254791259766-50.6898803710938 (2)
Space used during patch: (2) - (1)

Regards
Michel
Re: Help : Free Space and Allocated space growth difference [message #242675 is a reply to message #242530] Mon, 04 June 2007 11:33 Go to previous messageGo to next message
orasaket
Messages: 70
Registered: November 2006
Member
Hi Michel,

Thanks for the quick reply

However, how come the free space didn't got reduced by equal volume i.e. 16 GB instead of 11 GB what it is showing?

Thanks and Regards,
OraSaket
Re: Help : Free Space and Allocated space growth difference [message #242684 is a reply to message #242675] Mon, 04 June 2007 12:03 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe something was created and then dropped.

Regards
Michel
Previous Topic: LOG PARALLELISM
Next Topic: details
Goto Forum:
  


Current Time: Mon Dec 05 12:40:55 CST 2016

Total time taken to generate the page: 0.14011 seconds