Home » RDBMS Server » Server Administration » dataabse size
dataabse size [message #276492] Thu, 25 October 2007 05:22 Go to next message
bhoite_amol83
Messages: 110
Registered: June 2005
Location: Pune
Senior Member
Hi Experts,

If i want to know on how much size of database i am working then is there any way to find out?

Or is there any query through which i can find the size of the database?

Thanks in advance.
Re: dataabse size [message #276493 is a reply to message #276492] Thu, 25 October 2007 05:28 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

What you find inside this forum or what you find in google?
Re: dataabse size [message #276497 is a reply to message #276492] Thu, 25 October 2007 05:50 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
As i searched on the net i got the following query.

SELECT a.data_size+b.temp_size+c.redo_size "total_size"
FROM ( SELECT SUM(bytes) data_size
         FROM dba_data_files ) a,
     ( SELECT NVL(SUM(bytes),0) temp_size
         FROM dba_temp_files ) b,
     ( SELECT SUM(bytes) redo_size
         FROM sys.v_$log ) c
/


Thanks.
Re: dataabse size [message #276506 is a reply to message #276497] Thu, 25 October 2007 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Are you sure the database is defined by only these files?

Regards
Michel
icon14.gif  Re: dataabse size [message #280430 is a reply to message #276506] Tue, 13 November 2007 09:22 Go to previous messageGo to next message
alabaster_box
Messages: 4
Registered: October 2007
Junior Member
I think the query(posted by ora_2007) gives you the size of the data files excluding the control files.
To find the size of the control file ,you'll need to query the v$controlfile to locate the control files,then you can manually find out the size of these files via the operating system.
Re: dataabse size [message #280501 is a reply to message #276492] Tue, 13 November 2007 21:55 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

What about spfile and archived redo log files.
Re: dataabse size [message #280507 is a reply to message #280501] Tue, 13 November 2007 22:24 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
spfile is generally very small so it really should not matter, and archived redo logs also should not be taken into account, as far as I know. so the query posted by ora_2007 should be sufficient I think.
icon5.gif  Re: dataabse size [message #280510 is a reply to message #280430] Tue, 13 November 2007 22:45 Go to previous messageGo to next message
ora110
Messages: 42
Registered: September 2007
Location: China
Member
there are three kind of files:
datafile,redofile,controlfile
------------------------------
I think we can only concert the size of datafiles:
the following query can get all tablespaces usage.
---------------
SELECT df.tablespace_name Tablespace_NAME,
(df.extentspace - fs.freespace) Used_SP,
(df.totalspace-df.extentspace+fs.freespace) Free_SP,
df.totalspace Total_SP,
ROUND (100 * ((df.totalspace-df.extentspace+fs.freespace) / df.totalspace)) PERCENT_Free
FROM (SELECT tablespace_name,
ROUND (SUM (BYTES) / 1048576) extentspace,
ROUND (SUM (DECODE(AUTOEXTENSIBLE,'NO',BYTES,MAXBYTES)) / 1048576) totalspace
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name,
ROUND (SUM (BYTES) / 1048576) freespace
FROM dba_free_space
GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name;
Re: dataabse size [message #280511 is a reply to message #280507] Tue, 13 November 2007 22:55 Go to previous messageGo to next message
ora110
Messages: 42
Registered: September 2007
Location: China
Member
nirav_hyd wrote on Wed, 14 November 2007 12:24

spfile is generally very small so it really should not matter, and archived redo logs also should not be taken into account, as far as I know. so the query posted by ora_2007 should be sufficient I think.

yes,I think so too ,the parameter file and password file is not belong to database files,they are operating system files.
Re: dataabse size [message #280552 is a reply to message #280510] Wed, 14 November 2007 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
1/ Post query with code tags
2/ What percent free space has with database size?
3/ You missed temp files
4/ Control files can be hundred of megabytes, it is not small
5/ You can't start without a parameter file, so it belongs to the database (otherwise why rman should backup it?)

Regards
Michel
Re: dataabse size [message #280572 is a reply to message #280552] Wed, 14 November 2007 02:14 Go to previous messageGo to next message
ora110
Messages: 42
Registered: September 2007
Location: China
Member
Michel Cadot wrote on Wed, 14 November 2007 15:27

1/ Post query with code tags
2/ What percent free space has with database size?
3/ You missed temp files
4/ Control files can be hundred of megabytes, it is not small
5/ You can't start without a parameter file, so it belongs to the database (otherwise why rman should backup it?)

Regards
Michel


thank you for your advice.
Re: dataabse size [message #280924 is a reply to message #280572] Thu, 15 November 2007 03:33 Go to previous message
thorin666
Messages: 144
Registered: March 2007
Senior Member
i think that you must think what the person that ask you for the size of the database wants.
maybe using a du command on system.
or maybe the person want the size the data on the database. In this case i only think on something too simple like
select
      sum(bytes)
from
      dba_data_files 

i think that you can "play" with tablespaces organization using tablespace_name and group by clause.
Previous Topic: whats causing space
Next Topic: how to know the default next_extent, pct_increace on tables?
Goto Forum:
  


Current Time: Thu Dec 05 16:19:34 CST 2024