Oracle Database Size

SET FEEDBACK OFF
SET HEADING OFF
SET AUTOPRINT OFF
–SET NOTIFY OFF
SET VERIFY OFF
PROMPT ……………………………………………………..

SELECT    ‘** TableSpace Usage Snap For ‘
|| NAME
|| ‘ Instance Taken On ‘
|| RTRIM (TO_CHAR (SYSDATE, ‘Day’))
|| ‘ ‘
|| TO_CHAR (SYSDATE, ‘DD-MON-YYYY HH24:MI:SS’)
|| ‘ ** ‘
FROM   v$database;
COMPUTE SUM LABEL ‘Total’ OF Tot_Spc_Alloc ON report

BREAK ON REPORT
COLUMN TSname        Format A25               Heading ‘Tablespace Name’
COLUMN TSid          Format           999     Heading ‘Tablespace#’
COLUMN Tot_Spc_Alloc Format 9,999,999,990.00  Heading ‘Total Space|Allocated’
COLUMN Tot_Spc_Free  Format   999,999,990.00  Heading ‘Total Free|Space’
COLUMN Tot_Spc_Used  Format   999,999,990.00  Heading ‘Total Space|Used’
COLUMN Pct_Used      Format           990.000 Heading ‘Percent|Used’
COLUMN Pct_Free      Format           990.000 Heading ‘Percent|Free’
COLUMN FileName      Format A65               Heading ‘File Name’
COLUMN fileid        Format 99999             Heading ‘File|ID’
SET LINESIZE 10000
SET HEADING ON
SELECT tsid, tsname, pct_used, pct_free, tot_spc_alloc, tot_spc_used, tot_spc_free
FROM   (SELECT   c.ts# tsid,
a.tsname,
(1 – (b.tot_spc_free / a.tot_spc_alloc)) * 100 pct_used,
(b.tot_spc_free / a.tot_spc_alloc) * 100 pct_free,
a.tot_spc_alloc / 1024 / 1024 tot_spc_alloc,
(a.tot_spc_alloc – b.tot_spc_free) / 1024 / 1024 tot_spc_used,
b.tot_spc_free / 1024 / 1024 tot_spc_free
FROM     (SELECT   tablespace_name tsname, SUM (BYTES) tot_spc_alloc
FROM     DBA_DATA_FILES
GROUP BY tablespace_name) a,
(SELECT   tablespace_name tsname, SUM (BYTES) tot_spc_free, MAX (BYTES) max_b2
FROM     DBA_FREE_SPACE
GROUP BY tablespace_name) b,
v$tablespace c
WHERE    a.tsname = b.tsname AND a.tsname = c.NAME
ORDER BY c.ts#);