MySQL和Oracle数据库空间查看与扩容指南

远山来客

发布时间：2026-10-15| 5人浏览过

原创

在日常数据库维护中，监控存储空间是保障系统稳定运行的关键环节。无论是关系型数据库还是大型数据仓库，及时获取容量数据并在空间不足时进行扩容，能有效避免业务中断。本文将详细列出MySQL和Oracle中查询数据库、表格及表空间容量的核心SQL语句，并提供Oracle环境下的空间扩展实操步骤。

MySQL版

第1步：查看所有数据库容量大小

目的：宏观了解各数据库实例的数据与索引占用总量，按容量降序排列，便于快速定位资源消耗大户。

-- 查看所有数据库容量大小
SELECT
    table_schema AS '数据库',
    sum( table_rows ) AS '记录数',
    sum(
    TRUNCATE ( data_length / 1024 / 1024, 2 )) AS '数据容量(MB)',
    sum(
    TRUNCATE ( index_length / 1024 / 1024, 2 )) AS '索引容量(MB)' 
FROM
    information_schema.TABLES 
GROUP BY
    table_schema 
ORDER BY
    sum( data_length ) DESC,
    sum( index_length ) DESC;

第2步：查看所有数据库各表容量大小

目的：细化到表级别，分析单个表的存储分布，用于识别具体是哪个业务模块占用过多资源。

SELECT
    table_schema AS '数据库',
    table_name AS '表名',
    table_rows AS '记录数',
    TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '数据容量(MB)',
    TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)' 
FROM
    information_schema.TABLES 
ORDER BY
    data_length DESC,
    index_length DESC;

第3步：查看指定数据库容量大小

目的：聚焦特定业务库（如替换 '数据库名'），快速获取该库的整体水位线。

SELECT
    table_schema AS '数据库',
    sum( table_rows ) AS '记录数',
    sum(
    TRUNCATE ( data_length / 1024 / 1024, 2 )) AS '数据容量(MB)',
    sum(
    TRUNCATE ( index_length / 1024 / 1024, 2 )) AS '索引容量(MB)' 
FROM
    information_schema.TABLES 
WHERE
    table_schema = '数据库名';

第4步：查看指定数据库各表容量大小

目的：对目标库内的表进行逐层剖析，排序后可直观看到“大头”在哪些表上。

SELECT
    table_schema AS '数据库',
    table_name AS '表名',
    table_rows AS '记录数',
    TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '数据容量(MB)',
    TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)' 
FROM
    information_schema.TABLES 
WHERE
    table_schema = '数据库名' 
ORDER BY
    data_length DESC,
    index_length DESC;

第5步：查看指定数据库各表详细信息

目的：除了容量，还想查看引擎类型、字符集等元数据时使用此快捷命令。

SHOW TABLE STATUS;

Oracle版

第1步：查看用户表所占的空间大小

目的：在非DBA权限下，通过 USER_SEGMENTS 视图直接获取当前用户创建的表大小。若需查看整个实例的表空间总容量（dba_tablespaces），通常需要较高的 DBA 权限，日常工作中较少见。

-- 不需要DBA权限，查看当前用户下表的大小
SELECT SEGMENT_NAME TABLENAME,(BYTES/1024/1024) MB
,RANK() OVER (PARTITION BY NULL ORDER BY BYTES DESC) RANK_ID  -- 根据表大小进行排序
FROM USER_SEGMENTS
WHERE SEGMENT_TYPE='TABLE';

-- 需要DBA权限，通常仅DBA操作：查看表空间总大小
SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size 
FROM dba_tablespaces t, dba_data_files d 
WHERE t.tablespace_name = d.tablespace_name 
GROUP BY t.tablespace_name;

第2步：查看表空间的使用情况

目的：计算各表空间的总容量、剩余容量及已用容量百分比，是判断是否需要扩容的基础依据。

