Oracle 19c遗留数据库活跃用户与表识别方案问询

1. 识别数据库使用者及其使用用途 #

方案1：从数据字典基础权限与对象推断 #

涉及数据字典表：DBA_USERS、DBA_OBJECTS、DBA_TAB_PRIVS、DBA_ROLE_PRIVS

• 通过DBA_USERS查看用户创建时间、默认表空间，初步区分业务用户/运维用户等类型
• 通过DBA_OBJECTS统计用户拥有的对象类型（表、存储过程、视图等），推断其核心操作方向
• 通过DBA_TAB_PRIVS和DBA_ROLE_PRIVS查看用户被授予的权限/角色，明确其操作范围

SQL示例：

-- 统计用户拥有的对象类型及数量
SELECT u.username, o.object_type, COUNT(*) AS object_count
FROM dba_users u
LEFT JOIN dba_objects o ON u.username = o.owner
GROUP BY u.username, o.object_type
ORDER BY u.username, object_count DESC;

-- 查看用户的核心对象权限
SELECT grantee, privilege, table_name
FROM dba_tab_privs
WHERE grantee IN (SELECT username FROM dba_users)
ORDER BY grantee, privilege;

方案2：结合会话与SQL历史分析 #

涉及数据字典表：V$SESSION、V$SQL、V$SQLAREA

• 实时查询当前活跃会话的执行SQL，直接判断用户正在进行的操作
• 从V$SQLAREA中检索用户近期执行的SQL语句，分析业务逻辑（如订单查询、数据批量插入等）

SQL示例：

-- 查看当前活跃用户的执行SQL
SELECT s.username, s.program, sql.sql_text
FROM v$session s
LEFT JOIN v$sql sql ON s.sql_id = sql.sql_id
WHERE s.username IS NOT NULL
AND s.status = 'ACTIVE';

-- 统计用户最近一周执行的SQL频次
SELECT username, sql_text, COUNT(*) AS exec_count
FROM v$sqlarea
WHERE last_active_time >= SYSDATE - 7
AND username IS NOT NULL
GROUP BY username, sql_text
ORDER BY exec_count DESC;

方案3：审计日志追踪（若已开启审计） #

涉及数据字典表：DBA_AUDIT_TRAIL、DBA_AUDIT_OBJECT

• 若数据库开启了对象访问或会话审计，可通过审计日志完整追溯用户的所有操作，精准判断用途

SQL示例：

-- 查看用户的审计操作记录
SELECT username, action_name, obj_name, timestamp
FROM dba_audit_trail
WHERE username IS NOT NULL
ORDER BY timestamp DESC;

2. 识别高频/活跃连接用户 #

方案1：基于实时与近期会话统计 #

涉及数据字典表：V$SESSION、V$SESSION_HISTORY

• 统计当前活跃会话数量，以及最近1小时内的会话连接频次

SQL示例：

-- 统计当前活跃用户会话数
SELECT username, COUNT(*) AS active_sessions
FROM v$session
WHERE username IS NOT NULL
AND status = 'ACTIVE'
GROUP BY username
ORDER BY active_sessions DESC;

-- 统计最近1小时内的用户连接频次
SELECT username, COUNT(DISTINCT session_id) AS session_count
FROM v$session_history
WHERE sample_time >= SYSDATE - 1/24
GROUP BY username
ORDER BY session_count DESC;

方案2：基于AWR历史会话数据 #

涉及数据字典表：DBA_HIST_ACTIVE_SESS_HISTORY、DBA_HIST_SESS_SUMMARY

• 利用AWR的历史会话统计，分析长期（如7天）内的用户活跃情况，适合判断高频用户

SQL示例：

-- 统计7天内用户的活跃会话次数
SELECT user_name, COUNT(*) AS active_session_count
FROM dba_hist_active_sess_history
WHERE sample_time >= SYSDATE - 7
GROUP BY user_name
ORDER BY active_session_count DESC;

方案3：基于资源使用统计 #

涉及数据字典表：V$SESSTAT、V$STATNAME

• 通过用户消耗的CPU、IO等资源量，间接判断活跃程度

SQL示例：

-- 统计用户CPU资源消耗
SELECT s.username, SUM(ss.value) AS cpu_usage
FROM v$sesstat ss
JOIN v$statname sn ON ss.statistic# = sn.statistic#
JOIN v$session s ON ss.sid = s.sid
WHERE sn.name = 'CPU used by this session'
AND s.username IS NOT NULL
GROUP BY s.username
ORDER BY cpu_usage DESC;

