点击(此处)折叠或打开
-
--tablespace size
-
select a.tablespace_name,
-
round(a.bytes_alloc / 1024 / 1024 /1024,2) Size_G,
-
round(nvl(b.bytes_free, 0) / 1024 / 1024 /1024,2) Free_G,
-
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024 /1024,2) Used_G,
-
round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Free_Percent,
-
100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Usage_Percent,
-
round(maxbytes/ 1024 / 1024 /1024) Max_G
-
from ( select f.tablespace_name,
-
sum(f.bytes) bytes_alloc,
-
sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
-
from dba_data_files f
-
group by tablespace_name) a,
-
( select f.tablespace_name,
-
sum(f.bytes) bytes_free
-
from dba_free_space f
-
group by tablespace_name) b
-
where a.tablespace_name = b.tablespace_name (+)
-
union all
-
select h.tablespace_name,
-
round(sum(h.bytes_free + h.bytes_used) / 1024 / 1024 /1024,2) Size_G,
-
round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1024 / 1024 /1024,2) Free_G,
-
round(sum(nvl(p.bytes_used, 0))/ 1024 / 1024 /1024,2) Used_G,
-
round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
-
100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
-
round(sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes) / 1024 / 1024 /1024),2) Max_G
-
from sys.v_$TEMP_SPACE_HEADER h,
-
sys.v_$Temp_extent_pool p,
-
dba_temp_files f
-
where p.file_id(+) = h.file_id
-
and p.tablespace_name(+) = h.tablespace_name
-
and f.file_id = h.file_id
-
and f.tablespace_name = h.tablespace_name
-
group by h.tablespace_name
-
ORDER BY 1;
-
-
--tablespace can resize
-
select fs.tablespace_name, fs.file_id, round(nvl(sum(fs.bytes), 0) / 1048576) can_shrink_by
-
from dba_free_space fs,
-
(
-
select e1.tablespace_name, e1.file_id, e1.block_id + e1.blocks last_block
-
from dba_extents e1,
-
(Select tablespace_name, file_id, max(block_id) block_id
-
from dba_extents
-
group by tablespace_name, file_id) e2
-
where e1.tablespace_name = e2.tablespace_name
-
and e1.file_id = e2.file_id
-
and e1.block_id = e2.block_id
-
) e
-
where fs.tablespace_name = e.tablespace_name
-
and fs.file_id = e.file_id
-
and fs.block_id >= e.last_block
-
group by fs.tablespace_name, fs.file_id;
-
-
-
--datafile size
-
SELECT t.tablespace_name, 'Datafile' file_type,
-
t.status tablespace_status, d.status file_status,
-
round((d.bytes - NVL(f.sum_bytes, 0)) / 1024 / 1024 /1024,2) used_G,
-
round(NVL(f.sum_bytes, 0) / 1024 / 1024 /1024,2) free_G,
-
t.initial_extent,t.next_extent, t.min_extents, t.max_extents, t.pct_increase,
-
d.file_name, d.file_id, d.autoextensible, d.maxblocks,
-
round(d.maxbytes / 1024 / 1024 /1024,2) max_G,
-
nvl(d.increment_by,0) increment_by, t.block_size
-
FROM (SELECT tablespace_name, file_id, SUM(bytes) sum_bytes
-
FROM DBA_FREE_SPACE
-
GROUP BY tablespace_name, file_id) f,
-
DBA_DATA_FILES d,
-
DBA_TABLESPACES t
-
WHERE t.tablespace_name = d.tablespace_name
-
AND f.tablespace_name(+) = d.tablespace_name
-
AND f.file_id(+) = d.file_id
-
GROUP BY t.tablespace_name, d.file_name, d.file_id, t.initial_extent,
-
t.next_extent, t.min_extents, t.max_extents,
-
t.pct_increase, t.status, d.bytes, f.sum_bytes, d.status,
-
d.AutoExtensible, d.maxblocks, d.maxbytes, d.increment_by, t.block_size
-
UNION ALL
-
SELECT h.tablespace_name,
-
'Tempfile',
-
ts.status,
-
t.status,
-
round(SUM(NVL(p.bytes_used, 0)) / 1024 / 1024 /1024,2) used_G,
-
round(SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / 1024 / 1024 /1024,2) free_G,
-
-1,
-
-1,
-
-1,
-
-1,
-
-1,
-
t.file_name,
-
t.file_id,
-
t.autoextensible, t.maxblocks,
-
round(t.maxbytes / 1024 / 1024 /1024,2) max_G,
-
nvl(t.increment_by, 0) increment_by, ts.block_size
-
FROM sys.V_$TEMP_SPACE_HEADER h, sys.V_$TEMP_EXTENT_POOL p, sys.DBA_TEMP_FILES t, sys.dba_tablespaces ts
-
WHERE p.file_id(+) = h.file_id
-
AND p.tablespace_name(+) = h.tablespace_name
-
AND h.file_id = t.file_id
-
AND h.tablespace_name = t.tablespace_name
-
and ts.tablespace_name = h.tablespace_name
-
GROUP BY h.tablespace_name, t.status, t.file_name, t.file_id, ts.status,
-
t.autoextensible, t.maxblocks, t.maxbytes, t.increment_by, ts.block_size
-
ORDER BY 1, 5 DESC;
-
-
--datafile can resize
-
Select sum(bytes)/1024/1024 Can_Resize_M
-
from dba_free_space
-
where file_id = 4
-
and block_id >= nvl((Select (block_id + (bytes/8192))
-
from dba_extents
-
where block_id = (Select max(block_id)
-
from dba_extents
-
where file_id = 4
-
)
-
and file_id = 4), 0);
-
--tempfile can resize
-
Select round(sum(bytes) / 1048576) Can_Resize_M
-
from sys.v_$TEMP_EXTENT_MAP
-
where file_id = 1
-
and block_id >= nvl((Select block_id + (bytes / 4096)
-
from sys.v_$TEMP_EXTENT_MAP
-
where block_id = (Select max(block_id)
-
from sys.v_$TEMP_EXTENT_MAP
-
where file_id = 1
-
and owner <> 0)
- and file_id = 1), 0);