查看表空間的名稱及大小
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;
====================================
set pages 100
col ts_name form a20 head 'Tablespace'
col pieces form 9990 head 'Pcs'
col ts_size form 999,990 head 'SizeMb'
col largestpc form 999,990 head 'LrgMB'
col totalfree form 999,990 head 'FreeMb'
col pct_free form 990 head '%Free'
col whatsused form 999,990 head 'Used'
col pct_used form 990 head '%Used'
col problem head 'Prob??'
--spool /tmp/tbs_size.log
select q2.other_tname ts_name, pieces, ts_size ts_size,
??????? nvl(largest_chunk,0) largestpc, nvl(total_free,0) totalfree,
??????? nvl(round((total_free/ts_size)*100,2),0) pct_free,
??????? ts_size-total_free whatsused,
??????? nvl(100-round((total_free/ts_size)*100,2),100) pct_used,
??????? decode(nvl(100-round((total_free/ts_size)*100,0),100),
?????????????? 85,'+',86,'+',87,'+',88,'+',89,'++',90,'++',91,'++',
?????????????? 92,'++',93,'++',94,'+++',95,'+++',96,'+++',97,'++++',
?????????????? 98,'+++++',99,'+++++',100,'+++++','') problem
? from (select dfs.tablespace_name,count(*) pieces,
?????????????? round(max(dfs.bytes)/1024/1024,2) largest_chunk,
?????????????? round(sum(dfs.bytes)/1024/1024,2) total_free
???????? from dba_free_space dfs group by tablespace_name) q1,
?????? (select tablespace_name other_tname,
?????????????? round(sum(ddf2.bytes)/1024/1024,2) ts_size
????????? from dba_data_files ddf2 group by tablespace_name) q2
?where q2.other_tname = q1.tablespace_name(+)
?order by nvl(100-round((total_free/ts_size)*100,0),100) desc;
====================================
查看表空間使用情況的SQL語句：
?? SELECT a.tablespace_name "表空間名",total 表空間大小,free 表空間剩余大小,
(total-free) 表空間使用大小,
ROUND((total-free)/total,4)*100 "使用率 %"
FROM? (SELECT tablespace_name,SUM(bytes) free FROM DBA_FREE_SPACE
GROUP BY tablespace_name ) a,
(SELECT tablespace_name,SUM(bytes) total FROM DBA_DATA_FILES
GROUP BY tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name??
=====================================?
rem??? fsfi value compute
??? rem??? fsfi.sql
??? column fsfi format 999,99
??? select tablespace_name,sqrt(max(blocks)/sum(blocks))*
?????????? (100/sqrt(sqrt(count(blocks)))) fsfi
??? from dba_free_space
??? group by tablespace_name order by 1;
??? spool fsfi.rep;
??? /
??? spool off;