一、简单概述

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

这个问题很常见，本文来总结一下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';