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 a.segment_name "表名",
       a.bytes / 1024 "占用空间（GB）",
       b.num_rows "总数量"
  from (SELECT owner,
               segment_name,
               segment_type,
               SUM(bytes) / 1024 / 1024 bytes
          FROM dba_extents
         WHERE 1 = 1
           AND segment_type = 'TABLE'
           AND owner = '***'
         GROUP BY owner, segment_name, segment_type) a,
       (SELECT table_name,
               num_rows,
               ROW_NUMBER() OVER(PARTITION BY table_name ORDER BY num_rows DESC) AS rn
          FROM all_tables
         WHERE owner = '***') b

 where 1 = 1
   and a.segment_name = b.table_name(+)
 order by a.bytes desc;