Oracle数据库巡检(五)
查Oracle表空间使用情况
/* OracleOEM */
SELECT d.status "Status",
d.tablespace_name "Name",
d.contents "Type",
d.extent_management "Extent Management",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99,999,990.900') "Size (M)",
TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024, '99999999.999') || '/' ||
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99999999.999') "Used (M)",
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %"
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')
UNION ALL
SELECT d.status "Status",
d.tablespace_name "Name",
d.contents "Type",
d.extent_management "Extent Management",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99,999,990.900') "Size (M)",
TO_CHAR(NVL(t.bytes, 0) / 1024 / 1024, '99999999.999') || '/' ||
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99999999.999') "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
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'
/
Status Name Type Extent Man Size (M) Used (M) Used %
--------- --------------- --------- ---------- --------------- --------------------------- -------
ONLINE SYSAUX PERMANENT LOCAL 550.000 511.688/ 550.000 93.03
ONLINE UNDOTBS1 UNDO LOCAL 75.000 42.500/ 75.000 56.67
ONLINE USERS PERMANENT LOCAL 5.000 1.438/ 5.000 28.75
ONLINE SYSTEM PERMANENT LOCAL 680.000 677.000/ 680.000 99.56
ONLINE TEMP TEMPORARY LOCAL 29.000 28.000/ 29.000 96.55
Oracle数据库的数据是存放在表空间里，如果表空间剩余空间不足，数据库无法继续写入数据，数据库将报错。因此，及时检查表空间使用情况，确保表空间剩余空间维持在20%以上，对数据库的正常稳定运行来说具有重要意义。
检查Oracle扩展异常对象
SYS@ orcl> select segment_name, segment_type, tablespace_name, (extents/max_extents)*100 percent from dba_segments where max_extents !=0 and (extents/max_extents)*100 >=90 order by percent;
no rows selected
数据库中每个segment是由extent组成，而每个segment所能容纳的extent数量是有限制的，dba_segments中的max_extents列就是每个segment所能容纳的最大extent数量。如果segment中的extent数达到了这个数量的限制，则segment将无法继续扩展，数据库将报错。因此，通过检查每个segment中的extent数量，可以及时发现数据库中扩展异常的对象，以便于采取进一步参数，避免出现segment无法扩展的问题出现。（无返回值，状态正常）。
检查Oracle系统表空间
select distinct(owner) from dba_tables where tablespace_name = 'SYSTEM' and owner != 'SYS' and owner != 'SYSTEM'
union all
select distinct(owner) from dba_indexes where tablespace_name = 'SYSTEM' and owner != 'SYS' and owner != 'SYSTEM'