Home » RDBMS Server » Server Administration » Actual Database Size (Oracle 10g (10.2.0.5) windows server 2003)
Actual Database Size [message #624110] Wed, 17 September 2014 16:33 Go to next message
ashussain
Messages: 35
Registered: March 2011
Location: Saudi Arabia
Member

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 Go to previous messageGo to next message
EdStevens
Messages: 1005
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 #624117 is a reply to message #624110] Wed, 17 September 2014 21:32 Go to previous messageGo to next message
BlackSwan
Messages: 26275
Registered: January 2009
Location: SoCal
Senior Member
>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.
You are correct.

>Now my point is how to get back those spaces once occupied by the deleted data released to the free_space.

incapable or unwilling to use GOOGLE yourself?
Re: Actual Database Size [message #624129 is a reply to message #624110] Thu, 18 September 2014 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 65973
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Now my point is how to get back those spaces once occupied by the deleted data released to the free_space.


ALTER TABLE SHRINK SPACE

Quote:
I want to know my database's actual size.


Define "database's actual size".

Re: Actual Database Size [message #624321 is a reply to message #624129] Sun, 21 September 2014 11:17 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3131
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 #624862 is a reply to message #624110] Fri, 26 September 2014 12:49 Go to previous messageGo to next message
atlas.dba
Messages: 5
Registered: August 2014
Location: Morocco
Junior Member
Hi,

To know the size of physical files of your database you can user the query:

select d.data_size+t.temp_size+r.redo_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)/1024/1024/1024 redo_size
from sys.v_$log ) r;

Regards
Re: Actual Database Size [message #624864 is a reply to message #624862] Fri, 26 September 2014 12:55 Go to previous messageGo to next message
Michel Cadot
Messages: 65973
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ You don't know if this is what OP asks
2/ The query is wrong.

Re: Actual Database Size [message #625017 is a reply to message #624864] Tue, 30 September 2014 06:13 Go to previous messageGo to next message
atlas.dba
Messages: 5
Registered: August 2014
Location: Morocco
Junior Member
if it's wrong,could you please clarify why? and so give us the right answer

*It's always easy to say wrong or true....

[Updated on: Tue, 30 September 2014 06:13]

Report message to a moderator

Re: Actual Database Size [message #625021 is a reply to message #625017] Tue, 30 September 2014 06:45 Go to previous messageGo to next message
Michel Cadot
Messages: 65973
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Independently of the current topic, It is wrong because it assumes there is only one member per redo log groups (and it does not take into account control files).

For this topic, no one can say what can be the query as Op did not tell what he means by "actual database size".

Re: Actual Database Size [message #625166 is a reply to message #625021] Thu, 02 October 2014 04:23 Go to previous messageGo to next message
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 #625176 is a reply to message #625166] Thu, 02 October 2014 05:07 Go to previous messageGo to next message
Michel Cadot
Messages: 65973
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You don't need to join v$log and v$lofgile, v$log contains a MEMBERS column which gives you the number of members of the group.

Please read How to use [code] tags and make your code easier to read.

Re: Actual Database Size [message #625188 is a reply to message #625176] Thu, 02 October 2014 08:35 Go to previous messageGo to next message
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

Re: Actual Database Size [message #625198 is a reply to message #625188] Thu, 02 October 2014 10:15 Go to previous messageGo to next message
Michel Cadot
Messages: 65973
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please indent the code or use SQL Formatter which gives:
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; 

Easier to understand.
Re: Actual Database Size [message #625210 is a reply to message #625198] Thu, 02 October 2014 12:36 Go to previous messageGo to next message
Michel Cadot
Messages: 65973
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Forgot to mention: standby redo logs are missing (take care there is a trap Smile ).

Re: Actual Database Size [message #625226 is a reply to message #625210] Fri, 03 October 2014 06:32 Go to previous message
John Watson
Messages: 7669
Registered: January 2010
Location: Global Village
Senior Member
What about tempfiles being created as sparse files? Initial size effectivily zero? Is there a way to bring that into the algorithm?
Previous Topic: How to set NULL on LOG_ARCHIVE_DEST_N
Next Topic: Impact of parallel index rebuild on partition index
Goto Forum:
  


Current Time: Mon Nov 19 01:21:49 CST 2018