oracle数据库状态查询

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;