oracle数据库检测

连接数

-- 数据库连接数
select count(*) from v$process;
-- 数据库允许的最大连接数
select value from v$parameter where name ='processes';
-- session连接数
select count(*) from v$session;
-- 并发连接数
select count(*) from v$session where status='ACTIVE';

表空间占比

-- 表空间占比
SELECT
    a.tablespace_name,
    total / 1024            total,
    free / 1024             free,
    ( total - free ) / 1024 AS used,
    substr(
        free / total * 100, 1, 5
    )                       AS "FREE%",
    substr(
        (total - free) / total * 100, 1, 5
    )                       AS "USED%",
    a.autoextensible
FROM
    (
        SELECT
            tablespace_name,
            autoextensible,
            SUM(bytes) / 1024 / 1024 AS total
        FROM
            dba_data_files
        GROUP BY
            tablespace_name,
            autoextensible
    ) a,
    (
        SELECT
            tablespace_name,
            SUM(bytes) / 1024 / 1024 AS free
        FROM
            dba_free_space
        GROUP BY
            tablespace_name
    ) b
WHERE
    a.tablespace_name = b.tablespace_name
ORDER BY
    a.tablespace_name

归档日志

-- 归档日志路径
SELECT * FROM V$ARCHIVED_LOG;
-- 归档日志大小
SELECT
    SUM(blocks * block_size) / 1024 / 1024 / 1024 AS "ARCHIVE LOG SIZE (GB)"
FROM
    v$archived_log;

日志文件

-- 日志文件大小
SELECT
    l.group#,
    l.thread#,
    l.sequence#,
    l.bytes / 1024 / 1024 / 1024,
    f.member
FROM
    v$log l
    JOIN v$logfile f ON l.group# = f.group#;

数据表占比

SELECT
    t.segment_name             "表名",
--    t.segment_type             "对象类型",
    COUNT(*)                  "总数量",
    SUM(t.bytes) / 1024 / 1024/1024 "占用空间（GB）" 
FROM
    dba_extents t
WHERE
    1 = 1
    AND t.segment_type = 'TABLE'
   -- AND t.segment_name = '表名'
GROUP BY
    t.segment_name,
    t.segment_type
ORDER BY
    "占用空间（GB）" DESC;