| Actual Database Size [message #624110] |
Wed, 17 September 2014 16:33  |
|
|
Sir
I want to know my database's actual size. This is because, we have deleted/purged millions of records in number of tables.
I believe that when a row is deleted from the table, its spaces is still held by that table un-used and hence it will not be shown as free space.
Now my point is how to get back those spaces once occupied by the deleted data released to the free_space.
A complete detailed script would be highly appreciated
Thanks a lot, in advance
[MERGED by LF]
[Updated on: Thu, 18 September 2014 00:10] by Moderator Report message to a moderator |
|
|
|
| Re: Actual Database Size [message #624114 is a reply to message #624110] |
Wed, 17 September 2014 21:13  |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
ashussain wrote on Wed, 17 September 2014 16:33
A complete detailed script would be highly appreciated
In that case, a consultant's fee would be highly appreciated.
What have you attempted so far, other than posting this request twice? Everyone here (volunteers, all) are quite willing to help those who are willing to put forth some effort of their own.
And even if some is willing to to it pro bono, it would be totally dependent on both your OS and the full version of Oracle . . . neither of which you have revealed.
So you have purged millions of rows. Is this a permenant reduction in the number of rows, or will they just build back up over time? If the latter, why waste time reclaiming space just to have to start re-allocating it again?
[Updated on: Wed, 17 September 2014 21:19] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Actual Database Size [message #624321 is a reply to message #624129] |
Sun, 21 September 2014 11:17 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
@OP, your question is too broad to answer specifically. I echo Michel's outstanding question.
Please explain in words, what you understand by "database total size". DB is not just about database files, there are several other files which consume the physical memory. I see you are on 10g, however, generally speaking, with newer versions, it keeps varying.
If you are specific about your question, you can expect a specific answer.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Actual Database Size [message #625166 is a reply to message #625021] |
Thu, 02 October 2014 04:23 |
 |
atlas.dba
Messages: 5
Registered: August 2014
Location: Morocco
|
Junior Member |
|
|
I absolutely agree with you and thank you for your response,we can correct the query of redo log to contain the size of all member:
select sum(l2.bytes)/1024/1024/1024 redo_size
from v$logfile l1,sys.v_$log l2
where l1.group#=l2.group#
I have tested this query and it works fine.
On the other hand,to have the size of control files we can use this statement:
select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile
In summary,to have the size of all files of a database we use this query:
************************Global size of DataBase**************************
select d.data_size+t.temp_size+r.redo_size+control_size "volume en GB"
from ( select sum(bytes)/1024/1024/1024 data_size
from dba_data_files) d,
(select nvl(sum(bytes),0)/1024/1024/1024 temp_size
from dba_temp_files ) t,
(select sum(l2.bytes)/1024/1024/1024 redo_size
from v$logfile l1,v$log l2 where l1.group#=l2.group# ) r,
(select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 control_size
from v$controlfile)c;
**************************************************************************
[Updated on: Thu, 02 October 2014 04:30] Report message to a moderator |
|
|
|
|
|
| Re: Actual Database Size [message #625188 is a reply to message #625176] |
Thu, 02 October 2014 08:35 |
|
atlas.dba
Messages: 5
Registered: August 2014
Location: Morocco
|
Junior Member |
|
|
Hi,
Once again thank you for your response, you're right,the query must be:
select d.data_size+t.temp_size+r.redo_size+control_size "volume en GB"
from ( select sum(bytes)/1024/1024/1024 data_size
from dba_data_files) d,
(select nvl(sum(bytes),0)/1024/1024/1024 temp_size
from dba_temp_files ) t,
(select sum(bytes*members)/1024/1024/1024 redo_size
from v$log) r,
(select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 control_size
from v$controlfile)c;
[Updated on: Thu, 02 October 2014 08:37] Report message to a moderator |
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
).