Space Management

1) Tablespace Utilization Report:
select f.tablespace_name,a.total,u.used,f.free,round((u.used/a.total)*100) "% used",round((f.free/a.total)*100) "% Free"
from
(select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) used from dba_extents group by tablespace_name) u,
(select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name
and a.tablespace_name = u.tablespace_name

2) Show data files usage:
SELECT SUBSTR(max(A.TABLESPACE_NAME),1,16) "Tablespace",A.FILE_ID "File ID",substr(max(A.file_name),1,43) "Data file",substr(max(A.status),1,10) "Status",(MAX(A.BYTES)-nvl(sum(B.BYTES),0))/1024/1024 "USED SIZE(Mb)",MAX(A.BLOCKS)-nvl(sum(B.BLOCKS),0) "USED BLOCKS",TO_CHAR((MAX(A.BYTES)-nvl(sum(B.BYTES),0))*100/MAX(A.BYTES),'999.99')'%' "USED USAGE",nvl(sum(B.BYTES),0)/1024/1024 "FREE SIZE(Mb)",nvl(SUM(B.BLOCKS),0) "FREE BLOCKS",TO_CHAR(nvl(SUM(B.BYTES),0)*100/MAX(A.BYTES),'999.99')'%' "FREE USAGE",MAX(A.bytes)/1024/1024 "TOTAL SIZE(Mb)",MAX(A.blocks) "TOTAL BLOCKS"
from dba_data_files A,DBA_FREE_SPACE B
WHERE A.FILE_ID=B.FILE_ID(+)
group by a.file_idorder by 1,2;

3)Tables that are Highly fragmented:
SELECT segment_name table_name , COUNT(*) extents
FROM dba_segments
WHERE owner NOT IN ('SYS', 'SYSTEM')
GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX( COUNT(*) )
FROM dba_segments
GROUP BY segment_name)