3. 识别访问频率最高的表 #

方案1：基于实时SQL与会话分析 #

涉及数据字典表：V$SQL、DBA_TABLES

• 从近期执行的SQL中提取访问的表，统计访问频次

SQL示例：

-- 统计1天内被访问的表及频次
SELECT obj_name AS table_name, COUNT(*) AS access_count
FROM (
    SELECT regexp_substr(sql_text, 'FROM\s+(\w+\.)?(\w+)', 1, 1, 'i', 2) AS obj_name
    FROM v$sql
    WHERE last_active_time >= SYSDATE - 1
    AND sql_text LIKE '%FROM%'
)
WHERE obj_name IN (SELECT table_name FROM dba_tables)
GROUP BY obj_name
ORDER BY access_count DESC;

方案2：基于AWR历史SQL统计 #

涉及数据字典表：DBA_HIST_SQL_PLAN、DBA_HIST_SQLSTAT

• 利用AWR的历史执行计划数据，统计长期内表的访问次数

SQL示例：

-- 统计7天内表的访问频次
SELECT object_name AS table_name, SUM(executions) AS total_executions
FROM dba_hist_sql_plan p
JOIN dba_hist_sqlstat s ON p.sql_id = s.sql_id
WHERE p.operation = 'TABLE ACCESS'
AND s.sample_time >= SYSDATE - 7
GROUP BY object_name
ORDER BY total_executions DESC;

方案3：基于段访问统计 #

涉及数据字典表：V$SEGMENT_STATISTICS

• 通过Oracle的段级统计，直接获取表的读写次数

SQL示例：

-- 统计表的读写访问次数
SELECT owner, object_name AS table_name,
       SUM(CASE WHEN statistic_name = 'logical reads' THEN value END) AS logical_reads,
       SUM(CASE WHEN statistic_name = 'physical reads' THEN value END) AS physical_reads,
       SUM(CASE WHEN statistic_name = 'table scans' THEN value END) AS table_scans
FROM v$segment_statistics
WHERE object_type = 'TABLE'
GROUP BY owner, object_name
ORDER BY logical_reads DESC;

4. 识别未使用的表 #

方案1：基于AWR历史数据判断 #

涉及数据字典表：DBA_HIST_SQL_PLAN、DBA_TABLES

• 检查AWR历史中是否有访问记录，若长期（如30天）无访问则标记为未使用

SQL示例：

-- 查找30天内无AWR访问记录的表
SELECT owner, table_name
FROM dba_tables t
WHERE NOT EXISTS (
    SELECT 1
    FROM dba_hist_sql_plan p
    WHERE p.object_name = t.table_name
    AND p.owner = t.owner
    AND p.sample_time >= SYSDATE - 30
)
ORDER BY owner, table_name;

方案2：基于段统计信息判断 #

涉及数据字典表：V$SEGMENT_STATISTICS

• 查看表的读写统计值，若所有访问类统计均为0，则判定为未使用（需确认统计功能已开启）

SQL示例：

-- 查找无任何访问统计的表
SELECT owner, object_name AS table_name
FROM v$segment_statistics
WHERE object_type = 'TABLE'
AND statistic_name IN ('logical reads', 'physical reads', 'table scans', 'row lock waits')
GROUP BY owner, object_name
HAVING SUM(value) = 0
ORDER BY owner, table_name;

方案3：基于审计日志判断（若已开启） #

涉及数据字典表：DBA_AUDIT_OBJECT

• 若开启了对象访问审计，可直接查询是否有表的访问记录

SQL示例：

-- 查找无审计访问记录的表
SELECT owner, table_name
FROM dba_tables t
WHERE NOT EXISTS (
    SELECT 1
    FROM dba_audit_object a
    WHERE a.obj_name = t.table_name
    AND a.owner = t.owner
)
ORDER BY owner, table_name;

方案4：基于最后分析时间辅助判断 #

涉及数据字典表：DBA_TABLES

• 若表的LAST_ANALYZED时间极早甚至为NULL，结合其他方案可辅助判断是否未使用（注意：分析时间不代表实际访问，仅作参考）

SQL示例：

-- 查找半年未分析的表
SELECT owner, table_name, last_analyzed
FROM dba_tables
WHERE last_analyzed < SYSDATE - 180
OR last_analyzed IS NULL
ORDER BY last_analyzed NULLS FIRST;

内容的提问来源于stack exchange，提问作者Raghavendra