/*now后缀的是当前的状态，max后缀的是自动扩展后所能达到的状态。*/
SELECT tablespace_name,
--max_m,
--count_blocks free_blk_cnt,
sum_m - sum_free_m sum_used_m,
sum_free_m sum_free_m_now,
(sum_free_m + decode(sum_m_max, 0, sum_m, sum_m_max) - sum_m) sum_free_max,
sum_m sum_m_now,
decode(sum_m_max, 0, sum_m, sum_m_max) AS sum_m_max,
to_char(100 * sum_free_m / sum_m, '99999.99') || '%' AS pct_free_now,
to_char(100 *
(sum_free_m + decode(sum_m_max, 0, sum_m, sum_m_max) - sum_m) /
decode(sum_m_max, 0, sum_m, sum_m_max),
'99999.99') || '%' AS pct_free_max
FROM (SELECT tablespace_name,
sum(bytes) / 1024 / 1024 AS sum_m,
sum(decode(autoextensible,
'YES',
decode(sign(bytes - maxbytes), '1', bytes, maxbytes),
bytes)) / 1024 / 1024 AS sum_m_max
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(+)
-- AND tablespace_name not like 'APPS%'
ORDER BY 8;
--表空间使用情况的查询sql。
SELECT a.tablespace_name ,
SUM(a.tots /1048576) Tot_Size,
SUM(a.sumb /1048576) Tot_Free,
SUM(a.sumb) *100/SUM(a.tots) Percent_Free,
SUM(a.largest/1024) Max_Free,
SUM(a.chunks) Chunks_Free
FROM ( SELECT tablespace_name,
0 tots ,
SUM(bytes) sumb ,
MAX(bytes) largest ,
COUNT(*) chunks
FROM dba_free_space a
GROUP BY tablespace_name
UNION
SELECT tablespace_name,
SUM(bytes) tots,
0,0,0
FROM dba_data_files
GROUP BY tablespace_name
)
a
GROUP BY a.tablespace_name
ORDER BY pct_free;
Script 1: Tablespace information
SET linesize 150
column tablespace_name format a20 heading 'Tablespace'
column sumb format 999,999,999
column extents format 9999
column bytes format 999,999,999,999
column largest format 999,999,999,999
column Tot_Size format 999,999 Heading 'Total| Size(Mb)'
column Tot_Free format 999,999,999 heading 'Total Free(MB)'
column Pct_Free format 999.99 heading '% Free'
column Chunks_Free format 9999 heading 'No Of Ext.'
column Max_Free format 999,999,999 heading 'Max Free(Kb)'
SET echo OFF
PROMPT FREE SPACE AVAILABLE IN TABLESPACES
SELECT a.tablespace_name ,
SUM(a.tots /1048576) Tot_Size,
SUM(a.sumb /1048576) Tot_Free,
SUM(a.sumb) *100/SUM(a.tots) Percent_Free,
SUM(a.largest/1024) Max_Free,
SUM(a.chunks) Chunks_Free
FROM ( SELECT tablespace_name,
0 tots ,
SUM(bytes) sumb ,
MAX(bytes) largest ,
COUNT(*) chunks
FROM dba_free_space a
GROUP BY tablespace_name
UNION
SELECT tablespace_name,
SUM(bytes) tots,
0,0,0
FROM dba_data_files
GROUP BY tablespace_name
)
a
GROUP BY a.tablespace_name
ORDER BY pct_free;
Output like:
Total
Tablespace Size(Mb) Total Free(MB) % Free Max Free(Kb) No Of Ext.
-------------------- --------- -------------- ------- ------------ ----------
SYSTEM 790 3 .38 3,008 2
SYSAUX 752 52 6.86 32,768 132
USERS 5 1 11.25 576 1
MGMT_ECM_DEPOT_TS 100 43 43.25 43,968 2
MGMT_TABLESPACE 13,940 8,388 60.17 155,200 1594
UNDOTBS1 605 491 81.07 311,360 44
PATROL 1 1 93.75 960 1
Script 2: Tablespaces With Less Than 10% Free Space
set pagesize 300
set linesize 100
column tablespace_name format a15 heading 'Tablespace'
column sumb format 999,999,999
column extents format 9999
column bytes format 999,999,999,999
column largest format 999,999,999,999
column Tot_Size format 999,999 Heading 'Total Size(Mb)'
column Tot_Free format 999,999,999 heading 'Total Free(Kb)'
column Pct_Free format 999.99 heading '% Free'
column Max_Free format 999,999,999 heading 'Max Free(Kb)'
column Min_Add format 999,999,999 heading 'Min space add (MB)'
ttitle center 'Tablespaces With Less Than 10% Free Space' skip 2
set echo off
select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1024) Tot_Free,
sum(a.sumb)*100/sum(a.tots) Pct_Free,
ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add
from
(
select tablespace_name,0 tots,sum(bytes) sumb
from dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0 from
dba_data_files
group by tablespace_name) a
group by a.tablespace_name
having sum(a.sumb)*100/sum(a.tots) < 10
order by pct_free;
