oracle 常用系统查询

最新推荐文章于 2024-06-11 17:25:26 发布

wasphuang

最新推荐文章于 2024-06-11 17:25:26 发布

阅读量79

点赞数

分类专栏：数据库文章标签： Oracle F#

数据库专栏收录该内容

22 篇文章 0 订阅

订阅专栏

查索引

DBA_INDEXES 或 ALL_INDEXES 或 USER_INDEXES

系统统计信息

v$sysstat

等待信息

v$session_wait

v$system_event

Oracle 标准的表空间使用情况查询：

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'