Oracle表空间使用情况
1.查看Oracle表空间及临时表空间使用情况
2.查看Oracle表空间使用情况
3.查看Oracle临时表空间使用情况
1.查看表空间及临时表空间
select t.*
from (SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
FREE_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL
SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",
ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",
SPACE - USED_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE,
ROUND(SUM(BLOCKS * 8192) / (1024 * 1024), 2) USED_SPACE
FROM V$SORT_USAGE
GROUP BY TABLESPACE) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE(+)) t
order by "USED_RATE(%)" desc;
SELECT /*+ first_rows */
d.tablespace_name "TS NAME",
NVL(a.bytes / 1024 / 1024, 0) "size MB",
NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024 "Used MB",
NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0) "Used %",
a.autoext "Autoextend",
NVL(f.bytes, 0) / 1024 / 1024 "Free MB",
d.status "STAT",
a.count "# of datafiles",
d.contents "TS type",
d.extent_management "EXT MGMT",
d.segment_space_management "Seg Space MGMT"
FROM sys.dba_tablespaces d,
(select tablespace_name,
sum(bytes) bytes,
count(file_id) count,
decode(sum(decode(autoextensible, 'NO', 0, 1)),
0,
'NO',
'YES') autoext
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.contents = 'UNDO'
AND NOT (d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY')
AND d.tablespace_name like '%%'
UNION ALL
SELECT d.tablespace_name,
NVL(a.bytes / 1024 / 1024, 0),
NVL(t.bytes, 0) / 1024 / 1024,
NVL(t.bytes / a.bytes * 100, 0),
a.autoext,
(NVL(a.bytes, 0) / 1024 / 1024 - NVL(t.bytes, 0) / 1024 / 1024),
d.status,
a.count,
d.contents,
d.extent_management,
d.segment_space_management
FROM sys.dba_tablespaces d,
(select tablespace_name,
sum(bytes) bytes,
count(file_id) count,
decode(sum(decode(autoextensible, 'NO', 0, 1)),
0,
'NO',
'YES') autoext
from dba_temp_files
group by tablespace_name) a,
(select ss.tablespace_name,
sum((ss.used_blocks * ts.blocksize)) bytes
from gv$sort_segment ss, sys.ts$ ts
where ss.tablespace_name = ts.name
group by ss.tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management = 'LOCAL'
AND d.contents = 'TEMPORARY'
and d.tablespace_name like '%%'
UNION ALL
SELECT d.tablespace_name,
NVL(a.bytes / 1024 / 1024, 0),
NVL(u.bytes, 0) / 1024 / 1024,
NVL(u.bytes / a.bytes * 100, 0),
a.autoext,
NVL(a.bytes - NVL(u.bytes, 0), 0) / 1024 / 1024,
d.status,
a.count,
d.contents,
d.extent_management,
d.segment_space_management
FROM sys.dba_tablespaces d,
(SELECT tablespace_name,
SUM(bytes) bytes,
COUNT(file_id) count,
decode(sum(decode(autoextensible, 'NO', 0, 1)),
0,
'NO',
'YES') autoext
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes
FROM (SELECT tablespace_name, sum(bytes) bytes, status
from dba_undo_extents
WHERE status = 'ACTIVE'
group by tablespace_name, status
UNION ALL
SELECT tablespace_name, sum(bytes) bytes, status
from dba_undo_extents
WHERE status = 'UNEXPIRED'
group by tablespace_name, status)
group by tablespace_name) u
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = u.tablespace_name(+)
AND d.contents = 'UNDO'
AND d.tablespace_name LIKE '%%'
ORDER BY 1;
2.查看表空间
该脚本计算自动扩展表空间,未使用过的表空间，该脚本查不出
set linesize 500
set pages 500;
col tablespace_name for a30
col USED_PCT for a15
select sysdate,
a.tablespace_name,
round(total_mb, 1) TOTAL_MB,
round(used_mb, 1) USED_MB,
round(used_mb / total_mb, 3) * 100 || '%' USED_PCT
from (select (case
when length(tablespace_name || '1') <= 1 then
'TOTAL'
ELSE
tablespace_name
end) tablespace_name,
used_mb
from (select tablespace_name, sum(bytes) / 1024 / 1024 used_mb
from dba_segments
group by rollup(tablespace_name))) a,
(select (case
when length(tablespace_name || '1') <= 1 then
'TOTAL'
ELSE
tablespace_name
end) tablespace_name,
SUM(case autoextensible
when 'YES' THEN
CASE
WHEN maxbytes > bytes THEN
maxbytes
ELSE
bytes
END
ELSE
bytes
END) / 1024 / 1024 total_mb
from dba_data_files
group by rollup(tablespace_name)) b
where a.tablespace_name = b.tablespace_name
order by 4, used_mb / total_mb;
3.查看临时表空间
set linesize 200 pagesize 200
SELECT D.tablespace_name,
SPACE "SUM_SPACE(M)",
blocks "SUM_BLOCKS",
used_space "USED_SPACE(M)",
Round(Nvl(used_space, 0) / SPACE * 100, 2) "USED_RATE(%)",
SPACE - used_space "FREE_SPACE(M)"
FROM (SELECT tablespace_name,
Round(SUM(bytes) / (1024 * 1024), 2) SPACE,
SUM(blocks) BLOCKS
FROM dba_temp_files
GROUP BY tablespace_name) D,
(SELECT tablespace,
Round(SUM(blocks * 8192) / (1024 * 1024), 2) USED_SPACE
FROM v$sort_usage
GROUP BY tablespace) F
WHERE D.tablespace_name = F.tablespace(+);
该脚本计算自动扩展临时表空间:
set linesize 200 pagesize 200
SELECT D.tablespace_name,
SPACE "SUM_SPACE(M)",
Allocate "Allocate_SPACE(M)",
blocks "SUM_BLOCKS",
used_space "USED_SPACE(M)",
Round(Nvl(used_space, 0) / SPACE * 100, 2) "USED_RATE(%)",
SPACE - used_space "FREE_SPACE(M)"
FROM (SELECT tablespace_name,
Round(SUM(decode(autoextensible, 'YES', MAXBYTES, BYTES)) / (1024 * 1024), 2) SPACE,
Round(SUM(bytes) / (1024 * 1024), 2) Allocate,
SUM(blocks) BLOCKS
FROM dba_temp_files
GROUP BY tablespace_name) D,
(SELECT tablespace,
Round(SUM(blocks * 8192) / (1024 * 1024), 2) USED_SPACE
FROM v$sort_usage
GROUP BY tablespace) F
WHERE D.tablespace_name = F.tablespace(+);