free_space_tablespace.sql

oracle dba 常用的管理脚本, 覆盖日常的系统管理.

--* File Name    : Free_Space_TableSpace.sql
--* Author       : Ivory.Cheng
--* Description  : Displays space usage for each datafile.
--* Requirements : Access to the DBA views.
--* Call Syntax  : ---
--* Last Modified: 2003-11
select A.tablespace_name,A.Capacity,B.Free_Space,(B.Free_Space/A.Capacity)*100 as SPARSITY
from 
(select tablespace_name,sum(BYTES)/1024/1024 as Capacity from DBA_DATA_FILES 
group by tablespace_name) A ,
(select tablespace_name,sum(bytes)/1024/1024 as Free_Space from dba_free_space
group by tablespace_name) B
where A.tablespace_name=B.tablespace_name
order by SPARSITY asc


--select* from dba_extents where segment_name='GL_JE_BATCHES_CS';