Run the following sql to get the space report in Oracle
set pagesize 500
set lines 80
column d1 noprint new_value _d1
column name noprint new_value n1
column sumb format 999,999,999
column largest head 'Largest|Avail.' format 99,999,999
column Tot_Size head 'Total|Size' format 99,999,999
column Tot_Free head 'Total|Free' format 99,999,999
column Pct_Free head '%|Free' format 999
column Chunks_Free head 'Chunks|Free' format 99,999,999
column Max_Contg_Free head 'Contg.|Free' format 99,999,999
column tablespace_name head 'Tablespace|Name' format a20
column owner head 'Owner' format a8
column segment_name head 'Segment|Name' format a30
column stype head ' ' format a1
column extents head 'Exts|Used' format 9,999
column Kbytes head 'KBytes|Used' format 999,999
column max_extents head 'Max|Exts' format 9,999
column next head 'Next|Ext' format 999,999
column pct head '% |Inc' format 999
col n2 head ' ' for a30
SELECT to_char(sysdate,'MMDDYY') d1,
name
FROM v$database ;
set echo off
rem spool space_&n1&_d1..lis
SELECT 'Database > '||name||chr(10)||
'As of > '||to_char(sysdate,'Mon DD YYYY')||chr(10) n2
from v$database ;
PROMPT SPACE AVAILABLE IN TABLESPACES
PROMPT Col 1 - Tablespace
PROMPT Col 2 - Tablespace Size in (MB)
PROMPT Col 3 - Free Space available (MB)
PROMPT Col 4 - Percentage Free
PROMPT Col 5 - Contiguous Free (MB)
PROMPT Col 6 - Number of Chunks free
PROMPT ==============================
SELECT a.tablespace_name ,
sum(a.tots)/1048576 Tot_Size,
sum(a.sumb)/1048576 Tot_Free,
sum(a.sumb)*100/sum(a.tots) Pct_Free,
sum(a.largest)/1048576 Max_Contg_Free,
sum(a.chunks) Chunks_Free
FROM
(
SELECT tablespace_name,
0 tots,
sum(bytes) sumb,
max(bytes) largest,
count(*) chunks
FROM dba_free_space a
GROUP by tablespace_name
UNION
SELECT tablespace_name,
sum(bytes) tots,
0,
0,
0
FROM dba_data_files
GROUP by tablespace_name
) a
GROUP by rollup(a.tablespace_name)
ORDER BY 1 asc ;
clear columns
Hope this helps!. Regards Rupam
