🎉 1.7 Oracle系统表空间用户检查

Oracle 表空间与用户的检查是数据库日常巡检的重要环节，主要关注用户的默认表空间、临时表空间、配额限制以及​各用户占用空间的情况。下面提供一组实用的 SQL 查询，帮助你快速了解表空间与用户的关系，并发现潜在问题。

一、查看所有用户及其默认表空间、临时表空间

SELECT username,
       default_tablespace,
       temporary_tablespace,
       account_status,
       created
 FROM dba_users
 ORDER BY default_tablespace, username;

SELECT username,
       default_tablespace,
       temporary_tablespace,
 FROM dba_users
 ORDER BY default_tablespace, username;

说明：

• default_tablespace：用户创建对象时默认使用的表空间。
• temporary_tablespace：用户执行排序等操作时使用的临时表空间。
• 若 default_tablespace​ 为 SYSTEM​ 或 USERS，需评估是否为生产库的最佳实践（通常建议为业务表空间）。
• 检查是否存在 ACCOUNT_STATUS​ 为 OPEN 但长期未使用的用户，考虑锁定或删除。

二、查看用户在表空间上的配额（空间限制）

SELECT tablespace_name,
       username,
       bytes / 1024 / 1024 AS quota_mb,
       max_bytes / 1024 / 1024 AS max_mb
FROM dba_ts_quotas
ORDER BY tablespace_name, username;

说明：

• max_bytes​ 为 -1 表示无限制（UNLIMITED）。
• 若 max_bytes​ 有限且接近或等于 bytes，说明该用户在该表空间上已用完配额，将无法再创建新对象或插入数据。
• 无配额记录的用户，其权限由 UNLIMITED TABLESPACE 系统权限决定。

三、检查哪些用户拥有 UNLIMITED TABLESPACE 系统权限

SELECT grantee, privilege, admin_option
FROM dba_sys_privs
WHERE privilege = 'UNLIMITED TABLESPACE'
ORDER BY grantee;

拥有此权限的用户在所有表空间上均不受配额限制，需评估是否合理（通常只应授予 DBA 或特定应用账户）。

四、按表空间统计用户占用空间大小（数据段）

SELECT owner,
       tablespace_name,
       ROUND(SUM(bytes) / 1024 / 1024, 2) AS used_mb
FROM dba_segments
WHERE owner NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','XDB','APEX_%','ORACLE_OCM')
GROUP BY owner, tablespace_name
ORDER BY tablespace_name, used_mb DESC;

说明：

• 统计每个用户在各表空间上实际占用的空间（包括表、索引、LOB等）。
• 排除系统用户（可根据实际环境调整过滤条件），便于定位业务用户的空间使用。
• 可与 dba_ts_quotas 对比，检查是否超过配额（若无配额限制则无问题）。

五、检查表空间总体使用情况（结合用户分布）

SELECT tablespace_name,
       ROUND(SUM(bytes) / 1024 / 1024, 2) AS total_mb,
       ROUND(SUM(CASE WHEN owner NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN','XDB') THEN bytes ELSE 0 END) / 1024 / 1024, 2) AS user_used_mb,
       ROUND(SUM(CASE WHEN owner IN ('SYS','SYSTEM','DBSNMP','OUTLN','XDB') THEN bytes ELSE 0 END) / 1024 / 1024, 2) AS sys_used_mb
FROM dba_segments
GROUP BY tablespace_name
ORDER BY tablespace_name;

可以快速区分表空间中系统对象和用户对象占用的比例，辅助空间规划。

六、检查未设置默认表空间的用户（DEFAULT_TABLESPACE​ 为 SYSTEM）

SELECT username, default_tablespace, temporary_tablespace
FROM dba_users
WHERE default_tablespace = 'SYSTEM'
  AND username NOT IN ('SYS','SYSTEM','DBSNMP','OUTLN');

生产库应避免将业务用户默认表空间设为 SYSTEM，否则会增加系统表空间维护负担并引发权限问题。

七、检查临时表空间配置

1. 查看所有用户的临时表空间分布

SELECT temporary_tablespace, COUNT(*) user_count
FROM dba_users
GROUP BY temporary_tablespace;

2. 检查临时表空间的大小与使用情况

SELECT tablespace_name,
       ROUND(bytes / 1024 / 1024, 2) AS size_mb,
       autoextensible,
       ROUND(maxbytes / 1024 / 1024, 2) AS max_mb
FROM dba_temp_files;

如果多个用户共享同一个临时表空间且存在大量排序操作，需监控临时表空间的使用率，避免空间不足。

八、综合巡检脚本示例

可将以上查询整合成一个脚本，按顺序输出关键信息。例如：

-- 1. 用户与默认表空间
SELECT '=== Users and Default Tablespaces ===' AS info FROM dual;
SELECT username, default_tablespace, temporary_tablespace, account_status
FROM dba_users WHERE username NOT IN ('SYS','SYSTEM') ORDER BY username;

-- 2. 配额信息
SELECT '=== Tablespace Quotas ===' AS info FROM dual;
SELECT tablespace_name, username, ROUND(bytes/1024/1024,2) quota_mb,
       DECODE(max_bytes, -1, 'UNLIMITED', ROUND(max_bytes/1024/1024,2)) max_mb
FROM dba_ts_quotas ORDER BY tablespace_name, username;

-- 3. UNLIMITED TABLESPACE 权限
SELECT '=== Users with UNLIMITED TABLESPACE ===' AS info FROM dual;
SELECT grantee FROM dba_sys_privs WHERE privilege = 'UNLIMITED TABLESPACE' ORDER BY grantee;

-- 4. 用户空间占用（Top 10）
SELECT '=== Top 10 Users by Space Usage ===' AS info FROM dual;
SELECT * FROM (
  SELECT owner, tablespace_name, ROUND(SUM(bytes)/1024/1024,2) used_mb
  FROM dba_segments
  GROUP BY owner, tablespace_name
  ORDER BY used_mb DESC
) WHERE ROWNUM <= 10;

-- 5. 临时表空间配置
SELECT '=== Temp Tablespace Info ===' AS info FROM dual;
SELECT tablespace_name, ROUND(bytes/1024/1024,2) size_mb, autoextensible
FROM dba_temp_files;

九、常见问题与处理建议

通过以上检查，可以全面掌握表空间与用户的关系，及时发现配置不合理或空间瓶颈，保障数据库的稳定运行。建议将上述查询定期执行，并将结果纳入日常巡检报告。