Oracle数据库记录数和空间使用

A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (2^22) blocks.

Oracle的单个物理文件（datafile）最大可以有2^22（4194302）个块，最大文件尺寸取决于每个数据块大小，如果是8K的块，那么最大为：2^22*8K = 32G

Oracle的逻辑存储是基于Tablespace，一个Tablespace中可用使用多个数据文件（datafiles）。

记录数统计

记录数可以从DBA_TABLES, ALL_TABLES, USER_TABLES表中查得。

总的记录数

SELECT SUM(NUM_ROWS)
FROM  ALL_TABLES
WHERE NUM_ROWS IS NOT NULL;

每个表里的记录数

SELECT OWNER, TABLE_NAME, NUM_ROWS, LAST_ANALYZED
FROM ALL_TABLES
WHERE NUM_ROWS IS NOT NULL
ORDER BY NUM_ROWS DESC;

数据库占用空间

使用操作系统命令行统计

du -sh

统计数据文件（datafile）所占用大小，包含了free部分

SELECT SUM(BYTES)/1024/1024/1024 AS TOTAL_GB FROM dba_data_files;

统计有效数据段所占大小

SELECT SUM(BYTES)/1024/1024/1024 TOTAL_GB FROM DBA_SEGMENTS;

统计可使用空间部分大小

SELECT SUM(BYTES)/1024/1024/1024 FROM dba_free_space;

基于TABLESPACE表空间统计

下面查询反应已分配表空间的使用情况，不能反应实际可自动分配的空间，这里使用率较高并不代表需要人工去扩展空间，如果是自动扩展就需要去查看分配情况数据文件大小是否已经到达分配上限，磁盘空间是否足够。

不能使用dba_data_files表中的USER_BYTES字段统计使用情况，这个字段包括了被truncate的数据所占用的空间，不能反映实际使用。

-- 查询表空间使用情况
SELECT 
    a.tablespace_name, 
    a.bytes total, 
    b.bytes used, 
    c.bytes free, 
    round((b.bytes * 100) / a.bytes, 2) "% USED ", 
    round((c.bytes * 100) / a.bytes, 2) "% FREE " 
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c 
WHERE a.tablespace_name = b.tablespace_name 
    AND a.tablespace_name = c.tablespace_name;

或者

-- 查询表空间使用情况
SELECT 
    a.tablespace_name "表空间名",  
    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;

更准确和完整的一种查询

-- 查询表空间使用情况
SELECT 
    DD.TABLESPACE_NAME,
    ROUND (DD.TOTAL_GB, 2 ) TOTAL_GB,
    ROUND (DF.FREE_GB, 2 ) FREE_GB,
    ROUND ((DD.TOTAL_GB - DF.FREE_GB), 2 ) USED_GB,
    ROUND ((DD.TOTAL_GB - DF.FREE_GB) / DD.TOTAL_GB * 100, 2 ) "USED_PER"
FROM 
( 
    SELECT TABLESPACE_NAME, SUM (BYTES / 1024 / 1024 / 1024 ) TOTAL_GB
    FROM DBA_DATA_FILES DD
    GROUP BY TABLESPACE_NAME
) DD
JOIN 
(
    SELECT A.TABLESPACE_NAME, SUM (A.BYTES / 1024 / 1024 / 1024 ) FREE_GB
    FROM DBA_FREE_SPACE A
    GROUP BY A.TABLESPACE_NAME
) DF
ON DD.TABLESPACE_NAME = DF.TABLESPACE_NAME
UNION ALL 
(
    SELECT TABLESPACE_NAME,
    ROUND (TF.TABLESPACE_SIZE / 1024 / 1024 / 1024 , 2 ),
    ROUND (TF.FREE_SPACE / 1024 / 1024 / 1024 , 2 ),
    ROUND ((TF.TABLESPACE_SIZE - TF.FREE_SPACE) / 1024 / 1024 / 1024 , 2),
    ROUND ((TF.TABLESPACE_SIZE - TF.FREE_SPACE) / TF.TABLESPACE_SIZE * 100 ,2)
    FROM DBA_TEMP_FREE_SPACE TF
)
ORDER BY USED_PER DESC;

