| dataabse size [message #276492] |
Thu, 25 October 2007 05:22  |
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 #276497 is a reply to message #276492] |
Thu, 25 October 2007 05:50  |
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 #280507 is a reply to message #280501] |
Tue, 13 November 2007 22:24 |
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.
|
|
|
|
 Re: dataabse size [message #280510 is a reply to message #280430] |
Tue, 13 November 2007 22:45 |
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 |
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 #280572 is a reply to message #280552] |
Wed, 14 November 2007 02:14 |
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
