Tablespace used 情况

SELECT a.tablespace_name,a.bytes bytes_used,b.largest,round(((a.bytes-b.bytes)/a.bytes)*100,2)percent_used

FROM

(SELECT tablespace_name,sum(bytes) bytes FROM dba_data_files GROUP BY tablespace_name)a,

(SELECT tablespace_name,sum(bytes) bytes,max(bytes) largest FROM dba_free_space GROUP BY tablespace_name)b

WHERE a.tablespace_name =b.tablespace_name

ORDER BY ((a.bytes-b.bytes)/a.bytes) DESC

 

检查表空间的使用情况
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 ;

检查剩余表空间

/* Formatted on 2007/03/01 15:56 (Formatter Plus v4.8.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;