Hi All,
I want to calculate the total database size. I also wanted to know how much database space is used and how much is free.
Can somebody help me with this?
Thanks in advance..
Printable View
Hi All,
I want to calculate the total database size. I also wanted to know how much database space is used and how much is free.
Can somebody help me with this?
Thanks in advance..
This script will give you the total space used and free by tablespace, you would then need to add the size of redo logs and controlfiles to get your overall size.
SET PAGESIZE 66
COLUMN pct_free FORMAT 999.99 HEADING "% Free"
COLUMN name FORMAT A16 HEADING "Tablespace Name"
COLUMN mbytes FORMAT 99,999,999 HEADING "Total MBytes"
COLUMN used FORMAT 99,999,999 HEADING "Used Mbytes"
COLUMN free FORMAT 99,999,999 HEADING "Free Mbytes"
BREAK ON REPORT
COMPUTE SUM OF mbytes ON REPORT
COMPUTE SUM OF free ON REPORT
COMPUTE SUM OF used ON REPORT
SELECT
fs.tablespace_name name,
df.totalspace mbytes,
(df.totalspace - fs.freespace) used,
fs.freespace free,
100 * (fs.freespace / df.totalspace) pct_free
FROM
(SELECT
tablespace_name,
ROUND(SUM(bytes) / 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(+);
Regards
what is your oracle database version ?
jovery : i think dba_temp_files should also be included in teh sql statement .
prakashs43 your quite right, for my purposes I deliberatly leave temp segments out of the script but in this case you would need to include them.
Regards