SELECT a.tablespace_name "表空间名称",
       total / (1024 * 1024) "表空间大小(M)",
       free / (1024 * 1024) "表空间剩余大小(M)",
       (total - free) / (1024 * 1024 ) "表空间使用大小(M)",
       total / (1024 * 1024 * 1024) "表空间大小(G)",
       free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
       (total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
       round((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
      FROM dba_free_space
      GROUP BY tablespace_name) a,
     (SELECT tablespace_name, SUM(bytes) total
      FROM dba_data_files
      GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name

第3步：查看回滚段名称及大小

目的：监控事务处理过程中的回滚段状态，排查长时间运行事务或一致性读取问题。

SELECT segment_name, 
tablespace_name, 
r.status, 
(initial_extent / 1024) initialextent, 
(next_extent / 1024) nextextent, 
max_extents, 
v.curext curextent 
FROM dba_rollback_segs r, v$rollstat v 
WHERE r.segment_id = v.usn(+) 
ORDER BY segment_name;

第4步：查看控制文件和日志文件位置

目的：确认控制文件与控制文件列表，以及在线重做日志文件的物理路径，通常用于备份恢复规划。

-- 查看控制文件
SELECT NAME FROM v$controlfile; 

-- 查看日志文件
SELECT MEMBER FROM v$logfile;

第5步：查看数据库对象统计信息

目的：按所有者、类型和状态统计对象数量，有助于了解数据库结构的复杂度和完整性。

SELECT owner, object_type, status, COUNT(*) count# 
FROM all_objects 
GROUP BY owner, object_type, status;

第6步：查看数据库版本和创建日期

目的：确认当前Oracle数据库的确切版本号和归档模式状态。

-- 查看数据库版本
SELECT version 
FROM product_component_version 
WHERE substr(product, 1, 6) = 'Oracle'; 

-- 查看数据库的创建日期和归档方式
SELECT created, log_mode 
FROM v$database;

第7步：查看表空间是否具有自动扩展能力

目的：检查数据文件是否开启了 AUTOEXTENSIBLE，这是防止磁盘写满导致报错的第一道防线。

SELECT T.TABLESPACE_NAME,D.FILE_NAME,
D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
FROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME,FILE_NAME;

Oracle进阶：表空间管理与扩容

一、查看表空间使用率（加强版）

以下查询涵盖了普通永久表空间及临时表空间的综合使用率，更贴合生产环境的实际监控需求。

1. 查看具体表的占用空间大小

--查看具体表的占用空间大小
select * from (
select t.tablespace_name,t.owner, t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) mb
from dba_segments t
where t.segment_type='TABLE'
group by t.tablespace_name,t.OWNER, t.segment_name, t.segment_type
) t
order by t.mb desc;

2. 查看数据库表空间使用率（含Temp）

--查看表空间使用率(包含临时表空间)
select * from (
Select a.tablespace_name,
(a.bytes- b.bytes) "表空间使用大小(BYTE)",
a.bytes/(1024*1024*1024) "表空间大小(GB)",
b.bytes/(1024*1024*1024) "表空间剩余大小(GB)",
(a.bytes- b.bytes)/(1024*1024*1024) "表空间使用大小(GB)",
to_char((1 - b.bytes/a.bytes)*100,'99.99999') || '%' "使用率"
from (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
union all
select c.tablespace_name,
d.bytes_used "表空间使用大小(BYTE)",
c.bytes/(1024*1024*1024) "表空间大小(GB)",
(c.bytes-d.bytes_used)/(1024*1024*1024) "表空间剩余大小(GB)",
d.bytes_used/(1024*1024*1024) "表空间使用大小(GB)",
to_char(d.bytes_used*100/c.bytes,'99.99999') || '%' "使用率"
from
(select tablespace_name,sum(bytes) bytes from dba_temp_files group by tablespace_name) c,
(select tablespace_name,sum(bytes_cached) bytes_used from v$temp_extent_pool group by tablespace_name) d
where c.tablespace_name = d.tablespace_name
)
order by tablespace_name;

二、扩展大小或增加表空间文件

当发现表空间使用率过高且无法清理历史数据时，需要进行扩容操作。操作前建议先备份相关配置文件或导出数据。

1. 更改现有数据文件大小或开启自动增长

入口：SQL Plus 或任何SQL客户端。

动作：调整已有 .dbf 文件的大小，或者让其自动扩展。

-- 开启自动扩展
alter database datafile '/path/to/system_01.dbf' autoextend on;
-- 固定大小扩容至1024MB
alter database datafile '/path/to/system_01.dbf' resize 1024M;

2. 为表空间新增一个数据文件

目的：当单文件达到上限（如OS限制32G或更高版本限制）时，添加新文件是标准做法。

alter tablespace SYSTEM add datafile '/****/new_file.dbf' size 1000m autoextend on next 100m;

3. 处理Temp临时表空间

注意： 临时表空间不能直接添加 datafile，必须使用 tempfile，否则会报错 ORA-03217。

alter tablespace TEMP01 add tempfile '/home/temp01.dbf' size 1000m autoextend on next 100m maxsize 10000m;

三、临时表空间爆满的紧急处理

临时表空间主要用于排序（order by）、索引创建和连接操作。当运算完成后系统会自动清理，但若遇到大查询未释放内存，会导致 TEMP 表空间骤增。

解决方法一：手动扩容Temp表空间

参考上述步骤二的 add tempfile 命令执行。

解决方法二：重建临时表空间（高风险操作，需谨慎）

如果资源极度受限且原有Temp无法清理，可新建Temp替代旧Temp。

查看当前默认临时表空间：

select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

创建新的临时表空间：

create temporary tablespace TEMP01 tempfile '/home/temp01.dbf' size 31G;

将数据库默认临时表空间切换为新表空间：

alter database default temporary tablespace TEMP01;

删除旧的临时表空间：

drop tablespace TEMP02 including contents and datafiles;

若删除时报错 ORA-60100，说明有会话正在使用原临时表空间进行排序。需先Kill掉相关进程：

-- 1. 查找正在使用临时段的会话
Select se.username,se.sid,se.serial#,su.extents,su.blocks*to_number(rtrim(p.value))as Space,
tablespace,segtype,sql_text
from v$sort_usage su,v$parameter p,v$session se,v$sql s
where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash
and s.address=su.sqladdr
order by se.username,se.sid;

-- 2. 根据查到的 sid 和 serial# 终止会话
alter system kill session 'sid,serial#';

以上就是查询数据库空间(MySQL和Oracle)的详细操作指南，更多关于数据库性能调优与维护的资料请关注本站其它相关文章！