查询表空间使用情况

 

 

 

  select ts_name
,        extensible_bytes               extensible
,        allocated_bytes                allocated
,        alloc_free
,        allocated_bytes - alloc_free   used
,        100 * (allocated_bytes - alloc_free) / allocated_bytes pct_used
,        to_number(decode(allocated_bytes, extensible_bytes, NULL,
         extensible_bytes
      - (allocated_bytes - alloc_free))) ne
,        to_number(decode(allocated_bytes, extensible_bytes, NULL, 
         100 * (extensible_bytes - (extensible_bytes - (allocated_bytes - alloc_free)))
       / extensible_bytes))             pct_used_ext
    from (  
  select a.tablespace_name              ts_name
,        sum(decode(b.autoextensible, 'YES', b.maxbytes, b.bytes))
       / count(distinct a.file_id||'.'||a.block_id) extensible_bytes
,        sum(b.bytes)/count(distinct a.file_id||'.'||a.block_id)  allocated_bytes
,        sum(a.bytes)/count(distinct b.file_id) alloc_free
    from sys.dba_free_space             a
,        sys.dba_data_files             b
   where a.tablespace_name              = b.tablespace_name (+)
group by a.tablespace_name
,        b.tablespace_name)
order by 6 desc;

TS_NAME	EXTENSIBLE	ALLOCATED	ALLOC_FREE	USED	PCT_USED	NE	PCT_USED_EXT
SYSTEM	34,359,721,984	786,432,000	6,684,672	779,747,328	99.15	33,579,974,656	2.2693644854
SYSAUX	34,359,721,984	587,202,560	37,617,664	549,584,896	93.59375	33,810,137,088	1.5995033262
USERS	34,359,721,984	5,242,880	3,866,624	1,376,256	26.25	34,358,345,728	0.004005434
UNDOTBS1	34,359,721,984	94,371,840	73,465,856	20,905,984	22.1527777778	34,338,816,000	0.0608444504
TESTTBS	68,719,443,968	209,715,200	207,618,048	2,097,152	1	68,717,346,816	0.0030517593