一、简单概述

我们在数据库使用过程中，各种工作场景下，一定会碰到过一个问题：如何查询数据库的数据量？

这个问题很常见，本文来总结一下 DM 数据库相关查询 SQL。

二、总结

2.1 整个实例的数据量

--单位MB
WITH TMP AS(SELECT USERNAME,USER_USED_SPACE(USERNAME)*PAGE/1024/1024 AS "USER_SIZE" FROM DBA_USERS)SELECT SUM(USER_SIZE) AS "数据总量(MB)" FROM TMP;
--单位GB
WITH TMP AS(SELECT USERNAME,USER_USED_SPACE(USERNAME)*PAGE/1024/1024/1024 AS "USER_SIZE" FROM DBA_USERS)SELECT SUM(USER_SIZE) AS "数据总量(MB)" FROM TMP;

				

					
				
				

					
				
				

					
				
				

					
				
			

上述 SQL 如果执行报数据溢出，可自行查询 SELECT PAGE()/1024; 后的结果带入上述 SQL，例如
执行后结果为 32【页大小 32K】，则执行如下 SQL

WITH TMP AS(SELECT USERNAME,USER_USED_SPACE(USERNAME)*32/1024/1024 AS "USER_SIZE" FROM DBA_USERS)SELECT SUM(USER_SIZE) AS "数据总量(GB)" FROM TMP;

				

					
				
				

					
				
				

					
				
				

					
				
			

原理是少乘了一个 1024，降低数据溢出的可能性。

2.2 用户的数据量

--MB
SELECT USERNAME,USER_USED_SPACE(USERNAME)*PAGE/1024/1024 AS "USER_SIZE(MB)" FROM DBA_USERS;
--GB
SELECT USERNAME,USER_USED_SPACE(USERNAME)*PAGE/1024/1024/1024 AS "USER_SIZE(GB)" FROM DBA_USERS;

				

					
				
				

					
				
				

					
				
				

					
				
			

同理，在数据溢出的情况下，可以用 SELECT PAGE()/1024; 的结果代入计算

SELECT USERNAME,USER_USED_SPACE(USERNAME)*32/1024/1024 AS "USER_SIZE(GB)" FROM DBA_USERS;

				

					
				
				

					
				
				

					
				
				

					
				
			

2.3 表的数据量

方法一：系统函数 TABLE_USED_SPACE

--MB
SELECT OWNER,TABLE_NAME,TABLE_USED_SPACE(OWNER,TABLE_NAME)*PAGE/1024/1024 AS "TABLE_SIZE(MB)" FROM DBA_TABLES WHERE OWNER = 'USERNAME' AND TABLE_NAME = 'TABLENAME';
--GB
SELECT OWNER,TABLE_NAME,TABLE_USED_SPACE(OWNER,TABLE_NAME)*PAGE/1024/1024/1024 AS "TABLE_SIZE(GB)" FROM DBA_TABLES WHERE OWNER = 'USERNAME' AND TABLE_NAME = 'TABLENAME';

				

					
				
				

					
				
				

					
				
				

					
				
			

方法二：利用 DBA_SEGMENTS 视图计算

--MB
SELECT OWNER,SEGMENT_NAME,CAST(BYTES AS NUMBER)/1024/1024 "所占空间(MB)" FROM DBA_SEGMENTS WHERE OWNER = 'USERNAME' AND DBA_SEGMENTS.SEGMENT_NAME = 'TABLENAME';
--GB
SELECT OWNER,SEGMENT_NAME,CAST(BYTES AS NUMBER)/1024/1024/1024 "所占空间(GB)" FROM DBA_SEGMENTS WHERE OWNER = 'USERNAME' AND DBA_SEGMENTS.SEGMENT_NAME = 'TABLENAME';

				

					
				
				

					
				
				

					
				
				

					
				
			

2.4 另：表数据量由大到小排序

SELECT
	A.OWNER AS "模式名",
	A.SEGMENT_NAME AS "表名",
	A.BYTES / 1024 / 1024 AS "大小(MB)",
	A.TABLESPACE_NAME AS "所属表空间",
	B.COMMENTS AS "表注释"
FROM
	DBA_SEGMENTS A,
	DBA_TAB_COMMENTS B
WHERE
	A.OWNER = B.OWNER
	AND A.SEGMENT_NAME = B.TABLE_NAME
	AND A.OWNER = '模式名'
ORDER BY
	3 DESC;

				

					
				
				

					
				
				

					
				
				

					
				
			

2.5 另：查询表的数量

--整个实例中表的数量
SELECT COUNT(*) FROM DBA_TABLES WHERE DBA_TABLES.OWNER NOT IN ('SYS','SYSAUDITOR','SYSSSO','CTISYS','SYSDBA','SYSJOB');
--某个用户下表的数量
SELECT COUNT(*) FROM DBA_TABLES WHERE DBA_TABLES.OWNER = 'USERNAME';

				

					
				
				

					
				
				

					
				
				

					
				
			

2.6 另：表的行数

表的行数记录存储在其段头页中，其位置为段头页的第 75 个字节开始存储。