数据量统计

为体现系统运营情况,了解数据库中数据增长量,公司需每月统计一次数据总量和数据增长量。以下是简单的语句记录

Oracle数据量统计

查看表空间及表空间大小

1
SELECT TABLESPACE_NAME,SUM(BYTES) / 1024 / 1024 AS MB FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME ORDER BY MB DESC;

查看表空间及对应的表空间文件

1
SELECT TABLESPACE_NAME, FILE_NAME FROM DBA_DATA_FILES;

统计占用空间

1
SELECT SUM(BYTES) / 1024 / 1024 AS MB FROM DBA_SEGMENTS;

统计数据总行数

1
SELECT SUM(SAMPLE_SIZE) FROM USER_TABLES;

SQL Server 数据量统计

查询数据库所有表的数据的行数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
--方法1:
SELECT A.NAME, B.ROWS
FROM SYSOBJECTS AS A
INNER JOIN SYSINDEXES AS B
ON A.ID = B.ID
WHERE (A.TYPE = 'u')
AND (B.INDID IN (0, 1))
ORDER BY B.ROWS DESC
--方法2:
SELECT SCHEMA_NAME(T.SCHEMA_ID) AS [ SCHEMA ],
T.NAME AS TABLENAME,
I.ROWS AS [ ROWCOUNT ]
FROM SYS.TABLES AS T, SYSINDEXES AS I
WHERE T.OBJECT_ID = I.ID
AND I.INDID <= 1
--如果只看总量,使用以下语句
SELECT SUM(ROWS)
FROM SYS.TABLES AS T, SYSINDEXES AS I
WHERE T.OBJECT_ID = I.ID
AND I.INDID <= 1

统计数据库占用空间大小

1
EXEC sp_spaceused

PS: 以上语句均来自网络,暂未找到可以直接统计一个月的数据增量,因每个月统计一次,可以使用本月的数据量减去上月统计的数据量来获取数据增量。

独学无友,孤陋寡闻;学习记录,欢迎拍砖!