Home » RDBMS Server » Server Administration » database size (Oracle 10g R2,Linux AS4)
database size [message #379598] Wed, 07 January 2009 04:52 Go to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
hello,

How can we determin the database size . I queried

SELECT SUM(bytes)/1024/1024,tablespace_name,file_name FROM DBA_DATA_FILES
WHERE tablespace_name <> 'UNDOTBS1'
GROUP BY tablespace_name,file_name .

This showed me the output . But i'm looking for the database size in the last month . I'm trying to go for sysdate-30 , but not knowing where to fix it .

Can somebody help me on this.

Regards,

Raj
Re: database size [message #379604 is a reply to message #379598] Wed, 07 January 2009 05:28 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>I'm trying to go for sysdate-30 , but not knowing where to fix it

I believe we already tried to explain this to you.
Unless you save the information in a custom table, it is not saved anywhere.
v$datafile has a creation_time.
Join it with dba_data_file.
All you can do is, list and sum the size of datfiles that has
a create_time sysdate-30

Edit:
Or just use v$datafile

[Updated on: Wed, 07 January 2009 05:33]

Report message to a moderator

Re: database size [message #379610 is a reply to message #379604] Wed, 07 January 2009 05:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I believe we already tried to explain this to you.

Yes, reread one of your previous topics:
http://www.orafaq.com/forum/m/377765/102589/#msg_377765

Regards
Michel
Re: database size [message #379612 is a reply to message #379598] Wed, 07 January 2009 06:01 Go to previous messageGo to next message
swoop1973
Messages: 4
Registered: January 2009
Junior Member
The dba view dba_hist_tbspc_space_usage may be of use but this would depend on you being licensed, the version of oracle you have and the AWR retention policies you have defined (none of which you cared to share Confused )

We use it at our shop (as well as having a full capacity planning program !) when adding space to quickly get a ballpark figure on how the tablespace has grown in the past month (our retention for AWR).

Re: database size [message #379613 is a reply to message #379612] Wed, 07 January 2009 06:05 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>none of which you cared to share
Or cared to even to look into. Smile
We already suggested DBA_HIST*
Re: database size [message #379625 is a reply to message #379613] Wed, 07 January 2009 06:58 Go to previous message
swoop1973
Messages: 4
Registered: January 2009
Junior Member
Mahesh Rajendran wrote on Wed, 07 January 2009 06:05
>>none of which you cared to share
Or cared to even to look into. Smile
We already suggested DBA_HIST*


I did read the other thread, saw you suggested DBA_HIST_SEG_STAT, didnt see dba_hist_tbspc_space_usage mentioned so I thought I would explicitly. Thumbs Up
Previous Topic: Remove spfile and create pfile
Next Topic: Possible issues when migrating data from WE8ISO8859P1 to UTF8 (merged)
Goto Forum:
  


Current Time: Sat Dec 10 15:07:30 CST 2016

Total time taken to generate the page: 0.09165 seconds