oracle数据库状态查询(表空间,表空间利用率,是否自动扩展,IOPS,MBPS,数据库总大小)
排除了('SYSAUX', 'USERS', 'SYSTEM','UNDOTBS1')
结果示例
-- 查询用户自定义表空间的使用信息
WITH user_tablespaces AS (
SELECT DISTINCT tablespace_name
FROM dba_segments
WHERE tablespace_name NOT IN ('SYSAUX', 'USERS', 'SYSTEM','UNDOTBS1')
),
tbs_used_size AS (
SELECT TRUNC(SUM(bytes) / 1024 / 1024 / 1024, 2) AS used_size_gb, tablespace_name
FROM dba_segments
WHERE tablespace_name IN (SELECT tablespace_name FROM user_tablespaces)
GROUP BY tablespace_name
),
tbs_size AS (
SELECT tablespace_name AS name,
TRUNC(SUM(bytes) / 1024 / 1024 / 1024, 2) AS total_size_gb
FROM dba_data_files
WHERE tablespace_name IN (SELECT tablespace_name FROM user_tablespaces)
GROUP BY tablespace_name
),
tbs_autoextensible AS (
SELECT tablespace_name AS name,
MAX(autoextensible) AS auto_extensible
FROM dba_data_files
WHERE tablespace_name IN (SELECT tablespace_name FROM user_tablespaces)
GROUP BY tablespace_name
)
SELECT a.name "表空间名",
a.total_size_gb || ' GB' "表空间大小(GB)",
NVL(b.used_size_gb, 0) || ' GB' "表空间使用的大小(GB)",
TRUNC(NVL(b.used_size_gb, 0) / a.total_size_gb * 100, 2) || '%' "表空间使用率(%)",
a.name || ':' || TRUNC(NVL(b.used_size_gb, 0) / a.total_size_gb * 100, 2) || '% ' ||
CASE WHEN c.auto_extensible = 'YES' THEN '自动扩展' ELSE '非自动扩展' END AS summary
FROM tbs_size a
LEFT JOIN tbs_used_size b ON (a.name = b.tablespace_name)
LEFT JOIN tbs_autoextensible c ON (a.name = c.name);
-- 查询 IOPS 和 MBPS 以及数据库总大小
WITH io_stats AS (
SELECT
(SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'physical reads') AS physical_reads,
(SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'physical writes') AS physical_writes,
(SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'physical read total bytes') AS read_bytes,
(SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'physical write total bytes') AS write_bytes
FROM dual
),
time_stats AS (
SELECT
(SYSDATE - startup_time) * 86400 AS uptime_seconds
FROM V$INSTANCE
),
iops_mbps AS (
SELECT
ROUND((physical_reads + physical_writes) / uptime_seconds, 2) AS iops,
ROUND((read_bytes + write_bytes) / (uptime_seconds * 1024 * 1024), 2) AS mbps
FROM io_stats, time_stats
),
total_size AS (
SELECT
TRUNC(SUM(bytes) / 1024 / 1024 / 1024, 2) AS total_gb
FROM dba_data_files
)
SELECT 'IOPS' AS Metric, TO_CHAR(iops) || ' operations/sec' AS Value
FROM iops_mbps
UNION ALL
SELECT 'MBPS', TO_CHAR(mbps) || ' MB/sec'
FROM iops_mbps
UNION ALL
SELECT '数据库总大小', TO_CHAR(total_gb) || ' GB'
FROM total_size;
标签:1024,name,数据库,查询,tablespace,gb,oracle,SELECT,size
From: https://www.cnblogs.com/yogochann/p/18311963