Oracle 数据文件 实际使用量 计算说明
一. 查看DB 实际使用磁盘数量
我们可以通过DBA_DATA_FILES这张表来查看整个表空间的大小。
SQL> desc dba_data_files;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)
我们可以通过bytes字段和Blocks 字段来判断。
查看整个DB 表空间大小：
SQL> select sum(bytes)/1024/1024 "MB" from dba_data_files;
MB
----------
16790
从这里，我们可以说我们的数据库大小在17G所有。 即占用的空间。
查看整个DB 空闲空间量：
SQL> select sum(bytes)/1024/1024 "MB" from dba_free_space;
MB
----------
10872.5
所有表空间的空闲空间是10872.5MB。
通过dba_data_files 不能直接得出使用量。实际数据量的计算需要用上面的2个值相减：16790-10872.5. 所以，我们DB 实际数据存储量在6G左右。
二. 深入研究
2.1 dba_free_space 表
该表描述了数据库中所有表空间的空闲extents。 在我的BLog：
表空间(tableSpace) 段(segment) 盘区(extent) 块(block) 关系
http://blog.csdn.net/tianlesoftware/archive/2009/12/08/4962476.aspx
讲了segment，extent 和 block的关系。 我们的数据文件对应segment。 而segment 是由一些列extent组成。 每个extent又是由block组成。
所以通过dba_free_space 查询的结果，实际是表空间对应的每个extent的空闲量。
注意：
对于本地管理的表空间，当数据文件或者整个表空间offline， dba_free_space 不会显示任何extent的信息。
SQL>create tablespace dave datafile '/u01/app/oracle/oradata/dave/dave01.dbf' size 50m;
SQL> select * from dba_free_space;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
--------------- ---------- ---------- ---------- ---------- ------------
SYSTEM 1 17617 65536 8 1
SYSTEM 1 64641 16777216 2048 1
SYSTEM 1 66705 9306112 1136 1
UNDOTBS1 2 33 65536 8 2
UNDOTBS1 2 49 65536 8 2
UNDOTBS1 2 65 65536 8 2
UNDOTBS1 2 97 65536 8 2
UNDOTBS1 2 161 65536 8 2
UNDOTBS1 2 185 131072 16 2
UNDOTBS1 2 209 65536 8 2
UNDOTBS1 2 225 5570560 680 2
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
--------------- ---------- ---------- ---------- ---------- ------------
UNDOTBS1 2 1033 13303808 1624 2
UNDOTBS1 2 2665 1310720 160 2
UNDOTBS1 2 2953 29360128 3584 2
UNDOTBS1 2 6665 90177536 11008 2
UNDOTBS1 2 17801 71303168 8704 2
UNDOTBS1 2 26633 17760256 2168 2
UNDOTBS1 2 28809 5242880 640 2
UNDOTBS1 2 29577 4194304 512 2
UNDOTBS1 2 30217 70254592 8576 2
UNDOTBS1 2 38921 84869120 10360 2
USERS 4 57 4784128 584 4
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
--------------- ---------- ---------- ---------- ---------- ------------
DAVE 5 9 52363264 6392 5
23 rows selected.
SQL> alter database datafile 5 offline;
Database altered.
SQL> select * from dba_free_space;
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
--------------- ---------- ---------- ---------- ---------- ------------
SYSTEM 1 17617 65536 8 1
SYSTEM 1 64641 16777216 2048 1
SYSTEM 1 66705 9306112 1136 1
UNDOTBS1 2 33 65536 8 2
UNDOTBS1 2 49 65536 8 2
UNDOTBS1 2 65 65536 8 2
UNDOTBS1 2 97 65536 8 2
UNDOTBS1 2 161 65536 8 2
UNDOTBS1 2 185 131072 16 2
UNDOTBS1 2 209 65536 8 2
UNDOTBS1 2 225 5570560 680 2
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
--------------- ---------- ---------- ---------- ---------- ------------
UNDOTBS1 2 1033 13303808 1624 2
UNDOTBS1 2 2665 1310720 160 2
UNDOTBS1 2 2953 29360128 3584 2
UNDOTBS1 2 6665 90177536 11008 2
UNDOTBS1 2 17801 71303168 8704 2
UNDOTBS1 2 26633 17760256 2168 2
UNDOTBS1 2 28809 5242880 640 2
UNDOTBS1 2 29577 4194304 512 2
UNDOTBS1 2 30217 70254592 8576 2
UNDOTBS1 2 38921 84869120 10360 2
USERS 4 57 4784128 584 4
22 rows selected.
SQL>
offline 之后，就没有显示Dave 的相关信息了。
在上面的查询结果，我们看到UNDOTBS 有很多记录。 这个就是我们之前说的，dba_free_space 会显示表空间下所有extent 的空闲状况。 当表空间比较大时，这里的extent 的记录也就会很多。
dba_free_space 字段的含义：
|
Datatype |
Description |
|
|
TABLESPACE_NAME |
VARCHAR2(30) |
Name of the tablespace containing the extent |
|
FILE_ID |
NUMBER |
File identifier number of the file containing the extent |
|
BLOCK_ID |
NUMBER |
Starting block number of the extent 每个extent 开始的block number |
|
BYTES |
NUMBER |
Size of the extent (in bytes) |
|
BLOCKS |
NUMBER |
Size of the extent (in Oracle blocks) |
|
RELATIVE_FNO |
NUMBER |
Relative file number of the file containing the extent --extent 管理的数据文件号 |
还有一点，就是这里的BYTES 和 Blocks 是对应的关系。 我们看一个具体的例子。 从上面的结果中拉一条记录过来：
TABLESPACE_NAME BYTES BLOCKS
--------------- ---------- ----------
UNDOTBS1 13303808 1624
BLOCKS 显示的该extent中空闲的block 数量。
BYTES 显示的是这些block对应的空间大小。
BYTES=BLOCKS*块的大小
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
即每个块是8k。
BYTES=BLOCKS*块的大小
1624*8*1024=13303808
2.2 dba_data_files
该表显示的是DB 所有数据文件的信息。是个整体概念。
|
Column |
Datatype |
NULL |
Description |
|
FILE_NAME |
VARCHAR2(513) |
|
Name of the database file |
|
FILE_ID |
NUMBER |
NOT NULL |
File identifier number of the database file |
|
TABLESPACE_NAME |
VARCHAR2(30) |
NOT NULL |
Name of the tablespace to which the file belongs |
|
BYTES |
NUMBER |
|
Size of the file in bytes |
|
BLOCKS |
NUMBER |
NOT NULL |
Size of the file in Oracle blocks |
|
STATUS |
VARCHAR2(9) |
|
File status: AVAILABLE or INVALID (INVALID means that the file number is not in use, for example, a file in a tablespace that was dropped) |
|
RELATIVE_FNO |
NUMBER |
|
Relative file number |
|
AUTOEXTENSIBLE |
VARCHAR2(3) |
|
Autoextensible indicator |
|
MAXBYTES |
NUMBER |
|
Maximum file size in bytes |
|
MAXBLOCKS |
NUMBER |
|
Maximum file size in blocks |
|
INCREMENT_BY |
NUMBER |
|
Number of Oracle blocks used as autoextension increment |
|
USER_BYTES |
NUMBER |
|
The size of the file available for user data. The actual size of the file minus the USER_BYTES value is used to store file related metadata. |
|
USER_BLOCKS |
NUMBER |
|
Number of blocks which can be used by the data |
|
ONLINE_STATUS |
VARCHAR2(7) |
|
Online status of the file: SYSOFF SYSTEM OFFLINE ONLINE RECOVER |
三. 查看表空间使用率的一个SQL
SELECT D.TABLESPACE_NAME,
SPACE||'M' "SUM_SPACE(M)",
BLOCKS "SUM_BLOCKS",
SPACE - NVL (FREE_SPACE, 0)||'M' "USED_SPACE(M)",
ROUND( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2)||'%' "USED_RATE(%)",
FREE_SPACE||'M' "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --如果有临时表空间
SELECT D.TABLESPACE_NAME,
SPACE||'M' "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE||'M' "USED_SPACE(M)",
ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2)||'%' "USED_RATE(%)",
NVL (FREE_SPACE, 0)||'M' "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY 1;
---------------------------------------------------------------------------------------------------
Blog： http://blog.csdn.net/tianlesoftware
网上资源： http://tianlesoftware.download.csdn.net
相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
DBA1 群：62697716(满); DBA2 群：62697977(满) DBA3 群：62697850(满)
DBA 超级群：63306533(满); DBA4 群： 83829929 DBA5群： 142216823
聊天 群：40132017
--加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请
