點選(此處)摺疊或開啟

--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);

oracle實用sql(6)--tablespace/datafile resize

相關文章