是否需要通过增加数据文件或扩展数据文件来增大表空间，还需要通过查看是否有可分配的空间。

-- 查看自动扩展表空间最大可分配的剩余空间
SELECT 
    TU.TABLESPACE_NAME, 
    TU.MAX_MB,
    TU.ALLOC_MB, 
    TU.MAX_MB - ALLOC_MB AS NOT_ALLOC_MB, 
    ROUND((TU.MAX_MB - ALLOC_MB) / TU.MAX_MB, 2) AS NOT_ALLOC_PER
FROM (
    SELECT 
        T.TABLESPACE_NAME, 
        ROUND(SUM(D.BYTES/1024/1024), 2) ALLOC_MB, 
        ROUND(SUM(D.MAXBYTES/1024/1024), 2) MAX_MB
    FROM DBA_TABLESPACES T, DBA_DATA_FILES D 
    WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME AND AUTOEXTENSIBLE='YES'
    GROUP BY T.TABLESPACE_NAME
) TU
ORDER BY NOT_ALLOC_PER;

如果是固定大小的数据文件，在到达最大空间之前可以通过resize来扩展文件大小

ALTER DATABASE DATAFILE 'D:/app/administrator/oradata/orcl/user_test_02.dbf' RESIZE 32G;

如果表空间中的数据文件都已经到达最大大小，则需要添加新的数据文件到表空间

ALTER TABLESPACE user_test ADD DATAFILE 'D:/app/administrator/oradata/orcl/user_test_03.dbf' SIZE 1G autoextend on;

查看所有数据文件占用的总空间（已分配空间）及用户使用的空间（已使用）

SELECT 
    ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) "已分配空间(MB)", 
    ROUND(SUM(DD.USER_BYTES) / (1024 * 1024), 2) "已使用空间(MB)"
FROM SYS.DBA_DATA_FILES DD;

每个表空间大小

SELECT
    DD.TABLESPACE_NAME,
    ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) "表空间大小(M)"
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME;

表空间剩余大小

SELECT
    TABLESPACE_NAME,
    ROUND(SUM(BYTES) / (1024 * 1024), 2) "空闲空间(M)"
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME;

已使用表空间大小（1）

SELECT 
    TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) "已使用空间(M)" 
FROM SYS.DBA_SEGMENTS GROUP BY TABLESPACE_NAME;

已使用表空间大小（2）

SELECT D.TABLESPACE_NAME, D.TOTALSIZE - F.FREESIZE "已使用空间(M)"
FROM 
(
    SELECT DD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOTALSIZE
    FROM SYS.DBA_DATA_FILES DD
    GROUP BY DD.TABLESPACE_NAME
) D,
(
    SELECT TABLESPACE_NAME,
           ROUND(SUM(BYTES) / (1024 * 1024), 2) FREESIZE
    FROM SYS.DBA_FREE_SPACE
    GROUP BY TABLESPACE_NAME
) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME;

基于SCHEMA统计

查看有多少个用户就可以知道有多少个SCHEMA

SELECT USERNAME FROM SYS.DBA_USERS

查询每个SCHEMA所占用的空间

SELECT 
   OWNER,
   ROUND(SUM(BYTES)/1024/1024/1024, 2) SIZE_GB
FROM SYS.DBA_SEGMENTS 
GROUP BY OWNER;

查询SCHEMA中的表

SELECT TABLE_NAME FROM SYS.DBA_TABLES WHERE OWNER=UPPER('&schema');

首页 » Oracle数据库记录数和空间使用

记录数统计

数据库占用空间

基于TABLESPACE表空间统计

基于SCHEMA统计

相关内容