【oracle，mysql】表空间，表大小查询

最新推荐文章于 2025-03-13 09:00:58 发布

末无忧

最新推荐文章于 2025-03-13 09:00:58 发布

阅读量1.5k

点赞数

文章标签： oracle mysql 数据库

本文链接：https://blog.youkuaiyun.com/zzt880805/article/details/130005588

版权

文章提供了查询Oracle和MySQL数据库中表空间存储路径、总容量、剩余空间以及各表大小的方法。对于Oracle，包括了查询所有表空间和特定表空间的命令；对于MySQL，展示了查看所有数据库及指定数据库容量和各表大小的SQL语句。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

【oracle】

查询表空间存路径位置：

select file_name,tablespace_name from dba_data_files;

查询所有表空间：：

SELECT A.TABLESPACE_NAME "表空间名",

A.TOTAL_SPACE "总空间(M)",

NVL(B.FREE_SPACE, 0) "剩余空间(M)",

A.TOTAL_SPACE - NVL(B.FREE_SPACE, 0) "使用空间(M)",

trunc(NVL(B.FREE_SPACE, 0) / A.TOTAL_SPACE * 100, 2) "剩余百分比%"

FROM (SELECT TABLESPACE_NAME, trunc(SUM(BYTES) / 1024 / 1024 ,2) TOTAL_SPACE

FROM DBA_DATA_FILES

GROUP BY TABLESPACE_NAME) A,

(SELECT TABLESPACE_NAME, trunc(SUM(BYTES / 1024 / 1024 ),2) FREE_SPACE

FROM DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME) B

WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)

ORDER BY 5;

查询指定表空间内各表大小（用户级）：

SELECT a.OWNER, a.SEGMENT_NAME ,b.table_name, sum(a.bytes), a.SEGMENT_TYPE

FROM DBA_SEGMENTS a,User_Tables b

WHERE a.TABLESPACE_NAME = '表空间名称（要修改）'

and a.segment_name=b.table_name

group by a.OWNER, a.SEGMENT_NAME,b.table_name,a.SEGMENT_TYPE

order by sum(bytes) desc

查询指定表空间内各表大小（dba级）：

SELECT a.OWNER, a.SEGMENT_NAME ,b.table_name, sum(a.bytes), a.SEGMENT_TYPE

FROM DBA_SEGMENTS a,dba_tables b

WHERE a.TABLESPACE_NAME = '表空间名称（要修改）'

and a.segment_name=b.table_name

group by a.OWNER, a.SEGMENT_NAME,b.table_name,a.SEGMENT_TYPE

order by sum(bytes) desc

【mysql】

mysql 查看表的大小方法：

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

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;

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

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;

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

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 = 'mysql';

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

例：查看mysql库各表容量大小

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 = 'mysql'
ORDER BY
	data_length DESC,
	index_length DESC;