Hello Friend's
In this post we will discuss about the scripts for getting free and used space in tablespace and datafile
For Tablespace used and free space :
set pages 200
set line 180
col file_name for a60
col TABLESPACE_NAME for a25
SELECT A.TABLESPACE_NAME,round(SUM(A.TOTS)/1024/1024) "Tot size MB",
round(SUM(A.SUMB)/1024/1024) "Tot Free MB",
(SUM(A.SUMB)*100/SUM(A.TOTS)) "%FREE",
100-(SUM(A.SUMB)*100/SUM(A.TOTS)) "%USED",
round(SUM(A.LARGEST)/1024/1024) MAX_FREE,SUM(A.CHUNKS) CHUNKS_FREE
FROM (
SELECT TABLESPACE_NAME,0 TOTS,SUM(BYTES) SUMB,
MAX(BYTES) LARGEST,COUNT(*) CHUNKS
FROM SYS.DBA_FREE_SPACE A
GROUP BY TABLESPACE_NAME
UNION
SELECT TABLESPACE_NAME,SUM(BYTES) TOTS,0,0,0
FROM SYS.DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A, V$INSTANCE B
where A.TABLESPACE_NAME=upper('&ts')
GROUP BY UPPER(B.INSTANCE_NAME),A.TABLESPACE_NAME
/
For Datafile space usage stats :
select df.FILE_ID,df.FILE_NAME,df.TABLESPACE_NAME,round(df.BYTES/1024/1024) Allocated,
round(nvl(dfs.BYTES/1024/1024,0)) FREE_SPACE, round(((df.BYTES/1024/1024)-nvl((dfs.BYTES/1024/1024),0))) USED_SPACE
from dba_data_files df,
(select file_id,tablespace_name,sum(bytes) bytes
from dba_free_space
group by file_id,tablespace_name) dfs
where df.FILE_ID=dfs.FILE_ID (+)
AND df.TABLESPACE_NAME=upper('&ts')
order by df.FILE_ID
/
ConversionConversion EmoticonEmoticon