Oracle数据库|表空间管理 Oracle查看表空间的常用SQL语句详解

📊 Oracle表空间管理指南：这些SQL让你轻松掌控数据库空间

场景引入：
凌晨3点，DBA老王被报警短信惊醒——"表空间不足，应用即将崩溃！"😱 手忙脚乱连上数据库，却突然忘记查看表空间的关键SQL...别慌！这篇指南就是你的"急救手册"，涵盖Oracle表空间管理的所有实用SQL，收藏等于保平安！✨

表空间基础概念

表空间（Tablespace）是Oracle存储数据的逻辑容器，就像仓库里的货架🏗️，一个数据库通常包含：

• 系统表空间（SYSTEM/SYSAUX）：存储数据字典等核心信息
• 用户表空间（USERS等）：存放业务数据
• 临时表空间（TEMP）：处理排序等临时操作
• 撤销表空间（UNDOTBS）：事务回滚专用

必备监控SQL（附使用场景）

1️⃣ 查看所有表空间概况

SELECT tablespace_name "表空间名", 
       status "状态",
       contents "类型",
       extent_management "管理方式",
       allocation_type "分配类型"
FROM dba_tablespaces;

输出示例：

表空间名      状态      类型      管理方式      分配类型  
SYSTEM       ONLINE    PERMANENT  LOCAL        SYSTEM  
TEMP         ONLINE    TEMPORARY  LOCAL        UNIFORM  

用途：快速了解数据库中有哪些"仓库"，哪些是临时货架🗄️

2️⃣ 实时空间使用率（含预警）

SELECT df.tablespace_name "表空间",
       df.bytes/1024/1024 "总大小(MB)",
       (df.bytes-fs.bytes)/1024/1024 "已用(MB)",
       fs.bytes/1024/1024 "空闲(MB)",
       ROUND(100*(df.bytes-fs.bytes)/df.bytes) "使用率%",
       CASE WHEN ROUND(100*(df.bytes-fs.bytes)/df.bytes) > 90 THEN '⚠️紧急'
            WHEN ROUND(100*(df.bytes-fs.bytes)/df.bytes) > 80 THEN '❗警告'
            ELSE '✅正常' END "状态"
FROM (SELECT tablespace_name, SUM(bytes) bytes
      FROM dba_data_files GROUP BY tablespace_name) df,
     (SELECT tablespace_name, SUM(bytes) bytes
      FROM dba_free_space GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name;

技巧：配合ORDER BY "使用率%" DESC可优先处理高危表空间🔥

3️⃣ 查看数据文件明细

SELECT file_name "文件路径",
       tablespace_name "所属表空间",
       bytes/1024/1024 "大小(MB)",
       autoextensible "自动扩展",
       maxbytes/1024/1024 "最大可扩展(MB)"
FROM dba_data_files
WHERE tablespace_name = 'USERS';  -- 替换为实际表空间名

典型问题：发现autoextensible=NO且空间不足时，需手动扩容📈

实战维护操作

1️⃣ 扩容表空间（三种方式）

方式1：增加数据文件

ALTER TABLESPACE USERS 
ADD DATAFILE '/oracle/data/users02.dbf' 
SIZE 500M AUTOEXTEND ON NEXT 50M MAXSIZE 2G;

方式2：调整现有文件

ALTER DATABASE DATAFILE '/oracle/data/users01.dbf'
RESIZE 2G;

方式3：开启自动扩展

ALTER DATABASE DATAFILE '/oracle/data/users01.dbf'
AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

2️⃣ 临时表空间组管理

-- 查看临时表空间组
SELECT group_name, tablespace_name 
FROM dba_tablespace_groups;
-- 创建临时表空间组
ALTER TABLESPACE temp1 TABLESPACE GROUP temp_grp;

高阶技巧 💡

1️⃣ 查找大对象占用

SELECT segment_name "对象名",
       segment_type "类型",
       tablespace_name "表空间",
       bytes/1024/1024 "大小(MB)"
FROM dba_segments
WHERE tablespace_name = 'USERS'
ORDER BY bytes DESC
FETCH FIRST 10 ROWS ONLY;

2️⃣ 表空间碎片检查

SELECT tablespace_name "表空间",
       COUNT(*) "碎片数量",
       SUM(bytes)/1024/1024 "碎片总大小(MB)"
FROM dba_free_space
GROUP BY tablespace_name
HAVING COUNT(*) > 10;  -- 碎片数大于10需关注

避坑指南 🚨

1. SYSTEM表空间爆满：优先检查AUD$审计表，定期清理
2. 临时表空间异常：检查是否有笛卡尔积查询等SQL问题
3. ORA-01653错误：立即按本文第三节扩容

最后叮嘱：建议将关键SQL保存为脚本，起名类似space_emergency.sql放在常用目录，毕竟在深夜救火时，脑子可能比表空间还空呢！😅

（本文参考Oracle 19c官方文档及实际运维经验，2025-08更新验证）