表空间增长

SELECT tsname,
alloc_gb,
used_gb,
free_gb,
pct_used,
last_week_gb,
last_week_max_gb,
yesterday_gb,
yesterday_max_gb,
LEAST(days_left, 999) days_left,
CASE
WHEN days_left <= 20 THEN
‘<<’
ELSE
NULL
END flag
FROM (SELECT curr.tsname,
round( curr.alloc / (1024 1024 1024) ,2) alloc_gb,
round( curr.used / (1024 1024 1024) ,2) used_gb,
round( curr.free / (1024 1024 1024) ,2) free_gb,
round( curr.pct_used,2) pct_used,
round( last_week.growth / (1024 10241024) ,2) last_week_gb,
round( last_week_max.growth / (1024 1024 1024) ,2) last_week_max_gb,
round( yesterday.growth / (1024 10241024) ,2) yesterday_gb,
round( yesterday_max.used / (1024 1024 1024) ,2) yesterday_max_gb,
CASE
WHEN yesterday.growth > 0 THEN
round(curr.free / yesterday.growth,2)
ELSE
999
END days_left
FROM (SELECT df.tablespace_name tsname,
df.alloc,
df.alloc - NVL(fs.free, 0) used,
NVL(fs.free, 0) free,
((df.alloc - NVL(fs.free, 0)) / df.alloc) * 100 pct_used
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) fs,
(SELECT tablespace_name, SUM(bytes) alloc
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name = df.tablespace_name) curr,
(SELECT tsname, SUM(growth) growth
FROM (SELECT s.snap_id,
s.instance_number,
s.dbid,
ti.tsname,
NVL(NVL(su.tablespace_usedsize * ti.block_size,
0) - LAG(NVL(su.tablespace_usedsize *
ti.block_size,
0),
1)
OVER(PARTITION BY ti.tsname,
su.dbid ORDER BY su.snap_id),
0) growth
FROM dba_hist_snapshot s,
dba_hist_tbspc_space_usage su,
(SELECT dbid,
ts#,
tsname,
MAX(block_size) block_size
FROM dba_hist_datafile
GROUP BY dbid, ts#, tsname) ti
WHERE s.dbid = su.dbid
AND s.snap_id = su.snap_id
AND su.dbid = ti.dbid
AND su.tablespace_id = ti.ts#
AND s.begin_interval_time >= TRUNC(SYSDATE - 8)
AND s.begin_interval_time < TRUNC(SYSDATE - 1)
AND su.dbid = (SELECT dbid FROM vdatabase) AND s.instance_number = (SELECT instance_number FROM vdatabase)ANDs.instancen​umber=(SELECTinstancen​umberFROMvinstance))
GROUP BY tsname) last_week,
(SELECT tsname, MAX(growth) growth
FROM (SELECT TRUNC(begin_interval_time, ‘DD’) begin_interval_time,
tsname,
SUM(growth) growth
FROM (SELECT s.snap_id,
s.instance_number,
s.dbid,
s.begin_interval_time,
ti.tsname,
NVL(NVL(su.tablespace_usedsize *
ti.block_size,
0) - LAG(NVL(su.tablespace_usedsize *
ti.block_size,
0),
1)
OVER(PARTITION BY ti.tsname,
su.dbid ORDER BY su.snap_id),
0) growth
FROM dba_hist_snapshot s,
dba_hist_tbspc_space_usage su,
(SELECT dbid,
ts#,
tsname,
MAX(block_size) block_size
FROM dba_hist_datafile
GROUP BY dbid, ts#, tsname) ti
WHERE s.dbid = su.dbid
AND s.snap_id = su.snap_id
AND su.dbid = ti.dbid
AND su.tablespace_id = ti.ts#
AND s.begin_interval_time >=
TRUNC(SYSDATE - 8)
AND s.begin_interval_time <
TRUNC(SYSDATE - 1)
AND su.dbid = (SELECT dbid FROM vdatabase) AND s.instance_number = (SELECT instance_number FROM vdatabase)ANDs.instancen​umber=(SELECTinstancen​umberFROMvinstance))
GROUP BY TRUNC(begin_interval_time, ‘DD’), tsname)
GROUP BY tsname) last_week_max,
(SELECT tsname, SUM(growth) growth
FROM (SELECT s.snap_id,
s.instance_number,
s.dbid,
ti.tsname,
NVL(NVL(su.tablespace_usedsize * ti.block_size,
0) - LAG(NVL(su.tablespace_usedsize *
ti.block_size,
0),
1)
OVER(PARTITION BY ti.tsname,
su.dbid ORDER BY su.snap_id),
0) growth
FROM dba_hist_snapshot s,
dba_hist_tbspc_space_usage su,
(SELECT dbid,
ts#,
tsname,
MAX(block_size) block_size
FROM dba_hist_datafile
GROUP BY dbid, ts#, tsname) ti
WHERE s.dbid = su.dbid
AND s.snap_id = su.snap_id
AND su.dbid = ti.dbid
AND su.tablespace_id = ti.ts#
AND s.begin_interval_time >= TRUNC(SYSDATE - 1)
AND s.begin_interval_time < TRUNC(SYSDATE)
AND su.dbid = (SELECT dbid FROM vdatabase) AND s.instance_number = (SELECT instance_number FROM vdatabase)ANDs.instancen​umber=(SELECTinstancen​umberFROMvinstance))
GROUP BY tsname) yesterday,
(SELECT TRUNC(s.begin_interval_time, ‘DD’) begin_interval_time,
ti.tsname,
MAX(su.tablespace_usedsize * ti.block_size) used
FROM dba_hist_snapshot s,
(SELECT dbid, ts#, tsname, MAX(block_size) block_size
FROM dba_hist_datafile
GROUP BY dbid, ts#, tsname) ti,
dba_hist_tbspc_space_usage su
WHERE s.dbid = su.dbid
AND s.snap_id = su.snap_id
AND su.dbid = ti.dbid
AND su.tablespace_id = ti.ts#
AND s.begin_interval_time >= TRUNC(SYSDATE - 1)
AND s.begin_interval_time < TRUNC(SYSDATE)
AND su.dbid = (SELECT dbid FROM vdatabase) AND s.instance_number = (SELECT instance_number FROM vdatabase)ANDs.instancen​umber=(SELECTinstancen​umberFROMvinstance))
GROUP BY TRUNC(s.begin_interval_time, ‘DD’), ti.tsname) yesterday_max
WHERE curr.tsname = last_week.tsname
AND curr.tsname = yesterday.tsname
AND curr.tsname = last_week_max.tsname
AND curr.tsname = yesterday_max.tsname
AND curr.tsname NOT LIKE ‘UNDO%’)
ORDER BY days_left ASC;

select c.tablespace_name,
c.contents,
c.extent_management ext_mgmt,
c.allocation_type alloc_type,
c.initial_extent / 1024 ext_kb,
a.total_mb,
b.free_mb,
to_char(100 * b.free_mb / a.total_mb, ‘999.99’) free_pct
from (select tablespace_name,
to_char(sum(bytes) / 1048576, ‘99999999.99’) total_mb
from dba_data_files
group by tablespace_name) a,
(select tablespace_name,
to_char(sum(bytes) / 1048576, ‘99999999.99’) free_mb,
count(*) free_exts,
to_char(max(bytes) / 1048576, ‘99999999’) max_mb,
to_char(min(bytes) / 1048576, ‘99999999’) min_mb
from dba_free_space
group by tablespace_name) b,
dba_tablespaces c
where a.tablespace_name = b.tablespace_name(+)
and c.tablespace_name = b.tablespace_name
order by free_pct, tablespace_name;

表空间使用率大于90%

sELECT d.tablespace_name “Name”,
NVL(a.bytes / 1024 / 1024, 0) “Size (M)”,
NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024 “Used (M)”,
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), ‘990.00’) “Used %”,
NVL(a.bytes / 1024 / 1024, 0) - NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024 “Free (M)”,
d.contents “Type”,
d.extent_management “Extent Management”
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes
from dba_free_space
group by tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT
(d.extent_management like ‘LOCAL’ AND d.contents like ‘TEMPORARY’)
AND TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), ‘990.00’) > TO_CHAR(90,‘990.00’)
UNION ALL
SELECT d.tablespace_name “Name”,
NVL(a.bytes / 1024 / 1024, 0) “Size (M)”,
NVL(t.bytes, 0) / 1024 / 1024 “Used (M)”,
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), ‘990.00’) “Used %”,
NVL(a.bytes / 1024 / 1024, 0) - NVL(t.bytes, 0) / 1024 / 1024 “Free (M)”,
d.contents “Type”,
d.extent_management “Extent Management”
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes
from dba_temp_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes
from v$temp_extent_pool
group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like ‘LOCAL’
AND d.contents like ‘TEMPORARY’
AND TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), ‘990.00’) > TO_CHAR(90,‘990.00’)
order by 4 desc;

检查表空间利用率

select a.tablespace_name,
a.used_space 8192 / (1024 1024) used_space_mb,
a.tablespace_size 8192 / (1024 1024) tablespace_size_mb,
round(a.used_percent, 2) “used%”
from dba_tablespace_usage_metrics a
where a.tablespace_name not in
(select distinct tablespace_name from dba_undo_extents);

最后修改：2021 年 10 月 02 日
© 允许规范转载
如果觉得我的文章对你有用，请随意赞赏