6). 检查表空间的使用情况 SELECT tablespace_name, max_m, count_blocks free_blk_cnt, sum_free_m,to_char(100*sum_free_m/sum_m, '99.99') || '%' AS pct_free FROM ( SELECT tablespace_name,sum(bytes)/1024/1024 AS sum_m FROM dba_data_files GROUP BY tablespace_name), ( SELECT tablespace_name AS fs_ts_name, max(bytes)/1024/1024 AS max_m, count(blocks) AS count_blocks, sum(bytes/1024/1024) AS sum_free_m FROM dba_free_space GROUP BY tablespace_name ) WHERE tablespace_name = fs_ts_name (7). 检查剩余表空间 SELECT tablespace_name, sum ( blocks ) as free_blk , trunc ( sum ( bytes ) /(1024*1024) ) as free_m, max ( bytes ) / (1024) as big_chunk_k, count (*) as num_chunks FROM dba_free_space GROUP BY tablespace_name;
监控剩余空间的大小或者是使用率: SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)", ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)",BLOCKS USED_BLOCKS FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D, (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
1.用DBA Studio里可以看到. 2.用SQL写: clear buffer clear columns clear breaks column a1 heading 'Tablespace' format a15 column a2 heading 'data file' format a45 column a3 heading 'Total|space' format 999,999.99 column a4 heading 'Free|space' format 999,999.99 column a5 heading 'Free|perc' format 999,999.99 break on a1 on report compute sum of a3 on a1 compute sum of a4 on a1 compute sum of a3 on report compute sum of a4 on report set linesize 120 select a.tablespace_name a1,a.file_name a2,a.avail a3,nvl(b.free,0) a4, nvl(round(((free/avail)*100),2),0) a5 from (select tablespace_name,substr(file_name,1,45) file_name,file_id, round(sum(bytes/(1024*1024)),3) avail from sys.dba_data_files group by tablespace_name,substr(file_name,1,45),file_id ) a, (select tablespace_name,file_id,round(sum(bytes/(1024*1024)),3) free from sys.dba_free_space group by tablespace_name,file_id ) b where a.file_id = b.file_id (+) order by 1,2
SELECT tablespace_name, max_m, count_blocks free_blk_cnt, sum_free_m,to_char(100*sum_free_m/sum_m, '99.99') || '%' AS pct_free
FROM ( SELECT tablespace_name,sum(bytes)/1024/1024 AS sum_m FROM dba_data_files GROUP BY tablespace_name),
( SELECT tablespace_name AS fs_ts_name, max(bytes)/1024/1024 AS max_m, count(blocks) AS count_blocks, sum(bytes/1024/1024) AS sum_free_m FROM dba_free_space GROUP BY tablespace_name )
WHERE tablespace_name = fs_ts_name
(7). 检查剩余表空间
SELECT tablespace_name, sum ( blocks ) as free_blk ,
trunc ( sum ( bytes ) /(1024*1024) ) as free_m,
max ( bytes ) / (1024) as big_chunk_k, count (*) as num_chunks
FROM dba_free_space GROUP BY tablespace_name;
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)", ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)",BLOCKS USED_BLOCKS
FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
2.用SQL写:
clear buffer
clear columns
clear breaks
column a1 heading 'Tablespace' format a15
column a2 heading 'data file' format a45
column a3 heading 'Total|space' format 999,999.99
column a4 heading 'Free|space' format 999,999.99
column a5 heading 'Free|perc' format 999,999.99
break on a1 on report
compute sum of a3 on a1
compute sum of a4 on a1
compute sum of a3 on report
compute sum of a4 on report
set linesize 120
select a.tablespace_name a1,a.file_name a2,a.avail a3,nvl(b.free,0) a4,
nvl(round(((free/avail)*100),2),0) a5
from (select tablespace_name,substr(file_name,1,45) file_name,file_id,
round(sum(bytes/(1024*1024)),3) avail
from sys.dba_data_files
group by tablespace_name,substr(file_name,1,45),file_id
) a,
(select tablespace_name,file_id,round(sum(bytes/(1024*1024)),3) free
from sys.dba_free_space
group by tablespace_name,file_id
) b
where a.file_id = b.file_id (+)
order by 1,2